Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Using rnd() in VBA

  1. #1
    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. #2
    Join Date
    22nd April 2003
    Location
    Virginia, USA
    Posts
    5,035

    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 09:32.
    Best Regards,
    Tom
    ---------------------------
    Please help Oz share knowledge among all users by posting your questions in a public forum rather than using a Private Message.

  3. #3
    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. #4
    Join Date
    10th February 2003
    Location
    Palm Beach, Florida
    Posts
    1,590

    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.
    Sub All_Macros(Optional control As Variant)

  5. #5
    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


Thread Information

Users Browsing this Thread

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

Bookmarks

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