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

I have an example on my website


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Scott" wrote in message
...
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

Thanks for the help!!!


-----Original Message-----
I have an example on my website


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Scott" wrote in message
...
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


.

Thanks for the help!!!

-----Original Message-----
I have an example on my website


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Scott" wrote in message
...
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







In article ,
"Scott" wrote:

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