Microsoft Office Tutorials and References
In Depth Information
Returning an array from a function
Most functions that you develop with VBA return a single value. It's possible, however, to write a function that
returns multiple values in an array.
Part IV deals with arrays and array formulas. Specifically, these chapters provide ex-
amples of a single formula that returns multiple values in separate cells. As you'll see,
you can also create custom functions that return arrays.
VBA includes a useful function called Array. The Array function returns a variant that contains an array. It's im-
portant to understand that the array returned is not the same as a normal array composed of elements of the vari-
ant type. In other words, a variant array is not the same as an array of variants.
If you're familiar with using array formulas in Excel, you have a head start understanding the VBA Array func-
tion. You enter an array formula into a cell by pressing Ctrl+Shift+Enter. Excel inserts brackets around the for-
mula to indicate that it's an array formula. See Chapters 14 and 15 for more details on array formulas.
The lower bound of an array created by using the Array function is, by default, 0.
However, the lower bound can be changed if you use an Option Base statement.
The following MONTHNAMES function demonstrates how to return an array from a Function procedure:
Function MONTHNAMES() As Variant
MONTHNAMES = Array( _
“Jan”, “Feb”, “Mar”, “Apr”, _
“May”, “Jun”, “Jul”, “Aug”, _
“Sep”, “Oct”, “Nov”, “Dec”)
Figure 26-7 shows a worksheet that uses the MONTHNAMES function. You enter the function by selecting
A4:L4 and then entering the following formula: