generate a random number to enter in a cell

B

brianfosterblack

Active Member
Joined
Nov 1, 2011
Messages
251
I am am looping through a range on my computer (Excel 2013) and where I find a number 1 I want to replace it with a random number between the bottom number in Cell I9 and the top number in cell I11
So what I need my code to do is Activecell.value= "Randbetween Range ("I9").value and Range ("I11").Value
Can someone help with the code to do this?
The number generated must be a whole number
 
Last edited:
Sort by date Sort by votes
shg said:
The VBA Rnd() function is about 1000 times faster in VBA than WorksheetFunction.Rand
Click to expand...

Actually, it is an "infinite times" faster, since there is no WorksheetFunction.Rand -- at least, not in Excel 2010. ;)

On my computer (YMMV), Worksheetfunction.RandBetween(lo,hi) is 8 to 16 times slower than Int((hi-lo+1)*Rnd)+lo, including time to call QueryPerformanceCounter. We are talking about less than 7 usec.

It's a range because the Rnd method clocks in at 1 "performance counter tick", the minimum timer resolution. So it could be close to 2 "performance counter ticks".

In contrast, Int((hi-lo+1)*[RAND()])+lo is 40 to 79 times slower than the Rnd expression. [RAND()] calculates in the Excel thread, whereas Worksheetfunction.RandBetween calculates in the VBA thread.

And Evaluate(fml), where fml is "RANDBETWEEN(" & lo & "," & hi & ")", is 45 to 90 times slower than the Rnd expression. We are talking about less than 34 usec. Again, Evaluate calculates in the Excel thread.

All are a far cry from "1000 times faster".

Check it out!

Code: 
Option Explicit

Public Declare Function QueryPerformanceFrequency Lib "kernel32" _
   (ByRef freq As Currency) As Long
Public Declare Function QueryPerformanceCounter Lib "kernel32" _
   (ByRef cnt As Currency) As Long
   
Private freq As Currency, df As Double

Sub doit()
Const n As Long = 10000
Dim lo As Long, hi As Long
Dim st As Single, et As Single
Dim i As Long, x As Long, s As String, fml As String
Dim dt(1 To n) As Double
Dim sc As Currency, ec As Currency, tc As Currency, dc As Currency
Dim minDC As Currency, maxDC As Currency
Dim wf As Object

Set wf = WorksheetFunction
lo = 2
hi = 1000000

Randomize
minDC = 922337203685477.5807@
maxDC = 0
tc = 0
st = Timer
For i = 1 To n
    QueryPerformanceCounter sc
    x = Int((hi - lo + 1) * Rnd) + lo
    QueryPerformanceCounter ec
    dc = ec - sc
    dt(i) = convertmytimer(dc)
    If dc > maxDC Then maxDC = dc
    If dc < minDC Then minDC = dc
    tc = tc + dc
Next
et = Timer
s = "VBA Rnd" & _
    vbNewLine & "loop: " & Format(CDbl(et - st), "0.000000") & _
    vbNewLine & "min: " & Format(convertmytimer(minDC), "0.000000\,000") & _
    vbNewLine & "avg: " & Format(convertmytimer(tc / n), "0.000000\,000") & _
    vbNewLine & "max: " & Format(convertmytimer(maxDC), "0.000000\,000") & _
    vbNewLine & "min perf counter: " & Format(convertmytimer(0.0001@), "0.000000\,000000") & _
    vbNewLine & "20%ile: " & Format(wf.Percentile(dt, 0.2), "0.000000\,000") & _
    vbNewLine & "50%ile: " & Format(wf.Percentile(dt, 0.5), "0.000000\,000") & _
    vbNewLine & "80%ile: " & Format(wf.Percentile(dt, 0.8), "0.000000\,000")
Debug.Print Now, s
MsgBox s

minDC = 922337203685477.5807@
maxDC = 0
tc = 0
st = Timer
For i = 1 To n
    QueryPerformanceCounter sc
    x = wf.RandBetween(lo, hi)
    QueryPerformanceCounter ec
    dc = ec - sc
    dt(i) = convertmytimer(dc)
    If dc > maxDC Then maxDC = dc
    If dc < minDC Then minDC = dc
    tc = tc + dc
Next
et = Timer
s = "WF.RandBetween" & _
    vbNewLine & "loop: " & Format(CDbl(et - st), "0.000000") & _
    vbNewLine & "min: " & Format(convertmytimer(minDC), "0.000000\,000") & _
    vbNewLine & "avg: " & Format(convertmytimer(tc / n), "0.000000\,000") & _
    vbNewLine & "max: " & Format(convertmytimer(maxDC), "0.000000\,000") & _
    vbNewLine & "min perf counter: " & Format(convertmytimer(0.0001@), "0.000000\,000000") & _
    vbNewLine & "20%ile: " & Format(wf.Percentile(dt, 0.2), "0.000000\,000") & _
    vbNewLine & "50%ile: " & Format(wf.Percentile(dt, 0.5), "0.000000\,000") & _
    vbNewLine & "80%ile: " & Format(wf.Percentile(dt, 0.8), "0.000000\,000")
Debug.Print Now, s
MsgBox s

minDC = 922337203685477.5807@
maxDC = 0
tc = 0
fml = "RANDBETWEEN(" & lo & "," & hi & ")"
st = Timer
For i = 1 To n
    QueryPerformanceCounter sc
    x = Evaluate(fml)
    QueryPerformanceCounter ec
    dc = ec - sc
    dt(i) = convertmytimer(dc)
    If dc > maxDC Then maxDC = dc
    If dc < minDC Then minDC = dc
    tc = tc + dc
Next
et = Timer
s = "Excel RANDBETWEEN" & _
    vbNewLine & "loop: " & Format(CDbl(et - st), "0.000000") & _
    vbNewLine & "min: " & Format(convertmytimer(minDC), "0.000000\,000") & _
    vbNewLine & "avg: " & Format(convertmytimer(tc / n), "0.000000\,000") & _
    vbNewLine & "max: " & Format(convertmytimer(maxDC), "0.000000\,000") & _
    vbNewLine & "min perf counter: " & Format(convertmytimer(0.0001@), "0.000000\,000000") & _
    vbNewLine & "20%ile: " & Format(wf.Percentile(dt, 0.2), "0.000000\,000") & _
    vbNewLine & "50%ile: " & Format(wf.Percentile(dt, 0.5), "0.000000\,000") & _
    vbNewLine & "80%ile: " & Format(wf.Percentile(dt, 0.8), "0.000000\,000")
Debug.Print Now, s
MsgBox s

minDC = 922337203685477.5807@
maxDC = 0
tc = 0
st = Timer
For i = 1 To n
    QueryPerformanceCounter sc
    x = Int((hi - lo + 1) * [RAND()]) + lo
    QueryPerformanceCounter ec
    dc = ec - sc
    dt(i) = convertmytimer(dc)
    If dc > maxDC Then maxDC = dc
    If dc < minDC Then minDC = dc
    tc = tc + dc
Next
et = Timer
s = "EXCEL RAND" & _
    vbNewLine & "loop: " & Format(CDbl(et - st), "0.000000") & _
    vbNewLine & "min: " & Format(convertmytimer(minDC), "0.000000\,000") & _
    vbNewLine & "avg: " & Format(convertmytimer(tc / n), "0.000000\,000") & _
    vbNewLine & "max: " & Format(convertmytimer(maxDC), "0.000000\,000") & _
    vbNewLine & "min perf counter: " & Format(convertmytimer(0.0001@), "0.000000\,000000") & _
    vbNewLine & "20%ile: " & Format(wf.Percentile(dt, 0.2), "0.000000\,000") & _
    vbNewLine & "50%ile: " & Format(wf.Percentile(dt, 0.5), "0.000000\,000") & _
    vbNewLine & "80%ile: " & Format(wf.Percentile(dt, 0.8), "0.000000\,000")
Debug.Print Now, s
MsgBox s

End Sub


Function convertmytimer(ByVal dt As Currency) As Double
If freq = 0 Then QueryPerformanceFrequency freq: df = freq
convertmytimer = dt / df
End Function
 
Upvote 0

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
So, with my code

Code: 
Function RandNum()
    Randomize
    RandNum = Int((Range("I11") - Range("I9") + 1) * Rnd + Range("I9"))
End Function

where the OP was happy with, why say about your code

