How to do asynchronous function call in vba

Question

I have created custom object in Python using pywin32. while calling that object takes some times. Following is my try at making that asynchronous with respect to application stat so that while macro is running application doesn't freez.

Sub demo()
    demofunction
End Sub

Function demofunction()
    Dim demoobj As Object
    Do While demoobj Is Nothing
        DoEvents
        Set demoobj = CreateObject("Python.DemoObj")
    Loop
    Debug.Print demoobj.Hello
End Function

Obviously the code is not working in terms of asynchronous run. Is this even possible any how?

Above code however working fine while freezing the application for a while.

CreateObject is a blocking call. The loop doesn't actually do anything. — Comintern
– Comintern, Commented Feb 4, 2017 at 5:36
Thanks for understanding it correctly that what I wanted. Thats why I put Obviously. I My code is just for explanation. — Rahul
– Rahul, Commented Feb 4, 2017 at 6:11
VBA doesn't do asynchronous or multithreaded. If that's a requirement, you need a more recent/powerful language. Consider VB.NET or C#.... or hack up an async solution with VBScript+VBA. Did you research anything on the subject? — Mathieu Guindon
– Mathieu Guindon, Commented Feb 4, 2017 at 7:16

S Meaden · Accepted Answer · 2017-02-04 08:02:37Z

1

Vba is single threaded but you can play some tricks to make it appear multi-threaded, after all rising star web server Node.js serves off of one thread but gives impression of multi-threaded.

1) So, you could put some Python component behind a web server and then use MSXML2.XMLHTTP60 asynchronously. Private WithEvents oXHR As MSXML2.XMLHTTP60 comes in handy here. (Note you need to declare this as a member of a class to use WithEvents)

2) You could shell out to a process (which gives you another thread) and call the Python component that way, then you use some Windows API calls to periodically check if the process has completed.

edited Feb 4, 2017 at 8:02

answered Feb 4, 2017 at 7:53

S Meaden

8,3203 gold badges39 silver badges74 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Rahul Over a year ago

Thanks. That's exactly what I have done. See my other question : stackoverflow.com/questions/41935082/…

Rahul Over a year ago

Method 2 was first choice but in my environment it still lags.

S Meaden Over a year ago

Goodness, is the Django code posted all that is required to get a web service up?

Rahul Over a year ago

That is what you want to change after you have created django project using django-admin startproject mysite from command line. see: docs.djangoproject.com/en/1.10/intro/tutorial01

Collectives™ on Stack Overflow

How to do asynchronous function call in vba

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related