generate a random number to enter in a cell

[brianfosterblack]

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

 

[joeu2004]

The VBA Rnd() function is about 1000 times faster in VBA than WorksheetFunction.Rand

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!

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

 

[Paul Ked]

So, with my 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

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

It is better to use RandBetween than VBA Rnd.

when it isn't? I'm a bit confused!

 

[shg]

there is no WorksheetFunction.Rand

[rand()]

 

[joeu2004]

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!

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?

 

[joeu2004]

[I wrote: ``there is no WorksheetFunction.Rand``]

[rand()]

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.

 

[shg]

although I believe "1000 times faster" is an exaggeration ...

Excel 2101 W7:

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

 

[shg]

Excel 2010 ..., and the first Debug.Print was entered after testing.