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
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
To return to the worksheet thereafter, press
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.
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)
Now in any cell, you’d enter your UDF just like this, and as seen in the next picture for cell A3.