Microsoft Office Tutorials and References
In Depth Information
Generating Random Numbers
Worksheet function data types
You may have noticed some differences in the data types used for functions and arguments so
far. For instance, in STATFUNCTION , the variable rng was declared as a Variant , while the
same variable was declared as a Range in STATFUNCTION2 . Also, the former’s return value was
declared as a Variant , while the latter’s is a Double data type.
Data types are two-edged swords. They can be used to limit the type of data that can be passed
to, or returned from, a function, but they can also reduce the flexibility of the function. Using
Variant data types maximizes flexibility but slows execution speed.
One of the possible return values of STATFUNCTION is an error, in the Case Else section of
the Select Case statement. That means that the function can return a Double data type or
an Error . The most restrictive data type that can hold both an Error and a Double is a
Variant (which can hold anything), so the function is typed as a Variant . On the other hand,
STATFUNCTION2 does not have any provision for returning an error, so it’s typed as the more
restrictive Double data type. Numeric data in cells is treated as a Double even if it looks like an
The rng arguments are also typed differently. In STATFUNCTION2 , the Address property of
the Range object is used. Because of this, you must pass a Range to the function, or it will
return an error. However, there is nothing in STATFUNCTION that forces rng to be a Range . By
declaring rng as a Variant , the user has the flexibility to provide inputs in other ways. Excel
will happily try to convert whatever it’s given into something it can use. If it can’t convert it, the
result will surely be an error. A user can enter the following formula:
Neither argument is a cell reference, but Excel doesn’t mind. It can find the minimum of an array
constant as easily as a range of values. It works the other way too, as in the case of the second
argument. If a cell reference is supplied, Excel will try to convert it to a String and will have no
problem doing so.
In general, you should use the most restrictive data types possible for your situation while
providing for the most user flexibility.
Generating Random Numbers
This section presents functions that deal with random numbers. One generates random numbers
that don’t change. The other selects a cell at random from a range.
The functions in this section are available on the companion CD-ROM. The filename is
random functions.xlsm .