Tom’s Tutorials For Excel: Entering a Static Random Number
I previously posted these examples of data entry using random numbers.
In each of those examples, by design, the random numbers returned by the RAND function were volatile. That is, any change to worksheet data would recalculate those formulas, which is what you would have wanted to happen for those examples.
In some project designs, you need to enter a static random number that remains unchanged until and unless you want to change it. For that, an approach other than the RAND function is needed.
In the next picture, the numbers in cells A1, A2, and A3 were each produced in a static random fashion. The next three sets of steps show how this was accomplished.
Example 1 — the number in cell A1.
The quickest and easiest way to enter a static random number is to use the Immediate window.
From your keyboard, press Alt+F11 then press Ctrl+G
I typed this line of code into the Immediate window…
Range("A1").Value = Format(Rnd() * 99 + 1, "000")
…to produce a static random number between 1 and 100 in cell A1.
To actually place that number in cell A1, hit the Enter key.
To return to the worksheet thereafter, press Alt+Q.
Final result for Example 1:
Example 2 — the number in cell A2.
Following the same steps as in Example 1, from your keyboard, press Alt+F11 then press Ctrl+G to access the Immediate window. Cell A2 also holds a static random number between 1 and 100, but with three decimal places using this statement:
Range("A2").Value = Format(Rnd() * 99 + 1, "0.000").
From there, same as with Example 1, press Enter to place that random number into cell A2, and then press Alt+Q to return to your worksheet. Here’s the final result for Example 2:
Example 3 — the number in cell A3.
Rather than going back and forth between your worksheet and the Immediate window, and further to place a static random number in any cell, you can employ a special kind of formula, called a User Defined Function (UDF), to do the job. There are a couple of one-time steps to take, and then you are home free to use the UDF in that workbook anytime thereafter.
Step 1
From your worksheet, press Alt+F11 and then press Ctrl+R.
Step 2
In the “Project VBAProject” window, select the bolded name of your workbook. Then, from the menu at the top of the screen, click Insert > Module.
Step 3
In the new module created in Step 2, I entered this UDF to return a number between 1 and 365:
Function StaticRand() As Double
StaticRand = Int(Rnd() * 365)
End Function
Step 4
Return to your worksheet by pressing Alt+Q.
Step 5
Now in any cell, you’d enter your UDF just like this, and as seen in the next picture for cell A3.
=StaticRand()
Final result for Example 3:
Very useful tips & codes & UDF.
Thank you!
In Step 3 for your UDF, you wrote… In the new module created in Step 2, I entered this UDF to return a number between 1 and 365:
Function StaticRand() As Double
StaticRand = Int(Rnd() * 365)
End Function
Actually, your UDF is returning a number between 0 and 364. The Rnd function returns a value less than 1 but greater than or equal to zero. If it returned 0, then 0 times 365 is still 0 and, of course, Int(0) equals 0. On the other hand, if the max value is less than 1, then that number times 365 will be less than 365 and so taking the Int of it will return a maximum value of 364. The fix is simple of course… just add 1 to your calculation.