Excel Timer

As has often been worried over on these forums, Excel does not allow you access to the Timer object that we are used to in the normal VB IDE, which can be irksome.
To rectify that, here is some sample code for your Excel-based apps which will run a section of code at an interval of your choosing.

Application.OnTime has been mentioned before several times in individual threads; here I have packaged it up into more of an event-like set of subs for ease of use.

You will need to put the following into a module:

Code:

---

Public counter As Integer      'My counter for the example

Public StopTimer As Boolean    'The flag to stop the timer
Public Interval As String      'The interval

Public Sub Timer_Start()

'----------------
'Starts the timer
'----------------

Timer_Tick

End Sub


Public Sub Timer_Tick()

'-----------------------------
'The ticker code for the timer
'-----------------------------


If StopTimer = True Then            'Exit if the timer is stopped
    StopTimer = False
    Exit Sub
End If

Call Timer_Code                    'Run the timer code


Application.OnTime Now() + CDate(Format(Interval, "hh:mm:ss")), ActiveWorkbook.Name & "!Timer_Tick" 'Re-run the ticker after the interval

'------------------------------------------------------------------------------------------------
'IMPORTANT: In the above line, change the ActiveWorkbook.Name to whatever your workbook name is.
'          If you don't, the OnTime event will not fire the ticker if you switch workbooks.
'------------------------------------------------------------------------------------------------

End Sub

Public Sub Timer_Code()

'----------------------
'Put timer code in here
'----------------------


'My example, fill the next cell with the current time
ActiveWorkbook.Worksheets(1).Cells(counter, 1) = Now()
counter = counter + 1


DoEvents    'Put a DoEvents at the bottom to ensure that other code keeps running


'------------------------------------------------------------------------------------------------
'IMPORTANT: In the above code, you should fully specify the workbook and worksheets always.
'          If you don't, the code will run on a different book/sheet if you switch.
'------------------------------------------------------------------------------------------------


End Sub

Public Sub Timer_Stop()

'---------------
'Stops the timer
'---------------

StopTimer = True

End Sub

---

and for the example, put two buttons on a new sheet and paste the following in:

Code:

---

Private Sub CommandButton1_Click()

'Just formatting for this example
Range("A:A").Value = ""
Range("A:A").NumberFormat = "hh:mm:ss"


'Setting the timer parameters
StopTimer = False
counter = 1
Interval = "00:00:01"

'Start the timer
Timer_Start

End Sub


Private Sub CommandButton2_Click()

'Stop the timer
Timer_Stop

End Sub

---

This just prints the current time to column A every second.


Points to note:

You don't have to use Timer_Code for the code. You can specify any macro name in the Timer_Tick sub.

You should set the interval in the string format "hh:mm:ss" as I have done.

Pay attention to the comments and fully specify your workbooks and worksheets, or you'll find your macros going wrong if you have multiple books / sheets open within a single Excel session. This is because Application.OnTime is run at the Application level, so applies to all open workbooks in the session, meaning that if you do not specify or use ActiveWorkbook... (etc) there will be errors. I've used them here to ensure that it works for the example.


zaza