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
.Resize(NumArrayRows, NumArrayColumns).Name = "MyNamedRange"
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.
Pingback: Some useful vba samples | User Friendly