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”)

End Function

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:

{=MONTHNAMES()}