467,154 Members | 866 Online

# VBA-Random Numbers

 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. Mar 3 '06 #1
• viewed: 23964
Share:
3 Replies
 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) Mar 10 '06 #2
 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 Apr 21 '06 #3
 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 Sep 10 '07 #4

 12 posts views Thread by guy lateur | last post: by reply views Thread by andreas | last post: by 1 post views Thread by Igor Mendizabal | last post: by 22 posts views Thread by Illya Havsiyevych | last post: by 41 posts views Thread by Matt Alanzo | last post: by 6 posts views Thread by Terry Bell | last post: by 1 post views Thread by Igor Mendizabal | last post: by 18 posts views Thread by Ann Scharpf via AccessMonster.com | last post: by 15 posts views Thread by Cheryl Langdon | last post: by 3 posts views Thread by MMcCarthy | last post: by reply views Thread by SwissProgrammer | last post: by 1 post views Thread by SwissProgrammer | last post: by 2 posts views Thread by SwissProgrammer | last post: by 2 posts views Thread by Firas Rihan | last post: by 3 posts views Thread by CD Tom | last post: by reply views Thread by AjayGohil | last post: by 4 posts views Thread by Petrol | last post: by 1 post views Thread by vinayak1209 | last post: by reply views Thread by Raftar | last post: by