Calling Application.OnTime with arguments

[Rafał Gwoździński]

Hello,

I want to schedule some Excel jobs to be executed on given time. In order to do this, I use method Application.OnTime (https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime).

Here is an example F# code that runs simpleCommand function immediately.

[<ExcelCommand()>]
let simpleCommand () =
    ExcelDnaUtil.Application?Range("A1")?Value2 <- "example value"

[<ExcelFunction(IsMacroType=true)>]
let schedule () =
    ExcelDnaUtil.Application?OnTime(DateTime.Now, "simpleCommand")

The problem that I have, is that I can't pass arguments to ExcelCommand.

According to MSDN, it isn't possible to pass arguments to VBA Application.OnTime:

Procedure must take no arguments and cannot be declared in a custom class or form.

https://docs.microsoft.com/en-us/office/vba/api/excel.application.ontime#remarks

However, people do pass parameters with OnTime:

https://stackoverflow.com/questions/31439866/multiple-variable-arguments-to-application-ontime

I have created a small example in VBA according to link above and it worked:

Sub Test()

    Application.OnTime Now, "'CallMe ""test""'"

End Sub

Public Sub CallMe(str As String)

    MsgBox (str)

End Sub

I tried to use similar approach in F#:

[<ExcelCommand()>]
let simpleCommand 

  ([<ExcelArgument()>] s: string) =
    ExcelDnaUtil.Application?Range("A1")?Value2 <- s

[<ExcelFunction(IsMacroType=true)>]
let schedule () =
    ExcelDnaUtil.Application?OnTime(DateTime.Now, @"'simpleCommand ""test""'")

, but I get an excel error:

Cannot run the macro "'simpleCommand ""test""'". The macro may not be available in this workbook or all macros may be disabled.

I can even try to run previously defined macro CallMe, but it works only when I set a default parameter:

Public Sub CallMe(Optional str As String = "Default")

    MsgBox (str)

End Sub

I can then call it from F#:

[<ExcelFunction(IsMacroType=true)>]
let schedule () =
    ExcelDnaUtil.Application?OnTime(DateTime.Now, @"'CallMe ""Test""'")

However, I can't replicate this with ExcelDNA.

I've tried to create static method with default optional argument, but it doesn't work:

[<AbstractClass; Sealed>]

type Commands private () =

    [<ExcelCommand()>]

    static member CommandWithArg([<Optional; DefaultParameterValue(""); ExcelArgument()>]s: string) =

        ExcelDnaUtil.Application?Range("A1")?Value2 <- s

Do you have any idea if I can't make it  work for ExcelCommands?

Best,

Rafal

[Govert van Drimmelen]

Hi Rafal,

 

Macros with parameters are very confusing in the xll world.

I vaguely remember some things working by putting a strategic “!” in place somewhere.

 

But I would suggest you don’t use Application.OnTime, and rather set up your own timer or time delay, and then call back to Excel using ExcelAsyncUtil.QueueAsMacro. That will give you better control, and isn’t ‘global’ like Application.OnTime, where other add-ins or VBA code might interfere.

 

-Govert

--
You received this message because you are subscribed to the Google Groups "Excel-DNA" group.
To unsubscribe from this group and stop receiving emails from it, send an email to exceldna+u...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/exceldna/df90e02b-770b-4b08-a2e6-5c23c85b9bbfn%40googlegroups.com.