UDF and OnTime macro

[Anthony47]

Hello everyone,
I tryed to schedule a macro from within a Udf using the OnTime method:
Application.OnTime Now + TimeValue("00:00:05"), "Macro1"

The instruction is executed normally, as well as the remaining instructions of the Udf, but Macro1 does not start.
Is this one of the forbidden actions for an Udf, or am I missing something?

Thank you.

 

[Legacy 98055]

Yes. A restriction. A workaround that can be used successfully, if done with care, and under limited circumstances, is to use a timer.

Place these functions in A1, A2, and A3.
=OnTime( B1 = 1,2,"Condition1")
=OnTime( B1 = 2,2,"Condition2")
=OnTime( B1 = 3,2,"Condition3")

Paste this code into a standard module...

Option Explicit
 
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
 
Private TimerID As Long
Private EventID As Long
Private pTarget As Range
Private pRunWhen As Long
Private pRunProcedure As String
Private Caller As Range
 
Function OnTime(Condition As Boolean, RunWhen As Long, RunProcedure As String) As Boolean
 
    If Condition Then
        Set Caller = Application.Caller
        pRunWhen = RunWhen
        pRunProcedure = RunProcedure
        EventID = 1
        TimerID = SetTimer(0, EventID, 10, AddressOf FunctionAction)
    End If
 
    OnTime = Condition
End Function
 
Private Sub FunctionAction(ByVal hwnd As Long, ByVal uint1 As Long, ByVal nEventId As Long, ByVal dwParam As Long)
 
    On Error Resume Next
 
    Call KillTimer(0, EventID)
    Call KillTimer(0, TimerID)
 
    Application.OnTime Now + TimeSerial(0, 0, pRunWhen), pRunProcedure
 
End Sub
 
Sub Condition1()
    Caller.Font.Bold = True
    MsgBox "B1 = 1"
End Sub
 
Sub Condition2()
    Caller.Font.Bold = True
    MsgBox "B1 = 2"
End Sub
 
Sub Condition3()
    Caller.Font.Bold = True
    MsgBox "B1 = 3"
End Sub

Now type 1, 2, or 3 into Cell B1...

Will run a certain macro in 2 seconds based upon the arguments passed to your function. Displays a msgox and changes the font of the caller to bold...

RunProcedureFromCellFormula.zip

 

[Anthony47]

Thank you for this smart workaround!

Bye.