Microsoft Office Tutorials and References

In Depth Information

**Function Examples**

Sub MakeList()

Dim Nums(1 To 100) As Double

Dim i as Integer

For i = 1 To 100

Nums(i) = Rnd * 1000

Next i

MsgBox SumArray(Nums)

End Sub

Notice that the
SumArray
function doesn’t declare the data type of its argument (it’s a variant).

Because it’s not declared as a specific numeric type, the function also works in your worksheet

formulas in which the argument is a
Range
object. For example, the following formula returns

the sum of the values in A1:C10:

=SumArray(A1:C10)

You might notice that, when used in a worksheet formula, the
SumArray
function works very

much like Excel’s SUM function. One difference, however, is that
SumArray
doesn’t accept

multiple arguments. Understand that this example is for educational purposes only. Using the

SumArray
function in a formula offers absolutely no advantages over the Excel SUM function.

This example, named
array argument.xlsm
, is available on the companion CD-ROM.

A function with optional arguments

Many of Excel’s built-in worksheet functions use optional arguments. An example is the LEFT

function, which returns characters from the left side of a string. Its syntax is

LEFT(text,num_chars)

The first argument is required, but the second is optional. If the optional argument is omitted,

Excel assumes a value of 1. Therefore, the following two formulas return the same result:

=LEFT(A1,1)

=LEFT(A1)

The custom functions that you develop in VBA also can have optional arguments. You specify an

optional argument by preceding the argument’s name with the keyword
Optional
. In the

argument list, optional arguments must appear after any required arguments.