Writing an array function from VBA

Posted on 09/06/2013 by dougaj4

I have covered the topic of writing VBA arrays to a spreadsheet range quite frequently here.  As a reminder, the procedure is:

ReDim myarray(1 To NumArrayRows, 1 To NumArrayColumns)
' Fill array
With Range("MyNamedRange")
.ClearContents
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
End With
Range("MyNamedRange").Value2 = myarray

But what if you want to write an array function to the spreadsheet from VBA?  The procedure for that is also quite simple, as shown in the example below:
Range("LCRes").ClearContents
Range("LCRes").Resize(NumRows, 4).Name = "LCRes"
Range("LCRes").FormulaArray = "=getlcres(LC_1Res,LC_2Res,stofile,PlotLC,Env_LF)"

This example writes a User Defined Function (UDF) called “GetLCres” to the named range “LCRes”, having first cleared the range, then resized it to Numrows rows x 4 columns.

The arguments of GetLCRes are all named spreadsheet ranges, but could be numerical values or cell addresses (although using cell addresses in VBA code is usually not a good idea).  The example uses a UDF, but the same technique will work with built-in functions, or any valid spreadsheet array formula.

Posted in Arrays, Excel, UDFs, VBA | Tagged , , , | 1 Comment

Using RC Design Functions – 3

Posted on 08/06/2013 by dougaj4

In the previous post in this series I looked at the results available from the EStress function in the RC Design Functions spreadsheet.  Three associated functions are:

  • EstressCap
  • PLCurve
  • TStiff

EStressCap finds the maximum bending moment for a specified axial load, either using the stress limits specified in AS 3600 or AS 5100, or an input stress limit.

EStressCap Output

EStressCap Output

The function input is similar to the EStress function:

EstressCap(input range, axial load, output column index, output row index, Allowstress, MSign, prestress, code, depth)
Function arguments in italics are optional

If the optional AllowStress argument is omitted the stress limit specified in AS 3600 is used if Code = 1 or is omitted, or the lower stress specified in AS 5100 for dead load only if Code = 2.  If AllowStress   > 0 then this value is used as the stress limit, and the Code value is ignored.

The other two functions are alternative methods of evaluating the tension stiffening effect. PLCurve uses a fictitious pre-compression strain applied to the reinforcement to model the effect of concrete shrinkage and creep strains, and the Eurocode 2 provisions for tension stiffening.  TStiff uses a concrete tensile stress block to model the effect of tension stiffening; see Tension Stiffening for more details.

RCdesign5c-2

PLCurve and TStiff output

PLCurve either requires creep and shrinkage data (to AS 3600 or Eurocode 2) to be input, or the fictitious prestress may be input directly.

TStiff requires details of the tensile stress block, or default values may be used.  See Tension Stiffening for details.

Additional input for PLCurve and TStiff

Posted in Beam Bending, Concrete, Excel, Newton, UDFs, VBA | Tagged , , , , , , , , | 1 Comment

Bridge Design, Beyond the Basics …

Posted on 03/06/2013 by dougaj4

… is the name of a series of full-day seminars that I will be presenting around Australia (together with two other bridge engineers), for the Concrete Institute of Australia, starting in Sydney next Wednesday, 12th June.

Click below for details:

Amongst the additional benefits to attendees are a 30% discount on the purchase price of the Strand7 API, and a free full working copy of my Strand7 API tools spreadsheet, allowing direct 2-way communication between Excel and Strand7 (normally only available to active clients). See the link above for full details.

Posted in Arch structures, Concrete, Newton | Tagged , , , , | Leave a comment

Recently discovered (and rediscovered) links

Posted on 25/05/2013 by dougaj4

Math ∩ Programming

– A place for elegant solutions (another Alfred Vachris link)

About the Author

My name is Jeremy Kun. I’m currently a mathematics PhD student at the University of Illinois in Chicago, and I did my undergraduate at Cal Poly San Luis Obispo in mathematics and computer science. I have a thorough background in computer science, but my most exciting experiences there always stemmed from elegant (and uncoincidentally mathematical) solutions to programming problems. This blog is a presentation of the interesting solutions I come across, and an exploration of the deeper mathematical ideas therein. Most often this means exploring the mathematical structure of a problem to lubricate the cogs of algorithm design. In seldom cases, this involves using programs to reason about mathematical theory.

Two new engineering sites:

DesignToEurocodes.com:  Forums, software and articles related to the use of Eurocodes, including concrete, steel, masonry, timber and seismic design.

Simple Supports – Started Feb 2013, articles on the science, maths, and practice of structural engineering.

Brian Potter works as a structural engineer, making sure buildings don’t fall down. He writes here about engineering, sometimes in the third person.

The BBC series on Climbing Great Buildings led me to:

  • Maths in the City and
  • The dome of St Paul’s Cathedral, London which led to:
  • The Arch Never Sleeps

  • It has been said that arches never sleep, that they are almost living in the way they adapt to change.  Using case studies of Ely Cathedral in England and Chartres in France, the tracks on this album examine the way in which the design of arches developed in the early middle ages, and use mathematical models to explore some of  the problems faced and overcome by the stone masons. This material makes up part of the course MST209, Mathematical methods and models.

And finally two blogs on The Universe and Everything that I linked to in 2011, then promptly forgot about:

Cosmic Horizons

My personal take on what’s going on within our Event Horizon. Mostly astronomical, often cosmological, usually quite grumpy.

In the Dark

A blog about the Universe, and all that surrounds it

Posted in Arch structures, Dome Structures, Newton | Tagged , , , , , , | Leave a comment

Using Goal Seek on a multi-column range

Posted on 20/05/2013 by dougaj4

Following a comment requesting help using my Goal Seek spreadsheet I have modified the macro to allow it to operate on a multi-column range.  The spreadsheet can be downloaded from GSeek.zip, including the solution to the problem described below.

The problem is to find the values of H such that Z = 1/(sqrt(H^2 + (A-x)^2 + (A-y)^2) for values of x and y between -10 and 10.

The stages to solve the problem are:

Set up a 21 x 21 table with copies of the formula reading x values from the row above the table, y values from the column to the left, and H values from a 21 x 21 range below:

Click for full-size view

Click for full-size view

The formula is entered in cell B13 as =SQRT(B36^2+($B$7-B$12)^2+($B$7-$A13)^2)
note the use of $ signs to specify x values in row 12 (B$12) and y values in column A ($A13).  The formula is then copied to the range B13:V33.  The Z values will be written to the range B36:V56, and this range must be filled with a starting value for Goal Seek.

The ranges for the target cells and “by changing” cells, and the target value are then entered in the grey shaded cells.  Note that I have entered the target as 1/Z, and modified the formula accordingly.  This gives better precision in the results, because the target is a larger number (and also makes the formulas a little simpler).

Gseek3-2b

After entering the data and ranges press Alt-F8 and run the GSeekA macro.  The range B13:V33 should show the target value (100.0) and the range B36:V66 will contain the Z values found by Goal Seek:

Click for full size view

Click for full size view

Posted in Excel, VBA | Tagged , , , | Leave a comment