Timed loop

[Ramthebuffs]

How can I run a do loop that runs the code every 5 seconds? I've tied a couple things I thought might work, but I'm not really getting it. I'm on excel2003.

[Tushar Mehta]

What have you tried and how have they not worked?

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article <Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com>,
Ramthebuffs.1qyewo_1119319751.6994@e...rum-nospam.com says...
>
> How can I run a do loop that runs the code every 5 seconds? I've tied a
> couple things I thought might work, but I'm not really getting it. I'm
> on excel2003.
>
>
> --
> Ramthebuffs
> ------------------------------------------------------------------------
> Ramthebuffs's Profile: http://www.excelforum.com/member.php...o&userid=16429
> View this thread: http://www.excelforum.com/showthread...hreadid=380747
>
>

[Robin Hammond]

Depends what you are doing but this might help:

http://www.enhanceddatasystems.com/E...ExcelTimer.htm

Your other alternatives are using the OnTime method which is clunky and
unreliable, or windows api callbacks.

Robin Hammond
www.enhanceddatasystems.com

"Ramthebuffs" <Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com>
wrote in message
news:Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com...
>
> How can I run a do loop that runs the code every 5 seconds? I've tied a
> couple things I thought might work, but I'm not really getting it. I'm
> on excel2003.
>
>
> --
> Ramthebuffs
> ------------------------------------------------------------------------
> Ramthebuffs's Profile:
> http://www.excelforum.com/member.php...o&userid=16429
> View this thread: http://www.excelforum.com/showthread...hreadid=380747
>

[Ramthebuffs]

I ended up using
Application.Wait Now + (TimeValue("00:00:01"))
along with a for next loop. Its not really pretty but gets the job done for now.

[Chip Pearson]

> windows api callbacks.

Be careful with the API callback functions. If Excel is in Edit
Mode when the callback is called, you'll crash Excel.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com




"Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in
message news:ezGKl8hdFHA.3808@TK2MSFTNGP14.phx.gbl...
> Depends what you are doing but this might help:
>
> http://www.enhanceddatasystems.com/E...ExcelTimer.htm
>
> Your other alternatives are using the OnTime method which is
> clunky and unreliable, or windows api callbacks.
>
> Robin Hammond
> www.enhanceddatasystems.com
>
> "Ramthebuffs"
> <Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com>
> wrote in message
> news:Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com...
>>
>> How can I run a do loop that runs the code every 5 seconds?
>> I've tied a
>> couple things I thought might work, but I'm not really getting
>> it. I'm
>> on excel2003.
>>
>>
>> --
>> Ramthebuffs
>> ------------------------------------------------------------------------
>> Ramthebuffs's Profile:
>> http://www.excelforum.com/member.php...o&userid=16429
>> View this thread:
>> http://www.excelforum.com/showthread...hreadid=380747
>>
>
>

[Robin Hammond]

Absolutely agree. I've tested it a fair bit, and my timer seems to avoid the
problem as far as I can see. Further, if using OnTime and you go into edit
mode, then miss the call once the next OnTime event won't be set up
correctly.

I would probably look at using a modeless hidden form to contain the
vbatimer, and let the vbatimer do the hard work for you.

Robin Hammond
www.enhanceddatasystems.com

"Chip Pearson" <chip@cpearson.com> wrote in message
news:%23xfuDBjdFHA.616@TK2MSFTNGP12.phx.gbl...
>> windows api callbacks.
>
> Be careful with the API callback functions. If Excel is in Edit Mode when
> the callback is called, you'll crash Excel.
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
>
>
> "Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in message
> news:ezGKl8hdFHA.3808@TK2MSFTNGP14.phx.gbl...
>> Depends what you are doing but this might help:
>>
>> http://www.enhanceddatasystems.com/E...ExcelTimer.htm
>>
>> Your other alternatives are using the OnTime method which is clunky and
>> unreliable, or windows api callbacks.
>>
>> Robin Hammond
>> www.enhanceddatasystems.com
>>
>> "Ramthebuffs" <Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com>
>> wrote in message
>> news:Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com...
>>>
>>> How can I run a do loop that runs the code every 5 seconds? I've tied a
>>> couple things I thought might work, but I'm not really getting it. I'm
>>> on excel2003.
>>>
>>>
>>> --
>>> Ramthebuffs
>>> ------------------------------------------------------------------------
>>> Ramthebuffs's Profile:
>>> http://www.excelforum.com/member.php...o&userid=16429
>>> View this thread:
>>> http://www.excelforum.com/showthread...hreadid=380747
>>>
>>
>>
>
>

