|
Timed loop
How can I run a do loop that runs the code every 5 seconds? I've tied couple things I thought might work, but I'm not really getting it. I' on excel2003 -- Ramthebuff ----------------------------------------------------------------------- Ramthebuffs's Profile: http://www.excelforum.com/member.php...fo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=38074 |
Timed loop
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" wrote in message ... 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 |
Timed loop
I ended up using Application.Wait Now + (TimeValue("00:00:01")) along with a for next loop. Its not really pretty but gets the jo done for now -- Ramthebuff ----------------------------------------------------------------------- Ramthebuffs's Profile: http://www.excelforum.com/member.php...fo&userid=1642 View this thread: http://www.excelforum.com/showthread.php?threadid=38074 |
Timed loop
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" wrote in message ... 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" wrote in message ... 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 |
Timed loop
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Timed loop
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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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" wrote in message ... 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 |
Timed loop
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. |
Timed loop
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" wrote in message ... 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 |
Timed loop
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" wrote in message ... 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 |
| All times are GMT +1. The time now is 10:22 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com