Microsoft Office Tutorials and References

In Depth Information

**Function Examples**

All the following formulas are valid, and the first two have the same effect:

=DrawOne(A1:A100)

=DrawOne(A1:A100,False)

=DrawOne(A1:A100,True)

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:

Function MonthNames()

MonthNames = Array(“Jan”, “Feb”, “Mar”, “Apr”,”May”, “Jun”, _

“Jul”, “Aug”, “Sep”, “Oct”, “Nov”, “Dec”)

End Function

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

Ctrl+Shift+Enter:

{=MonthNames()}