'Flash' random numbers

[Jock]

Is it possible to have numbers from a predetermined list, appear in a
specified cell at a specified time interval?
IE from a list in (A1:A30) containing numbers from 1-30, I would like a
random number to appear in F10 every 4 seconds.
What do you think?

 

[Gary''s Student]

Enter this VBA:

Public whn As Double
Public Const T = 4 ' four seconds
Public Const mac = "flash" ' the name of the procedure to run

Sub StartTimer()
whn = Now + TimeSerial(0, 0, T)
Application.OnTime EarliestTime:=whn, Procedure:=mac, Schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=whn, Procedure:=mac, Schedule:=False
End Sub

Sub flash()
n = Evaluate("=randbetween(1,30)")
Range("F10").Value = Cells(n, 1).Value
Call StartTimer
End Sub

Start by running StartTimer.

 

[Jock]

Nearly there!
I have placed the subs in sheet1 and public constants in module1.
When I run Start timer' a number appears in F10 then, after about 4 secs, I
get the message "The macro '!flash' cannot be found"
What have I done wrong here?

 

[Jock]

OK, ignore last as I've got it working. However.......
a number doesn't appear every time, sometimes the cell is blank for a couple
of changes before a number will appear. Odd
Also, would it be possible to show data from a list rather than numbers?
Thanks very much.

 

[Jock]

Ok, engage brain before typing........
Sorted! Thanks Gary''s Student

 

[Gary''s Student]

There are two separate thing to check:

A. Make sure the stuff I posted is pasted in a standard module, not a
Private area:

From an Excel worksheet:
1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. paste the stuff in and close the VBE window


If you have more than one workbook open at the same time, Excel may have
trouble finding flash. If this is the case change:

Sub Flash()
to:
Public Sub flash()

 

[L. Howard Kittle]

Hi Jock,
Try this, in the sheets VB editor. In A1 enter =RANDBETWEEN(1,30) AND IN
F10 enter =A1.
Run the sub FourSec()

Option Explicit

Sub FourSec()
Dim Myseconds
Myseconds = 4
Call MyPause(Myseconds)
End Sub

Sub MyPause(Myseconds)
Dim MyTime
'1/86400 = 0.0000115740740740741 = 1 second (fraction of a day)
MyTime = Time
Do Until Time >= MyTime + (1 / 86400 * Myseconds)
If Time < MyTime Then Exit Do 'For after midnight
Loop
Calculate
Call FourSec
'MsgBox "We paused for 4 seconds!"

End Sub

HTH
Regards,
Howard