Using Excel-VBA async functions from VBA

[Peter Drubetskoy]

I have a few asynchronous functions written using Excel-DNA and I am trying to figure out if there is a way to call them from VBA. I tried this:

Sub test_async()

    Dim timeout As Double, maxWait As Double

    timeout = 0.1
    maxWait = 10
    StartTime = Timer

    Range("b5").Formula = "=M_XXXX_ASYNC(" + Chr(34) + "some_argument_here" + Chr(34) + ")"

KeepWaiting:
    StartPause = Timer
    Do Until Timer >= StartPause + timeout
        DoEvents
    Loop
    If Left(Range("b5").Value, 7) = "Loading" And Timer < StartTime + maxWait Then
        GoTo KeepWaiting
    End If
    MsgBox "Loaded: " + CStr(result)



End Sub

(The M_XXXX_ASYNC function called should return "Loading.." until its done) This does not work. VBA does pass the control back to Excel (in DoEvents), but the cell's result is not updated until the max timeout of 10 seconds expires. The async function should have finished way before 10 secs.

Is there a way to achieve what I want?

[Govert van Drimmelen]

Excel is a bit funny in how RTD functions (which is how the Excel-DNA is implemented) are evaluated.

Can't you rather call directly into the .NET library from your VBA?

There's a great write-up by Mike Katajamäki here: http://mikejuniperhill.blogspot.com/2014/03/interfacing-c-and-vba-with-exceldna-no.html

Otherwise you might use Application.OnTime to call back into your VBA - it seems like DoEvents is not enough for Excel to transition to a state where it re-evaluates the RTD functions.

-Govert

[Peter Drubetskoy]

Thanks! I am not sure what you mean by "call directly into the .NET library from your VBA" and I would like to understand it - maybe indeed I am using a stapler to hammer nails? 

Maybe if I add some background to my issue it would be clearer:

Basically, I have an function that takes a long time to execute - it calls a 3rd party service and waits for it - sometimes as long as 15 mins. I need to run this function with many (say, 200) different inputs.  These inputs are going to be populated in cells in Excel. Most of the time I would like to press a button and let the process run overnight. 

Unfortunately,  the function cannot run on two different inputs using two different threads - this is a limitation of the 3rd party service.

However, I still don't want Excel to lock waiting for each function call - mostly because I would like to see updates, stop the whole process if needed without killing Excel etc. So, I thought I'll create an async version of this function - not because I want to run other things but only to avoid Excel locking while making all these long-long function calls.   Which is why I need VBA to be able to know when each call finishes - so that it can initiate a new call.

Hope that clarifies the issue.

Let me look into  Application.OnTime meanwhile.

Thanks a lot!