Microsoft Office Tutorials and References
In Depth Information
All the following formulas are valid, and the first two have the same effect:
This function might be useful for choosing lottery numbers, picking a winner from a list of names,
and so on.
This function is available on the companion CD-ROM. The filename is draw.xlsm .
A function that returns a VBA array
VBA includes a useful function called Array . The Array function returns a variant that contains an
array (that is, multiple values). If you’re familiar with array formulas in Excel, you have a head start
on understanding VBA’s Array function. You enter an array formula into a cell by pressing
Ctrl+Shift+Enter. Excel inserts curly braces around the formula to indicate that it’s an array formula.
See Chapter 3 for more details on array formulas.
It’s important to understand that the array returned by the Array function isn’t the
same as a normal array that’s made up of elements of the Variant data type. In other
words, a variant array isn’t the same as an array of variants.
The MonthNames function, which follows, is a simple example that uses VBA’s Array function in
a custom function:
MonthNames = Array(“Jan”, “Feb”, “Mar”, “Apr”,”May”, “Jun”, _
“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)
The MonthNames function returns a horizontal array of month names. You can create a multicell
array formula that uses the MonthNames function. Here’s how to use it: Make sure that the
function code is present in a VBA module. Then in a worksheet, select multiple cells in a row (start by
selecting 12 cells). Then enter the formula that follows (without the braces) and press