Rick Rothstein
03-27-2015, 11:02 PM
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...
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...
Public AlreadyRandomized As Boolean
and then include this at the beginning of each procedure that calls the Rnd function...
If Not AlreadyRandomized Then
Randomize
AlreadyRandomized = True
End If
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...
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...
Public AlreadyRandomized As Boolean
and then include this at the beginning of each procedure that calls the Rnd function...
If Not AlreadyRandomized Then
Randomize
AlreadyRandomized = True
End If