Results 1 to 2 of 2

Thread: VB's Randomize Function Should Be Run Only Once Per Session

  1. #1
    Forum Guru Rick Rothstein's Avatar
    Join Date
    Feb 2012
    Posts
    659
    Rep Power
    13

    VB's Randomize Function Should Be Run Only Once Per Session

    When VB programmers learn of the Randomize function (it initializes the random-number generator), their tendency is to use it before every use of the Rnd function (it returns a random number) thinking this will insure as much randomizing as possible. But they are wrong! In actuality, doing that produces less randomization, not more. Each time you execute the Randomize statement, you reset the sequence that the Rnd function draws from. Because your function only uses one Rnd per call, it effectively uses the first number in the sequence set by the Randomize statement for each call to your function… all the rest of the numbers in each sequence never get used. There are less total sequences than there are numbers in any one sequence which is what accounts for my above claim.

    This can all be demonstrated visually which I have done in the attached file. When you open the file, a UserForm will be displayed. Simply click anywhere in the main body of the UserForm to generate the display... click again to generate a new display. The code behind the UserForm simply sets a random color for each pixel within a rectangular area of the UserForm... the rectangle on the left was produced by executing the Randomize statement once before randomly coloring the pixels in it; the rectangle on the right was produced by executing the Randomize statement for each iteration of the loop that randomly colors the pixels. Which looks more random to you?

    So, you are probably wondering... "How do I manage executing Randomize only once per session?" There are a couple of ways. You could add the Randomize statement to your workbook's Open event, but that kind of ties your code to the workbook. This next way ties it to the code procedure itself making it easy to send the code to another individual (say, by email) without having to send an entire workbook. All you have to do is include a code structure like this at the beginning of your routine...

    Code:
      Static AlreadyRandomized As Boolean
      If Not AlreadyRandomized Then
        Randomize
        AlreadyRandomized = True
      End If
    The Static keyword will keep the variable "alive" even after the routine has closed, but it will be "alive" only for the next time that particular routine is called. If you will have more than one routine that calls the Rnd function, then instead of making the AlreadyRandomized variable Static, you need to make it global instead. You do that by declaring it Public at the beginning of the module where your multiple Rnd-calling routines will be placed. So you would put this at the top of the module...

    Code:
    Public AlreadyRandomized As Boolean
    and then include this at the beginning of each procedure that calls the Rnd function...

    Code:
      If Not AlreadyRandomized Then
        Randomize
        AlreadyRandomized = True
      End If
    Attached Files Attached Files
    Last edited by Rick Rothstein; 05-27-2015 at 12:25 PM.

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Very insightful indeed Rick. Thanks for sharing this.
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 6
    Last Post: 09-03-2019, 10:26 AM
  2. UDF (user defined function) replacement for Excel's DATEDIF function
    By Rick Rothstein in forum Rick Rothstein's Corner
    Replies: 21
    Last Post: 03-07-2015, 09:47 PM
  3. Run Time error '9': Subscript out of range
    By antonio in forum Excel Help
    Replies: 4
    Last Post: 03-26-2013, 01:53 AM
  4. Selecting workbook to run macro
    By Rajesh Kr Joshi in forum Excel Help
    Replies: 2
    Last Post: 08-24-2012, 08:21 PM
  5. Click Run cycle
    By PcMax in forum Excel Help
    Replies: 6
    Last Post: 11-01-2011, 04:50 AM

Posting Permissions

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