[Chip Pearson]

If you use OnTime and you are in Edit Mode when the OnTime timer
pops, it will wait until you are out of Edit Mode and then call
the specified macro.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in
message news:ecJa6mjdFHA.3620@TK2MSFTNGP09.phx.gbl...
> Absolutely agree. I've tested it a fair bit, and my timer seems
> to avoid the problem as far as I can see. Further, if using
> OnTime and you go into edit mode, then miss the call once the
> next OnTime event won't be set up correctly.
>
> I would probably look at using a modeless hidden form to
> contain the vbatimer, and let the vbatimer do the hard work for
> you.
>
> Robin Hammond
> www.enhanceddatasystems.com
>
> "Chip Pearson" <chip@cpearson.com> wrote in message
> news:%23xfuDBjdFHA.616@TK2MSFTNGP12.phx.gbl...
>>> windows api callbacks.
>>
>> Be careful with the API callback functions. If Excel is in
>> Edit Mode when the callback is called, you'll crash Excel.
>>
>>
>> --
>> Cordially,
>> Chip Pearson
>> Microsoft MVP - Excel
>> Pearson Software Consulting, LLC
>> www.cpearson.com
>>
>>
>>
>>
>> "Robin Hammond" <rjNOrhSPAM@PLEASEnetvigator.com> wrote in
>> message news:ezGKl8hdFHA.3808@TK2MSFTNGP14.phx.gbl...
>>> Depends what you are doing but this might help:
>>>
>>> http://www.enhanceddatasystems.com/E...ExcelTimer.htm
>>>
>>> Your other alternatives are using the OnTime method which is
>>> clunky and unreliable, or windows api callbacks.
>>>
>>> Robin Hammond
>>> www.enhanceddatasystems.com
>>>
>>> "Ramthebuffs"
>>> <Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com>
>>> wrote in message
>>> news:Ramthebuffs.1qyewo_1119319751.6994@excelforum-nospam.com...
>>>>
>>>> How can I run a do loop that runs the code every 5 seconds?
>>>> I've tied a
>>>> couple things I thought might work, but I'm not really
>>>> getting it. I'm
>>>> on excel2003.
>>>>
>>>>
>>>> --
>>>> Ramthebuffs
>>>> ------------------------------------------------------------------------
>>>> Ramthebuffs's Profile:
>>>> http://www.excelforum.com/member.php...o&userid=16429
>>>> View this thread:
>>>> http://www.excelforum.com/showthread...hreadid=380747
>>>>
>>>
>>>
>>
>>
>
>

[keepITcool]

Guys...

Following seems stable enough:
(coded for vba6)

Checks to see if window enabled...
Checks to see if in edit mode..
Exits on unhandled errors

IMPORTANT:
Ensure the timer is stopped before the TimedProc is removed from memory

In example below this is done my workbook deactivate event.
that could be the close event too..


Code follows:
'>>normal module<<
Option Explicit

Private Declare Function KillTimer Lib "user32.dll" ( _
ByVal hWnd As Long, ByVal nIDEvent As Long) As Long
Private Declare Function SetTimer Lib "user32.dll" ( _
ByVal hWnd As Long, ByVal nIDEvent As Long, _
ByVal uElapse As Long, ByVal lpTimerFunc As Long) As Long
Private Declare Function IsWindowEnabled Lib "user32.dll" ( _
ByVal hWnd As Long) As Long

Const ID_CLOCK = 123

Public Sub startclock()
SetTimer Application.hWnd, ID_CLOCK, 1000, AddressOf TimedProc
TimedProc
End Sub

Public Sub stopclock()
KillTimer Application.hWnd, ID_CLOCK
End Sub

