Stupid Random Numbers Keep Recalculating

Have you ever gotten annoyed by how the RAND and RANDBETWEEN function keeps recalculating?

I was trying to build a data model to test some business logic, so I used RANDBETWEEN to generate some random numbers. The problem was that any activity in my spreadsheet caused the random numbers to reset to new ones. Arghh!

.

This is because RAND and RANDBETWEEN are ‘volatile’ functions; they recalculate on each change of the spreadsheet. In most cases the volatility doesn’t affect me, but in this scenario I’d like my random numbers to stay put and recalculate only on demand.

.

I figure I had three options:

.

Option 1: Give my Random Numbers the Copy-Paste Special-Values Treatment

Copy the range with the random numbers, Right-Click, select Paste Special, and then choose Values.

This typically works, but it actually blows away the formulas. In my case I wanted to keep my formula because I did want to recalculate random numbers – but only when I say so.

.

Option 2: Set the Workbook to Manual Calculation

Choose File->Excel Options->Formulas then set the Workbook Calculation mode to Manual.

This basically stops all calculation in the workbook (effectively freezing all volatile functions) until I hit F9. This wouldn’t work for me, as I need my other calculations to keep working without me hitting F9.

.

Option 3: Create my Own Non-Volatile Version of the RANDBETWEEN Function

Creating my own function allows me to get the benefits of RANDBETWEEN without the volatility.

.

I decide to go with Option 3 and create my own function. I entered this code into a standard module, which effectively mimics RANDBETWEEN. Note that this code accepts a Low and High range.

Function RandomBetween(Low As Long, High As Long)
Randomize
RandomBetween = Int(Rnd * (High + 1 – Low)) + Low
End Function

.

Now all I have to do is use my function to generate random numbers.

Because it’s a non-volatile function, my random numbers remain static until I choose to change them by hitting F2 and Enter. I’m happy.

.

.

By the way – here’s the code that mimics RAND

Public Function RandomNumber()
Randomize
RandomNumber = Rnd()
End Function

.

.

Bonus RAND Trivia

Did you know that you can enter =RAND() into a Word document to generate test text?

.

You can even get fancy and specify how many paragraphs and sentences in the paragraphs.

.

You can do the same in PowerPoint.

.

.

Like most everything else in Word and PowerPoint, this is practically useless.

12 thoughts on “Stupid Random Numbers Keep Recalculating

  1. Patrick

    And just for giggles, use this in Word:

    =lorem(5,3)

    “Like most everything else in Word and PowerPoint, this is practically useless.”

    Of course!

  2. datapig

    Patrick: I didn’t word Word did that!

    I’ve been going to Lorem Ipsum Generators online for years.

    I learned something new…Thanks!

  3. Javier Jarabo

    Another solution:
    In cell A1 introduce the control value
    1: Calculate the random
    In B6 (for example) introduce the formula:
    =SI($A$1=1;ALEATORIO.ENTRE(50;100);B6+0)

    Beware the circular reference! (allows iterations if you want to save trouble)

  4. KF

    Here’s my list for Office 2010:

    Word and Outlook
    =lorem()
    =rand()
    =rand.old()

    PowerPoint
    =lorem()
    =rand()

  5. Dick Kusleika

    From Rick Rothstein, who still can’t post comments on this site:

    I would suggest changing your RandomBetween and RandomNumber functions so that the Randomize statement is executed only once per Excel session; here is what I mean using your RandomNumber function as an example…

    Public Function RandomNumber()

    Static AlreadyRandomized As Boolean

    If Not AlreadyRandomized Then

    Randomize

    AlreadyRandomized = True

    End If

    RandomNumber = Rnd()

    End Function

    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. This really reduces the randomness of the numbers produced by the Rnd function. This can be demonstrated with the following. Go into the VB editor and insert a UserForm into your project and put the following code into the UserForm’s code window. The code simple sets a random color within a rectangular area of the UserForm. Okay, now run the UserForm and click it… 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? Here is the code…

    Private Declare Function FindWindow Lib "user32" _

    Alias "FindWindowA" _

    (ByVal lpClassName As String, _

    ByVal lpWindowName As String) As Long

    Private Declare Function SetPixel Lib "gdi32" _

    (ByVal hDc As Long, _

    ByVal X As Long, _

    ByVal Y As Long, _

    ByVal crColor As Long) As Long

    Private Declare Function GetPixel Lib "gdi32" _

    (ByVal hDc As Long, _

    ByVal X As Long, _

    ByVal Y As Long) As Long

    Private Declare Function GetDC Lib "user32" ( _

    ByVal hWnd As Long) As Long

    Private Declare Function ReleaseDC Lib "user32" ( _

    ByVal hWnd As Long, _

    ByVal hDc As Long) As Long

    Private Sub PaintPixels()

    Dim hWnd As Long, hDc As Long

    Dim TP As Long, LT As Long

    Dim X As Long, Y As Long

    Dim Colr As Long

    Const Z As Long = 255

    Const C As Long = 16711680

    Me.Left = 10: Me.Top = 10

    Me.Width = (Z * 2) * 0.75 + 45: Me.Height = Z * 0.75 + 60

    ' if form is too small, change 0.75 to 1 or 1.25

    hWnd = FindWindow("ThunderDFrame", Me.Caption)

    hDc = GetDC(hWnd)

    TP = Me.Top + 15

    LT = Me.Left + 10

    Randomize

    For Y = TP To TP + Z

    For X = LT To LT + Z

    Colr = Rnd * C

    SetPixel hDc, X, Y, Colr

    Next

    Next

    LT = LT + Z + 15

    For Y = TP To TP + Z

    For X = LT To LT + Z

    Randomize

    Colr = Rnd * C

    SetPixel hDc, X, Y, Colr

    Next

    Next

    ReleaseDC hWnd, hDc

    End Sub

    Private Sub UserForm_Activate()

    Me.Caption = "Click me to (re-) PaintPixels"

    PaintPixels

    End Sub

    Private Sub UserForm_Click()

    ' Me.Repaint

    PaintPixels

    End Sub

  6. Evan

    thank you thank you thank you
    I have been playing around for hrs trying to work this out

  7. Kriszti

    This article exactly addresses my problem, so I was very happy to find a possible solution here. 🙂 However, I keep getting a #NAME error in Excel 2013.

    – function name is not the same as module name
    – I was working in the same file (.xlsm) all the time
    – I haven’t made any typos when calling the function in Excel
    – I have enabled macros in Excel. However, when trying to test your code in the “Immediate Window” in VBE, it tells me that I should enable macros in the host application (in this case, in Excel)…

    Do you have any idea how to settle this?
    Thank you very much in advance!

  8. Kriszti

    I have recreated the module in an empty worksheet.

    In this, it worked flawlessly.

    Then I re-opened the old file, removed the old module(s), created a new one, and voilá, it works, but I will never understand why. I just thought I should share this, should anyone run into the very same problem.

  9. Rusty Shackleford

    I somehow got a syntax error when I tried to use it. I copied your code and pasted it into the module, but for some reason it didn’t like the “-” sign in the third line. I removed it and added it back, which fixed the problem. I have no clue why it was being so picky, but I figured I’d let you guys know about it.

Leave a Reply

Your email address will not be published. Required fields are marked *