Currently our code uses 1 workbook per timer, which is crazy slow. This is currently declared as "not really an issue" because requiring more than 1 timer is likely rare(?).
However if a work around was desired we could instead:
Cache the workbook
Use the AddTimer() method of below:
Private Type Timer
current as long
frequency as long
id as string
End Type
Private Timers() as Timer
Private Const MAX_TIME as Long = 1000000
Private Const MIN_TIME as Long = 10
Sub AddTimer(ByVal iMilliseconds as long, ByVal id as string)
if iMilliseconds > MAX_TIME then iMilliseconds = MAX_TIME
if iMilliseconds < MIN_TIME then iMilliseconds = MIN_TIME
On Error Resume Next: iNext = ubound(Timers)+1: On Error GoTo 0
Redim Timers(0 to iNext)
With Timers(iNext)
Timers.current = 0
Timers.frequency = iMilliseconds
Timers.id= id
End With
End Sub
Sub MainLoop()
set r = Sheet1.Cells(1,1)
While True
For each timer in Timers
timer.current = timer.current + 1
if timer.current mod timer.frequency = 0 then
r.Value = id
timer.current = 0 'saves having to deal with overflows
end if
next
Doevents
Sleep 1
Wend
End Sub
OnChange use the id = Target.Value and lookup id in a dictionary to find callback. Also pass id to Tick event.
This can handle multiple timers each added at different times. Unfortunately as new timers are added the whole class slows down, but perhaps this is ok.
Currently our code uses 1 workbook per timer, which is crazy slow. This is currently declared as "not really an issue" because requiring more than 1 timer is likely rare(?).
However if a work around was desired we could instead:
AddTimer()
method of below:OnChange
use theid = Target.Value
and lookupid
in a dictionary to find callback. Also pass id to Tick event.This can handle multiple timers each added at different times. Unfortunately as new timers are added the whole class slows down, but perhaps this is ok.