Page 192 - Excel Progamming Weekend Crash Course

k540629 ch13.qxd 9/2/03 9:34 AM Page 167

Session 13 — Programming with Excel’s Built-In Functions 167

Function Description
Weekday Returns the day of the week (1–7) for a specified date.
Year Returns the year of a specified date.

Math and Trig Functions
Excel’s math and trig (trigonometry) functions perform a variety of related calculations. For
example, all the trig functions, such as sine and cosine, are available. Logarithms, matrix
operations, and exponentials, just to name a few, are also available. One math function that
can be useful in various situations is RAND, which returns pseudo-random numbers. The syn-
tax is:

RAND()

The return value is a pseudo-random number equal to or greater than 0 and less than 1.
Note that the return value of this function changes each time the worksheet is recalculated.
If your programs need a table of data for testing purposes, you can fill the cells with the
RAND function to get what you need. If you need numbers in a range other than 0–1, add a
multiplier to the formula. For example, the formula

=RAND()*100
returns random values in the range 0–100.

Nonchanging Random Numbers

Excel’s RAND function has the limitation that its return value changes each
time the worksheet is recalculated. To fill cells with nonchanging random num-
bers, you can use VBA’s RND function. RND works the same way, returning a
pseudo-random number equal to or greater than 0 and less than 1; however, by
using a short program to fill the cells with these values, you’ll get a table of
data that does not change with each worksheet recalculation. The program in
the following listing does just this.

Public Sub FillRangeWithRandomValues(r As Range, max As Single)

‘ Fills the specified worksheet range with random
‘ numbers in the range 0-max.

Dim i As Integer, j As Integer
For i = 1 To r.Columns.Count
For j = 1 To r.Rows.Count
r.Cells(j, i) = max * Rnd()
Continued