Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

# Thread: Using rnd() in VBA

1. Senior Member
Join Date
22nd November 2004
Location
London
Posts
133

## Using rnd() in VBA

Hi, I am using a sheet which is generating a lot of uniform random numbers. Therefore, rather than using the activecell.formula = "-rand()" method I have been trying to use rnd() instead. However, I am getting the feeling that the numbers generated the latter way are not completely random. I am going of the fact that I generating two functions and then plotting them, and the plots are looking quite different. Does anyone know if this could in fact be the case?

Excel Video Tutorials / Excel Dashboards Reports

2. ## Re: Using rnd() in VBA

Yes, it is possible. From VBA Help
Syntax

Rnd[(number)]

Remarks

The Rnd function returns a value less than 1 but greater than or equal to zero.

The value of number determines how Rnd generates a random number:

For any given initial seed, the same number sequence is generated because each successive call to the Rnd function uses the previous number as a seed for the next number in the sequence.

Before calling Rnd, use the Randomize statement without an argument to initialize the random-number generator with a seed based on the system timer.
Last edited by thomach; August 23rd, 2005 at 08:32.

3. Senior Member
Join Date
22nd November 2004
Location
London
Posts
133

## Re: Using rnd() in VBA

Thanks, however I am seeing almost the same results when I use the Randomize statement as well. Oh well, guess I will settle for a sheet that runs a little slower.

Excel Video Tutorials / Excel Dashboards Reports

4. ## Re: Using rnd() in VBA

Say what?

I think the RNG in VBA is pretty decent now. Should certainly give reasonable approximations for uniform distributions.

This just sounds odd.

5. Senior Member
Join Date
22nd November 2004
Location
London
Posts
133

## Re: Using rnd() in VBA

Without going into too much detail, my sheet generates two random numbers. The first random numbers outcome is like tossing a coin; if the result is greater than a predetermined value it generates a time value from a particular distribution, if its less it uses another distribution. The second random number is used as the value of x to get the PDF of that particular distribution.
I have used two methods of generating random numbers:
method 1:
VB:
```Range("Q1").Select
ActiveCell.Formula = "=rand()"
dblUniformValue = Range("Q1").Value
Range("A2").Offset(x, 0).Select
ActiveCell.Formula = "=rand()"
dblX = Range("A2").Offset(x, 0).Value

```
method 2:
VB:
```Randomize
dblUniformValue = Rnd()
Randomize
dblX = Rnd()

```
To test my hunch I generated 400 observations 100 hundred times with each method. The only thing that was different was the lines of code above. I took the sum of the result of the two functions which are generated and recorded them. I then ran independant samples t-tests on the data. The result was that for both functions the methods of generating the random number proved to be statistically significant at the p<0.001 level, with an eta squared of 0.0761, 0.0732 respectively.

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno