VBA-Random Numbers

[JoelPJustice]

I am working through a VBA book by myself to help and try and improve my skills. However, the book does not give you solutions to certain problems. I have worked through this problem up until bullet point 3. Here is the code I came up with up until now.

Function RandomNormal(Mean As Double, StdDev As Double) As Double
Application.Volatile
Randomize
RandomNormal = (StdDev * Sqr(-2 * Log(Rnd)) * Cos(2 * 3.141596 * Rnd)) + Mean

End Function

I am having trouble understanding how to generate the random numbers into the range to figure out the standard deviation and the mean on the actual spreadsheet. I know this is probably very basic but I am a beginner in VBA and am learning it on my own. Please help!

Joel Justice

�� Change the name of Worksheet2 in your workbook to “RandomNormal”
�� Go to the Visual Basic Editor and create a new function that will generate Random Normal Deviates. These are random numbers with a given Mean and Standard Deviation. Remember your statistics? The function’s name should be “RandomNormal” and have two arguments, the Mean (double) and StdDev (double) and the function should return a Double as the value. The formula to create a random number with the normal (Guassian) distribution is the following:
R1 = Random Number between 0 and 1 (use the Rnd function)
R2 = Random Number between 0 and 1 (use the Rnd function)
S = Standard Deviation (input)
M = Mean (input)
Pi = Pi (3.1415926)
X = Random Number you generate (output)
X = S * Sqr(-2 * Log(R1)) * Cos(2 * Pi * R2) + M
�� You can test your function in the Immediate Window to see that it works properly.
�� On the Worksheet “RandomNormal” put the label “Mean” in cell A1, and the label “StdDev” in cell A2. Name cell B1 “Mean” and name cell B2 “StdDev”. Put an outline around both of these cells as input and unprotect them.
�� On the Worksheet “RandomNormal” create a range from Cells D5:P15 and name this range “RandomNumbers”.
�� In cell C1 insert a formula the computes the actual Mean (Average) of the Range “RandomNumbers”. In Cell C2 insert a formula the computes the actual Standard Deviation of the range “RandomNumbers”. When you perform the next steps those outputs should tell you if you doing things right. These values should be close to (but not exactly equal to) the input Mean and StdDev.
�� Create a subroutine in the Visual Basic Editor called “GenerateNormals”. This Sub will examine the range “RandomNumbers” to determine the number of rows and columns in the range. Using a nested loop, fill this range with Random
Normal Numbers using the “Mean” and “StdDev” that you defined on that page. Note: I will change the definition of the range and fill in values for Mean and StdDev. Place a Command Button on the page to execute the subroutine GenerateNormals.

[zagarat]

If I understand you correctly then the following will fill the area:

Dim i As Integer, j As Integer, rngArea As Range
Set rngArea = Range("RandomNumbers")
For i = 1 To rngArea.Columns.Count
For j = 1 To rngArea.Rows.Count
rngArea.Item(j, i).Value = RandomNormal(Range("B1").Value ,Range("B2").Value)
Next j
Next i

The formulae in the spreadsheet that you want are probably:
=AVERAGE(RandomNumbers)
and
=STDEV(RandomNumbers)

[pgleckman]

I am working through a VBA book ...

The formula: X = S * Sqr(-2 * Log(R1)) * Cos(2 * Pi * R2) + M works very nicely. But where did you find it? Even "Numerical Recipes in C" didn't give it.
-Philip

[pansdad]

The formula: X = S * Sqr(-2 * Log(R1)) * Cos(2 * Pi * R2) + M works very nicely. But where did you find it? Even "Numerical Recipes in C" didn't give it.
-Philip

It is called the Box-Muller Transformation
Box, G.E.P. and Muller, M. E. "A Note on the Generation of Random Normal Deviates." Ann. Math. Stat. 29, 610-611, 1958.

Note that using Sin(2*Pi*R2) instead of Cos gives a second Normal Deviate, so for two uniform random numbers you get 2 normal ones.

Victor