Writing an array function from VBA

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")
.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").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.

This entry was posted in Arrays, Excel, UDFs, VBA and tagged , , , . Bookmark the permalink.

One Response to Writing an array function from VBA

  1. Pingback: Some useful vba samples | User Friendly

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s