Excel, VBA Timer

Excel – VBA Timer Example v1.00

http://sebthom.de/28-excel-vba-timer/

' *********************************************************************
' Excel VBA Timer Example v1.00
' Copyright ©2002 by Sebastian Thomschke, All Rights Reserved.
' http://www.sebthom.de
' *********************************************************************
' You are free to use this code within your own applications, but you
' are expressly forbidden from selling or otherwise distributing this
' source code without prior written consent.
' *********************************************************************
Option Explicit

Dim timer_enabled As Boolean
Dim timer_interval As Double


' *********************************************************************
' Makros for the command buttons
' *********************************************************************
Sub cmd_TimerOn()
Dim interval As Double

' get the interval value fomr cell D8
interval = CDbl(Range("D8").Value)

' start the timer with the specified interval
Call timer_Start(interval)
End Sub

Sub cmd_TimerOff()
' stop the timer
Call timer_Stop
End Sub


' *********************************************************************
' your code goes into this Makro
' *********************************************************************
Sub Timer()
' output the current time to cell D13
Range("D13").Value = CStr(Time)
Dim i As Integer

For i = 14 To 20
Range("D" & i).Value = Round(Rnd * 100, 0)
Next i



End Sub


' *********************************************************************
' internal timer methods
' *********************************************************************
Sub timer_OnTimer()
Call Timer
If timer_enabled Then Call timer_Start
End Sub

Sub timer_Start(Optional ByVal interval As Double)
If interval > 0 Then timer_interval = interval
timer_enabled = True
If timer_interval > 0 Then Application.OnTime (Now + timer_interval), "Timer_OnTimer"
End Sub

Sub timer_Stop()
timer_enabled = False
End Sub