Using Index() as an array function

Posted on 23/06/2013 by dougaj4

As promised, this post looks at using the Excel built in Index() function to operate on arrays; it also provides a VBA user defined function (UDF), VBIndex, which simplifies working with arrays within VBA, and also provides improved functionality when called from the spreadsheet.   A spreadsheet with full open-source code and the examples given below can be downloaded from:  VBIndex.

The Excel Index function returns the value of a single cell when provided with a row number and a column number.  If either the row or the column numbers are omitted then the function will return the full column or row respectively.  The screenshot below shows Index used to return the 4th row and the 3rd column of the sample data (see Useful Gayaan for more examples):

Using Index() to return an array

Using Index() to return an array

Note that the function must be entered as an array function to return all the data; see Using Array Formulas for details.

As well as a single value or omitted (or 0), the Index function will accept an array to define both the rows or columns to be returned.  The array may be a spreadsheet range (either a single row or column), or a list of values surrounded by {}.

Returning specified rows or columns with Index() and VBIndex()

Returning specified rows or columns with Index() and VBIndex()

The screenshot above shows the use of the UDF VBIndex() and the built-in Index() function. Note that when using the Index() function the list of rows must be entered as a column array (either a single column range, or values separated by a semi-colon), and the list of columns must be entered as a row array (either a single row range, or values separated by a comma). If this is not done correctly the returned array will not be the required values, as shown shaded in blue and pink.
To simplify the input, and also to simplify the use of the function from within VBA routines, I have written the UDF VBIndex, which will accept either a row or column array (or range) to specify both rows or columns. Examples are shown returning the 1st, 2nd, and 4th rows and columns with a variety of different forms of input.

Posted in Arrays, Excel, UDFs, VBA | Tagged , , , , , | 3 Comments

Two new blogs

Posted on 17/06/2013 by dougaj4

Two new Excel/computer related blogs that I have found recently are:

Useful Gyaan (Sharing Stuff to Make life @ work easier)

and

MS EXCEL UNPLUGGED (which looks like a good resource on using pivot tables)

In the near future I will be having a closer look at:
VBA Trick of the Week :: Slicing an Array Without Loop – Application.Index
from Useful Gyaan.

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

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