Skip to main content
Code Review

Return to Answer

deleted 1 character in body
Source Link
Greedo
Greedo
  • 2.6k
  • 2
  • 14
  • 35

Alternative suggestion too long for a comment. I wrote code to use WinAPI timers: Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

These can be used to call a method which may be:

  • Stopped with the stop button
  • Disabled when Excel closes
  • Even stopped with the End statement
  • Can handle Errors and launch debugger (just make sure to continue execution after)

Standard Module

Option Explicit

Sub StartContinuousRecalculating()
    Dim cellToRecalc As Range
    Set cellToRecalc = Sheet1.Range("A1")
    'spawn a new timer instance, it will call "RecalculateCellProc" supplying the Data argument as the userData param
    ' i.e. it will forward the cell reference to the proc every time it is called
    TickerAPI.StartManagedTimer New RecalculateCellProc, delaymillis:=1000, Data:=cellToRecalc
End Sub

Class RecalculateCellProc

Option Explicit

Implements Timing.ITimerProc

Private Sub ITimerProc_Exec(ByVal timerID As LongPtr, ByVal userData As Variant, ByVal tickCount As Long)
    'Doesn't matter if we raise errors here as this is a managed timer proc, error details are logged
    'Can even set breakpoints as long as we don't click `End` during a callback, that will crash Excel
    userData.Calculate 'assume it's the range we're expecting
End Sub

Obviously this isn't commentary on your approach. You just may want to do it this way since it is a bit less hacky (e.g. allows breakpoints/errors, doesn't use state machine logic that's hard to follow), and you can have finer grained control over what you call and millisecond delays.

ChristianBuseCristianBuse created another great approach to these timers using 1 workbook that spawns another delegate workbook to handle the WinAPI calls with an extra degree of safety. His approach is even more stable/harder to crash - you have to set a breakpoint in the second workbook I think. But it's a bit more overhead (which may or may not be an issue for you) and Excel specific IIUC and it sounds like you want host-agnostic code.

Alternative suggestion too long for a comment. I wrote code to use WinAPI timers: Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

These can be used to call a method which may be:

  • Stopped with the stop button
  • Disabled when Excel closes
  • Even stopped with the End statement
  • Can handle Errors and launch debugger (just make sure to continue execution after)

Standard Module

Option Explicit

Sub StartContinuousRecalculating()
    Dim cellToRecalc As Range
    Set cellToRecalc = Sheet1.Range("A1")
    'spawn a new timer instance, it will call "RecalculateCellProc" supplying the Data argument as the userData param
    ' i.e. it will forward the cell reference to the proc every time it is called
    TickerAPI.StartManagedTimer New RecalculateCellProc, delaymillis:=1000, Data:=cellToRecalc
End Sub

Class RecalculateCellProc

Option Explicit

Implements Timing.ITimerProc

Private Sub ITimerProc_Exec(ByVal timerID As LongPtr, ByVal userData As Variant, ByVal tickCount As Long)
    'Doesn't matter if we raise errors here as this is a managed timer proc, error details are logged
    'Can even set breakpoints as long as we don't click `End` during a callback, that will crash Excel
    userData.Calculate 'assume it's the range we're expecting
End Sub

Obviously this isn't commentary on your approach. You just may want to do it this way since it is a bit less hacky (e.g. allows breakpoints/errors, doesn't use state machine logic that's hard to follow), and you can have finer grained control over what you call and millisecond delays.

ChristianBuse created another great approach to these timers using 1 workbook that spawns another delegate workbook to handle the WinAPI calls with an extra degree of safety. His approach is even more stable/harder to crash - you have to set a breakpoint in the second workbook I think. But it's a bit more overhead (which may or may not be an issue for you) and Excel specific IIUC and it sounds like you want host-agnostic code.

Alternative suggestion too long for a comment. I wrote code to use WinAPI timers: Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

These can be used to call a method which may be:

  • Stopped with the stop button
  • Disabled when Excel closes
  • Even stopped with the End statement
  • Can handle Errors and launch debugger (just make sure to continue execution after)

Standard Module

Option Explicit

Sub StartContinuousRecalculating()
    Dim cellToRecalc As Range
    Set cellToRecalc = Sheet1.Range("A1")
    'spawn a new timer instance, it will call "RecalculateCellProc" supplying the Data argument as the userData param
    ' i.e. it will forward the cell reference to the proc every time it is called
    TickerAPI.StartManagedTimer New RecalculateCellProc, delaymillis:=1000, Data:=cellToRecalc
End Sub

Class RecalculateCellProc

Option Explicit

Implements Timing.ITimerProc

Private Sub ITimerProc_Exec(ByVal timerID As LongPtr, ByVal userData As Variant, ByVal tickCount As Long)
    'Doesn't matter if we raise errors here as this is a managed timer proc, error details are logged
    'Can even set breakpoints as long as we don't click `End` during a callback, that will crash Excel
    userData.Calculate 'assume it's the range we're expecting
End Sub

Obviously this isn't commentary on your approach. You just may want to do it this way since it is a bit less hacky (e.g. allows breakpoints/errors, doesn't use state machine logic that's hard to follow), and you can have finer grained control over what you call and millisecond delays.

CristianBuse created another great approach to these timers using 1 workbook that spawns another delegate workbook to handle the WinAPI calls with an extra degree of safety. His approach is even more stable/harder to crash - you have to set a breakpoint in the second workbook I think. But it's a bit more overhead (which may or may not be an issue for you) and Excel specific IIUC and it sounds like you want host-agnostic code.

Source Link
Greedo
Greedo
  • 2.6k
  • 2
  • 14
  • 35

Alternative suggestion too long for a comment. I wrote code to use WinAPI timers: Tick. Tick. *breathe* BOOM! - Setting up real, stable asynchronous callbacks with WinAPI Timers in VBA

These can be used to call a method which may be:

  • Stopped with the stop button
  • Disabled when Excel closes
  • Even stopped with the End statement
  • Can handle Errors and launch debugger (just make sure to continue execution after)

Standard Module

Option Explicit

Sub StartContinuousRecalculating()
    Dim cellToRecalc As Range
    Set cellToRecalc = Sheet1.Range("A1")
    'spawn a new timer instance, it will call "RecalculateCellProc" supplying the Data argument as the userData param
    ' i.e. it will forward the cell reference to the proc every time it is called
    TickerAPI.StartManagedTimer New RecalculateCellProc, delaymillis:=1000, Data:=cellToRecalc
End Sub

Class RecalculateCellProc

Option Explicit

Implements Timing.ITimerProc

Private Sub ITimerProc_Exec(ByVal timerID As LongPtr, ByVal userData As Variant, ByVal tickCount As Long)
    'Doesn't matter if we raise errors here as this is a managed timer proc, error details are logged
    'Can even set breakpoints as long as we don't click `End` during a callback, that will crash Excel
    userData.Calculate 'assume it's the range we're expecting
End Sub

Obviously this isn't commentary on your approach. You just may want to do it this way since it is a bit less hacky (e.g. allows breakpoints/errors, doesn't use state machine logic that's hard to follow), and you can have finer grained control over what you call and millisecond delays.

ChristianBuse created another great approach to these timers using 1 workbook that spawns another delegate workbook to handle the WinAPI calls with an extra degree of safety. His approach is even more stable/harder to crash - you have to set a breakpoint in the second workbook I think. But it's a bit more overhead (which may or may not be an issue for you) and Excel specific IIUC and it sounds like you want host-agnostic code.