# Tom’s Tutorials For Excel: Entering a Static Random Number

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:

###### 3 comments on “Tom’s Tutorials For Excel: Entering a Static Random Number”
1. sandeep kothari says:

Very useful tips & codes & UDF.

2. Rick Rothstein says:

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.