No announcement yet.

Using rnd() in VBA

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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?

  • #2
    Re: Using rnd() in VBA

    Yes, it is possible. From VBA Help



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


    • #3
      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.


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


        • #5
          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:
                  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:
                  dblUniformValue = Rnd()
                  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.