Public Sub TimedProc()
On Error GoTo errH
If IsWindowEnabled(Application.hWnd) > 0 Then
If Application.CommandBars(1).FindControl(, 723, , , 1).Enabled Then
Sheet1.Cells(1, 1) = Time
Else 'included for demo only
Debug.Print "editmode"
End If
Else 'included for demo only
Debug.Print "disabled"
End If
Exit Sub
errH:
Debug.Print Err.Number; Err.Description
End Sub


'>>Thisworkbook module<<
Option Explicit

Private Sub Workbook_activate()
If Me.ActiveSheet Is Sheet1 Then startclock
End Sub

Private Sub Workbook_Deactivate()
If Me.ActiveSheet Is Sheet1 Then stopclock
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Sh Is Sheet1 Then startclock
End Sub

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
If Sh Is Sheet1 Then stopclock
End Sub





--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Chip Pearson wrote :

> If you use OnTime and you are in Edit Mode when the OnTime timer
> pops, it will wait until you are out of Edit Mode and then call
> the specified macro.

[Henry]

Ram,

You don't need the For Next loop.
Application.Wait Now +(TimeValue("00:00:05")) will give you a five second
delay.
Application.Wait Now +(TimeValue("00:03:10")) will give you a three minutes
and ten seconds delay.
Application.Wait Now +(TimeValue("10:25:37")) will give you a ten hour,
twenty-five minutes and 37 seconds delay.
Etc., etc.
You may have to adjust the delay to take account of the length of time that
your program takes to run.

Do
'Your program here
Application.Wait Now +(TimeValue("00:00:05"))
While Now < TimeValue("17:00:00") 'Stops at 5PM

or use While 1<>0 'to run continuously

Henry


"Ramthebuffs" <Ramthebuffs.1qysqe_1119337516.856@excelforum-nospam.com>
wrote in message
news:Ramthebuffs.1qysqe_1119337516.856@excelforum-nospam.com...
>
> I ended up using
> Application.Wait Now + (TimeValue("00:00:01"))
> along with a for next loop. Its not really pretty but gets the job
> done for now.
>
>
> --
> Ramthebuffs
> ------------------------------------------------------------------------
> Ramthebuffs's Profile:
> http://www.excelforum.com/member.php...o&userid=16429
> View this thread: http://www.excelforum.com/showthread...hreadid=380747
>

[keepITcool]

exactly:
Important The Wait method suspends all Microsoft Excel activity and
may prevent you from performing other operations on your computer while
Wait is in effect. However, background processes such as printing and
recalculation continue.


thus a "timer" may be a bit more comfortable..


--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Henry wrote :

> Ram,
>
> You don't need the For Next loop.
> Application.Wait Now +(TimeValue("00:00:05")) will give you a five
> second delay.
> Application.Wait Now +(TimeValue("00:03:10")) will give you a three
> minutes and ten seconds delay.
> Application.Wait Now +(TimeValue("10:25:37")) will give you a ten
> hour, twenty-five minutes and 37 seconds delay.
> Etc., etc.
> You may have to adjust the delay to take account of the length of
> time that your program takes to run.
>
> Do
> 'Your program here
> Application.Wait Now +(TimeValue("00:00:05"))
> While Now < TimeValue("17:00:00") 'Stops at 5PM
>
> or use While 1<>0 'to run continuously
>
> Henry
>
>
> "Ramthebuffs"
> <Ramthebuffs.1qysqe_1119337516.856@excelforum-nospam.com> wrote in
> message
> news:Ramthebuffs.1qysqe_1119337516.856@excelforum-nospam.com...
> >
> > I ended up using
> > Application.Wait Now + (TimeValue("00:00:01"))
> > along with a for next loop. Its not really pretty but gets the job
> > done for now.
> >
> >
> > --
> > Ramthebuffs
> > --------------------------------------------------------------------
> > ---- Ramthebuffs's Profile:
> > http://www.excelforum.com/member.php...o&userid=16429
> > View this thread:
> > http://www.excelforum.com/showthread...hreadid=380747
> >