**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()`

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.