2

I know that in Excel one can use rand() function to generate random numbers, and each time pressing F9, all rand() results on that sheet will be refreshed.

However, now I need to repeatedly generate random numbers using VBA, and control it so that the random numbers won't refresh unless the code orders so.

For example I have

  • range A1~A100, where I'd like to generate random numbers;
  • column B is the calculation place where some calculation by Excel "Solver" function is executed by VBA;
  • column C is where I would like to record 1,000 times calculation, each time from column B;
  • the final result is an average value of column C.

Now my problems are

  1. I don't know how to refresh cells in column A, using VBA, How can I do this?
  2. I notice that, during the VBA solver function, as cells in column B are updated, each time updating, the column A random numbers are refreshed -- how can I suppress this?
1
  • Note that if you do a lot of cycles, using the the data analysis toolpak and NTRand plugins are faster because they are programmed in C++.
    – vinnief
    Aug 28, 2013 at 8:11

4 Answers 4

4

The below VBA will stick a random number in cells 1 to 100 on the active sheet. Every time you run the macro the cells will be updated

 Sub GenerateRandom()
     Dim i As Long
     For i = 1 To 100
         Range("A" & i) = rnd()
     Next i 
End Sub

ALT+F8 to View Macros

Note: you can run other calculations (incl. formulas) and numbers in column A will not change. You have to manually change them or re-run the above macro. Hope this helps

3

I inserted a "Button" form control (have to check the box to enable the Developer tab under Options->Customize Ribbon->Main Tabs) onto the sheet, and ran this macro every time it is clicked:

Public Sub ran()
Dim a As Range
    For i = 1 To 100
        Dim r As Integer
        r = Int(Timer * Rnd())   'truncate the random number to an integer
        Cells(i, 1).Value = r    'set the value in the cell to this random number
    Next i
End Sub

Rnd() produces a number in the range [0, 1) so I just multiplied by the system timer (which uses hundredths of a second) and rounded it down to an integer using Int(). You could leave out the Int() if you want decimal places, and optionally make a min-max range you could do something like

r = (max - min) * r + min

...after you get the timer seed.

1
  1. I think Excel autoupdates all cells by default when some cells change. You can unset this in the settings
  2. First you should know that generating random numbers repeatedly in Excel can lead to very short cycles, after which the numbers are repeated. It might have changed with later versions of Excel (I doubt it though), but in Excel 97, I have seen a cycle of length 4! I.e. after four times generating 100 random numbers, it generated EXACTLY the SAME 100 random numbers. You will have to set the seed cleverly. Second, you can generate the needed VBA code by recording a Macro in which you generate random numbers using the data analysis toolpak (VBA) plugin random number generation: it fills the cells with numbers, not the rand() function. To add the toolpak in XL2007-VBA:

AddIns("Analysis ToolPak - VBA").Installed = True

to get 100 random numbers in cells A1:A100:

Application.Run "ATPVBAEN.XLAM!Random", ActiveSheet.Range("$A$1"), 1, 100 _ , 1, , 0, 1

3
  • thanks @vinnief , i guess you are right, each time cells are changed the random numbers are generated again, this could be suppressed by excel settings. so i'll adopt mehow and hoosieree 's vba code.
    – athos
    Aug 28, 2013 at 1:26
  • 1
    Further to my comment about the quality, There is a wikibook which discusses it, Ntrand has a plugin enabling you to use a high quality Mersenne twister random number generator, and Rotz et al. discuss the quality of the generators.
    – vinnief
    Aug 28, 2013 at 7:37
  • 1
    By the way this question probably fits better on stackoverflow, see this question about random numbers repeating themselves.
    – vinnief
    Aug 28, 2013 at 8:56
1

Pressing F9 won't generate new random values from your VBA function, but pressing Ctrl+Alt+F9 (Excel's "global recalc" key combination) will.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .