Thanks to the earlier post on this page, I was able to create a sort of easy to use module.
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' POST UDF UPDATING MODULE v.9.2020 cdf
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Since Excel won't allow UDFs to update any other cells,
' an API timer is used to trigger a post UDF subroutine.
'
' This acts like a recalculate. Your code should recalculate the entire
' sheet. Tried to get it to work with a specific range, but, with all
' the different update variations ({Enter}, {Tab}, {Backspace}, {Delete},
' mouse click in the middle of an update, etc.), no luck. Any ideas?
'
' Originally, before the slight tweak, the code was found here:
' https://stackoverflow.com/questions/8520732/
'
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
' HOW TO USE
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'
' Create a module (in the Modules folder) and copy this entire document
' into it.
'
'
' Code this code into your UDF's subroutine in your module (in Modules
' folder) (should be close to the end of the code):
'
' ' Use Post UDF Timer to update other cells.
' Call TimerModulesName.SetAPITimer("UDFModule.PostUDFRoutine")
'
'
' Edit the code names:
'
' Change TimerModulesName to the module you copied this document into.
' UDFModule to the module your post UDF subroutine is in.
' PostUDFRoutine to your post UDF subroutine.
'
' Example: Module4.SetAPITimer("Module1.UpdateMyCells")
'
'
#If VBA7 Then
Private Declare PtrSafe Function SetTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As LongLong _
) As Long
Private Declare PtrSafe Function KillTimer Lib "user32" ( _
ByVal hwnd As Long, _
ByVal nIDEvent As Long _
) As Long
#Else
Private Declare Function SetTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long, _
ByVal uElapse As Long, _
ByVal lpTimerFunc As Long _
) As Long
Private Declare Function KillTimer Lib "user32" ( _
ByVal HWnd As Long, _
ByVal nIDEvent As Long _
) As Long
#End If
Private mCalculatedCells As Collection
Private mWindowsTimerID As Long
Private mApplicationTimerTime As Date
Private mRoutine As String
Public Sub SetAPITimer(sRoutine As String)
' Starts a windows timer that starts a second Appliction.OnTime
' timer that performs activities not allowed in a UDF. Do
' not make this UDF volatile, pass any volatile functions
' to it, or pass any cells containing volatile
' formulas/functions or uncontrolled looping will start.
' Cache the caller's reference so it can be dealt with in a non-UDF routine
If mCalculatedCells Is Nothing Then Set mCalculatedCells = New Collection
On Error Resume Next
mCalculatedCells.Add Application.Caller, Application.Caller.Address
On Error GoTo 0
' Setting/resetting the timer should be the last action taken in the UDF
If mWindowsTimerID 0 Then KillTimer 0&, mWindowsTimerID
mWindowsTimerID = SetTimer(0&, 0&, 1, AddressOf AfterUDFRoutine1)
' Set Post UDF module and routine
mRoutine = sRoutine
End Sub
Private Sub AfterUDFRoutine1()
' This is the first of two timer routines. This one is called by the Windows
' timer. Since a Windows timer cannot run code if a cell is being edited or a
' dialog is open this routine schedules a second safe timer using
' Application.OnTime which is ignored in a UDF.
' Stop the Windows timer
On Error Resume Next
KillTimer 0&, mWindowsTimerID
On Error GoTo 0
mWindowsTimerID = 0
' Cancel any previous OnTime timers
If mApplicationTimerTime 0 Then
On Error Resume Next
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2", , False
On Error GoTo 0
End If
' Schedule timer
mApplicationTimerTime = Now
Application.OnTime mApplicationTimerTime, "AfterUDFRoutine2"
End Sub
Private Sub AfterUDFRoutine2()
' This is the second of two timer routines. Because this timer routine is
' triggered by Application.OnTime it is safe, i.e., Excel will not allow the
' timer to fire unless the environment is safe (no open model dialogs or cell
' being edited).
' Do tasks not allowed in a UDF... (post UDF code)
Application.Run mRoutine
End Sub
