Include arguments in Excel.Application.Ontime()

[Colin]

Hi everyone,

 

I'm an Excel VBA developer by trade and I've been writing C#.Net for around 6 months. I thought it was time that I give Excel DNA a go. I'm using Visual Studio 2012.

 

I've already developed a number of successful (but basic) UDFs using Excel DNA, but I am having trouble calling the Excel.Application.OnTime() method with arguments passed in.

 

Here is an example with no arguments which works absolutely fine.

 

         [dna.ExcelFunction(IsMacroType = true)]
        public static DateTime MyTest1()
        {
            //this works great
            dynamic xlApp = dna.ExcelDnaUtil.Application;
            xlApp.OnTime(DateTime.Now, "TimedProc1");
           
            return DateTime.Now;
        }

         [dna.ExcelCommand]
         public static void TimedProc1()
         {
             System.Windows.Forms.MessageBox.Show("TimedProc1");
         }

 

 

 

Now, I would really like for the method being called by the timer to have a parameter or two.

This can be achieved in VBA by wrapping the name of the procedure to be called together with the arguments in single quotes. Here's an example:

Public Sub test()

    Application.OnTime Now, "'TimedProcVBA 1, 2'"

End Sub

Public Sub TimedProcVBA(ByVal lngValue1 As Long, ByVal lngValue2 As Long)
   
    MsgBox "TimedProcVBA " & CStr(lngValue1) & " " & CStr(lngValue2)
   
End Sub

 

Unfortunately, I can't seem to do this in DNA. I've tried a few different combinations (quotes, no quotes etc), with this being the most obvious:

         [dna.ExcelFunction(IsMacroType = true)]
         public static DateTime MyTest2()
         {
             //this works great
             dynamic xlApp = dna.ExcelDnaUtil.Application;
             xlApp.OnTime(DateTime.Now, "'TimedProc2 1, 2'");

             return DateTime.Now;
         }

         [dna.ExcelCommand]
         public static void TimedProc2(object myObject1, object myObject2)
         {
             System.Windows.Forms.MessageBox.Show(string.Format("TimedProc2 {0} {1}",myObject1,myObject2));
         }

 

But I get an error message in Excel saying that it can find the macro TimedProc2 1, 2.

 

Please would someone tell me the correct syntax to do this?

 

Thanks,

Colin

 

 

 

[Govert van Drimmelen]

Hi Colin,

Welcome to Excel-DNA!

Calling macros with parameters that are registered with the C API (like Excel-DNA does) can be a bit tricky, and doesn't always work as expected.

For example, if you change your function to:

    [dna.ExcelFunction(IsMacroType = true)]

    public static DateTime MyTest2()

    {

        dynamic xlApp = dna.ExcelDnaUtil.Application;

        xlApp.OnTime(DateTime.Now, "TimedProc2(1, 2)");

        return DateTime.Now;

    }

where the macro parameters are in parentheses, you'll find the macro with parameters run as expected, but still followed by the error message!?

If you want to schedule macro functionality from within a UDF, I'd suggest you use the Excel-DNA async macro feature, discussed here: https://exceldna.codeplex.com/wikipage?title=Performing%20Asynchronous%20Work

You need two steps for this to work:

1. Add a class that implements ExcelDna.Integration.IExcelAddIn, and in the AutoOpen() method call ExcelAsyncUtil.Initialize().

2. In your function call ExcelAscynUtil.QueueAsMacro( () => TimedProc2(1, 2) ); or any delegate.

One advantage of this approach (rather than Application.OnTime) is that your code will only run when the Excel COM Automation API is available (talking to the Application object will fail if Excel is 'busy' because the user is editing a cell or pressing her mouse button).

Let me know if that works for you.

Cheers,

Govert

[Colin]

Hi Govert,

 

Thank you for the warm welcome and quick response.

 

I tried calling the proc by surrounding the arguments with brackets but I saw the same behaviour  in Excel as you did: the macro was successfully called but still the error message showed. I'd love to improve my knowledge to understand why that is.

 

I will definitely look at the Excel-DNA async feature and let you know how I get on. It will take me some time. It's really interesting that you mentioned that this feature will only execute the macro when Excel is in safe mode: that was the exact reason I was trying to use Application.OnTime()!

 

Thanks again,

Colin