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
Your user-defined functions can return arrays of data to Excel. To do this, you
first need to declare
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
Then, you'll want to redimension the array to the number of rows or columns that the
users has selected:
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
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:
To return a vertical, or column, array, you'll have to transpose the array, from a row
to a column:
Two Dimensional Arrays
To return two-dimensional arrays, you must redimension the original array into two
Your function can then fill out the array with the proper values. To return the
array, set your
MyFunction = Arr
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()
R = Application.Caller.Rows.Count