Running a macro every half hour

  • Thread starter Thread starter Scott
  • Start date Start date
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
 
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top