Microsoft Office Tutorials and References
In Depth Information
Dim Nums(1 To 100) As Double
Dim i as Integer
For i = 1 To 100
Nums(i) = Rnd * 1000
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:
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
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:
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.