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

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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? :eeek:
 
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

O
  • Solved
Random Numbers Divisible By Random Numbers
Replies
9
Views
114
Peter_SSs
Peter_SSs
A
  • Question Question
Random Number Generator That Can Exclude Numbers
Replies
2
Views
498
FormulasOnly
F
O
  • Solved
Managing Random Numbers
Replies
6
Views
401
smozgur
smozgur
M
  • Solved
seed random number in VBA
Replies
4
Views
444
MetLife
M
J
  • Question Question
Random String generator throwing out duplicates issue
2
Replies
12
Views
433
6StringJazzer
6StringJazzer

Forum statistics

Threads
1,224,743
Messages
6,180,687
Members
452,994
Latest member
Janick

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