This page has been replaced. Click here to go to the new page.

It is often useful to create arrays in a VBA function and return them to Excel.

These examples assume that your Visual Basic module is using 1-based arrays, i.e., the first element
in the array is accessed by index 1, not 0.  Use the Option Base 1  statement to set this option.  Personally, I prefer 0-based array, because they conform to the conventions used by other programming languages (e.g, C and TAL).  However, many VB and VBA programmers use 1-based arrays, so I'll defer to the majority on this point.

Your user-defined functions can return arrays of data to Excel.  To do this, you first need to declare
a dimensionless array:

Dim Arr()

This statement acts as a placeholder for an array, but does not allocate any memory for the array. You must use the ReDim statement to allocate the array before you can use it.

One Dimensional Arrays

You may want to test the selection, to see if the user has selected a column or row array:

If Application.Caller.Rows.Count > 1 Then
    ReDim Arr (Application.Caller.Rows.Count)
Else
    ReDim Arr (Application.Caller.Columns.Count)
End If

Your function can then fill out the array with the proper values.  By default, VBA arrays are returned as a horizontal, or row, arrays.  To return a Row array, set your function to the value of the array:

MyFunction = Arr

To return a vertical, or column, array, you'll have to transpose the array, from a row to a column:

MyFunction = Application.Worksheetfunction.Transpose(Arr)

Two Dimensional Arrays

To return two-dimensional arrays, you must redimension the original array into two dimensions:

R = Application.Caller.Rows.Count
C = Application.Caller.Columns.Count

ReDim Arr(R,C)

Your function can then fill out the array with the proper values.  To return the array, set your
function's value to the array:

MyFunction = Arr

Sample Code For Dimensioning An Array

This sample code determines how the function was called, using the Application.Caller object.  If it was called by a one dimensional array, it determines whether it was called into a row or a column of cells.   It calls ReDim to allocate an array of the appropriate size.  At then end of the function, it returns the array in the proper format.

Public Function MyFunction()

Dim Arr()
Dim R as Integer
Dim C as Integer
Dim ReturnColumn As Boolean

R = Application.Caller.Rows.Count
C = Application.Caller.Columns.Count

ReturnColumn = False
If R > 1 Then
   If C > 1 Then
      ReDim Arr(R,C)
   Else
      ReDim Arr(R)
      ReturnColumn = True
   End If
Else
   ReDim Arr(C)
End If

'
'... Your Code Here
'

If ReturnColumn = True Then
  MyFunction = Application.Worksheetfunction.Transpose(Arr)
Else
  MyFunction = Arr
End If 

End Function