VBA create random numbers, BUT...

[Akashwani]

Good day,

I need a VBA code that produces a random number from 11-99 every 0.5 seconds. The number cannot be repeated and every number between 11-99 must be used. I also need to be able to pause/stop/restart the code as and when required.

Any ideas on how this can be achieved?
I do not write or really understand VBA so I am really in the dark with this.

Thanks

Ak

 

[shg]

Maybe like this:

Option Explicit
 
Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 
Sub GetRandom()
    Dim aiRnd()     As Long
    Dim iRnd As Long
 
    aiRnd = aiRandLong(11, 99)

    For iRnd = LBound(aiRnd) To UBound(aiRnd)
        Cells(Rows.Count, "A").End(xlUp).Offset(1).Value = aiRnd(iRnd)
'        Application.Wait Now() + 0.5 / 86400
        Sleep 500
    Next iRnd
End Sub
 
Public Function aiRandLong(iMin As Long, _
                           iMax As Long, _
                           Optional ByVal n As Long = -1, _
                           Optional bVolatile As Boolean = False) As Long()
    ' UDF or VBA
    ' Adapted from Chip Pearson at [URL]http://www.cpearson.com/excel/RandomNumbers.aspx[/URL]
    ' Returns a 1-based array of n unique Longs between iMin and iMax inclusive

    Dim aiSrc()     As Long
    Dim aiOut()     As Long
    Dim iSrc        As Long
    Dim iOut        As Long
    Dim iTop        As Long
 
    If bVolatile Then Application.Volatile
 
    If n = -1 Then n = iMax - iMin + 1
    If iMin > iMax Or n > (iMax - iMin + 1) Or n < 1 Then Exit Function
 
    ReDim aiSrc(iMin To iMax)
    ReDim aiOut(1 To n)
 
    ' init iSrc
    For iSrc = iMin To iMax
        aiSrc(iSrc) = iSrc
    Next iSrc
 
    iTop = iMax

    For iOut = 1 To n
        ' pick a number between 1 and iTop, swap with iTop, decrement iTop
        iSrc = Int((iTop - iMin + 1) * Rnd) + iMin
        aiOut(iOut) = aiSrc(iSrc)
        aiSrc(iSrc) = aiSrc(iTop)
        iTop = iTop - 1
    Next iOut
 
    aiRandLong = aiOut
End Function

I don't believe you can use Application.OnTime or Application.Wait for intervals of less than 1s, so I used Sleep.

 

[Akashwani]

Hi shg,

WOW, that is ridiculous
Thank you very much indeed, that is just what was required.

Now for the second part of my question.
How would I go about stopping that code from running and then starting it again from where it stopped?

Eg, I run the code and after it has created say 10 numbers, I want to click a button to pause/stop the code, then click another button to restart the code BUT, not have any of the numbers duplicated. Does that makes sense?

Once again, thanks for the code and help provided so far.

Ak

 

[Akashwani]

Hi shg,

You can forget the second part of the question.
I have just realise that I can press esc, then Continue

Thank you very much for the code provided. I'm sure that when I pass it on, the person requiring it will be as impressed and grateful as I am.

Cheers.

Ak

 

[shg]

You're welcome, hope it works out as you need.