Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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 repeated 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 are 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?
  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 could i suppress this?
share|improve this question
    
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 '13 at 8:11

3 Answers 3

up vote 2 down vote accepted

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

share|improve this answer

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.

share|improve this answer
  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

share|improve this answer
    
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 '13 at 1:26
    
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 '13 at 7:37
    
By the way this question probably fits better on stackoverflow, see this question about random numbers repeating themselves. –  vinnief Aug 28 '13 at 8:56

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.