Running a macro every half hour

S

Scott

If I want to run a macro every half hour, do I use the
OnTime method? This is what I have tried:

Dim KeepingTime As Boolean
Dim Times As Long

Times = 10

KeepingTime = True

Do While KeepingTime = True

Application.OnTime Now + TimeValue
("00:30:0"), "GetAll"

Times = Times - 1

If Times < 1 Then
KeepingTime = False
End If
Loop

Scott
 
J

JE McGimpsey

Change

Dim Times As Long

to

Static Times As Long

otherwise a new local variable will be created each time the Sub is run.

One alternative:

Public Sub GetAll()
Static Times As Long
Static bAlreadyRun As Boolean
If Not bAlreadyRun Then
Times = 10
bAlreadyRun = True
End If

'do stuff here

Times = Times - 1
If Times >= 1 Then _
Application.OnTime Now + TimeSerial(0, 30, 0), "GetAll"
End Sub
 
Top