Code: 
ActiveCell = WorksheetFunction.RandBetween(Range("I9"), Range("I11"))

It is better to use RandBetween than VBA Rnd.
Click to expand...

when it isn't? I'm a bit confused!
 
Last edited:
Upvote 0
Paul Ked said:
So, with my code

RandNum = Int((Range("I11") - Range("I9") + 1) * Rnd + Range("I9"))

[....] why say about your code

ActiveCell = WorksheetFunction.RandBetween(Range("I9"), Range("I11"))

when it isn't? I'm a bit confused!
Click to expand...

shg explained: starting in Excel 2010, Excel and WorksheetFunction.RandBetween use a better pseudorandom generator number algorithm. Here, "better" means: longer cycle and better distribution. IMHO, the distribution is the major benefit.

But make no mistake: I frequently use Rnd for quick-and-dirty random number generation.

The real point of my original comment was: Brian wanted to use RandBetween in VBA. I showed him how. All of my other comments were "gravy".

PS.... If you are generating so many random numbers that "longer cycle" is important, that outweighs any performance benefit of Rnd. Correctness first; speed second. Put another way: what good is it to get the wrong results faster? :eek:
 
Upvote 0
[I wrote: ``there is no WorksheetFunction.Rand``]

shg said:
[rand()]
Click to expand...

From the VBA help page: ``Using square brackets (for example, "[A1:C5]") is identical to calling the Evaluate method with a string argument``.

As I noted, [RAND()] and Evaluate are calculated in the Excel thread, not the VBA thread. In contrast, WorksheetFunction methods are calculated in the VBA thread.

-----

With respect to my performance tests.... On second thought, they are not dispositive. The reason is complicated; and my alternative tests are inconclusive. I could go into detail, but I should be out on my 24-mile bike ride -- long ago, sigh. (The sun sets too early now.)

I can confirm 6.041 usec (on my computer; YMMV) for the WorksheetFunction.RandBetween method. "1000 times faster" would suggest that the Rnd method should take 0.006 usec (6 nsec). I think that is unlikely, given that __sometimes__ I do see an average of 0.378 usec, my minimum "performance counter" resolution. (I'm being careful to avoid system clock interrupts. But I should disconnect from the internet to eliminate those interrupts. No way to know which CPU they are processed on. Okay, TMI.)

The point is: although I believe "1000 times faster" is an exaggeration, I cannot disprove it now. So I withdraw the argument.
 
Upvote 0
although I believe "1000 times faster" is an exaggeration ...
Click to expand...

Excel 2101 W7:

Code: 
Option Explicit

Sub RandVsRnd()
  Const n           As Long = 10000000
  Dim i             As Long
  Dim fT            As Single

  Dim f             As Single
  Dim fTot          As Single

  Dim d             As Double
  Dim dTot          As Double

  fT = Timer
  For i = 1 To n
    f = Rnd
  Next i
  Debug.Print (Timer - fT) * 256
  fTot = (Timer - fT) / (i - 1)

  fT = Timer
  For i = 1 To n / 200
    d = [rand()]
  Next i
  dTot = (Timer - fT) / (i - 1)

  Debug.Print "Rnd():  " & Format(fTot, "0.000 000 000") & vbLf & _
              "Rand(): " & Format(dTot, "0.000 000 000") & vbLf & _
              "[rand)] / Rnd(): " & Format(dTot / fTot, "0:1")
End Sub

I see results like ...

Rnd(): 0.000 000 041
Rand(): 0.000 064 922
[rand)] / Rnd(): 1598:1

Change [rand()] to [=rand()] and it's about 330:1
 
Upvote 0
Excel 2010 ..., and the first Debug.Print was entered after testing.
 
Last edited:
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
Need help with random number generator
Replies
3
Views
362
Fluff
Fluff
N
  • Solved
Can I generate a random order but with two inputted numbers?
Replies
5
Views
366
Peter_SSs
Peter_SSs
S
  • Question
Vba - generate random alphanumeric strings based on user input
Replies
1
Views
162
DanteAmor
DanteAmor
R
  • Question
Need help with random number generating
Replies
2
Views
396
royland27
R
G
  • Question
Random Number Generator Excluding Numbers Already in Cells
Replies
2
Views
1K
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,214,649
Messages
6,120,733
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top