Excel macro run from Interop (C#) application in Excel returns before it completes it's execution

1
votes

My macro sub ('first-sub') ends by calling another sub ('second-sub') from Application.OnTime, with a wait time of 5 seconds. My C# application however gets back the control after the 'first-sub' execution is done. It doesn't wait for the 'second-sub' to be completed. Is there a way, my C# application can wait till both the macros complete their run?

C#:

xlApp.Run("first-sub");

Macros:

Sub first-sub 'Some code lines' Application.OnTime(Now + "00:00:05"), "second-sub" End Sub

Sub second-sub 'Some code lines' End Sub

I tried using Thread.Sleep, it didn't work. Also, I tried creating a separate macro that runs calls first-sub and waits for 5 seconds. That didn't work either.

I am working with an add-in (bloomberg add-in) that calculates the result only after the 'first-sub' has ended. This add-in doesn't calculate results in the middle of the sub. So, once the control from the sub comes out, with-in about 5 seconds my results are populated. Then, I need 'second-sub' to execute. This is the reason, I have to use Application.OnTime and not Application.Wait.

So, the following codes didn't work

xlApp.Run("first-sub");            
xlApp.OnTime((DateTime.Now + TimeSpan.FromSeconds(5)).ToString(), "second-sub");

It did run the second-sub, but bloomberg results were not populated.

1
c#exceladd-inbloombergvba
What's the purpose of the 5-second VBA-wait? There's no beautiful way to wait for the second method, since it is out of your control. Several work-arounds could do the job, but it seems reasonable to question your design. - DanL
I understand the possible design questions. Please go through my edit. - Sandy
why not just call the second sub from C#? - sous2817
Tried that, it doesn't work. The bloomberg add-in stops populating when the control gets back to C# from excel. - Sandy
Any reason you have a "second-sub" method? Why not just drop the code in this method at the end of "first-sub"? - Jason Faulkner

1 Answers

0
votes

This is one of many possible solutions:

Sub first-sub
    'Some code lines'
    Application.Wait "00:00:05"
    second-sub
End Sub

This way, you wait 5 seconds, then execute second-sub synchronously, and first-sub ends AFTER the execution of second-sub.

Another solution is to execute the two subs SEPARATELY:

xlApp.Run("first-sub");
Application.Wait "00:00:05"
xlApp.Run("second-sub");