orchestrating asynchronicity in excel vba using promises

Orchestrating asynchronous Excel VBA operations with promises

cPromise primer from Excel Liberation

http://ramblings.mcpher.com/

Excel Liberation for details

Waiting for stuff to finish JavaScript is good at doing something else in the

meantimedoSomething ( function (result) {

report(result);

} );doSomethingElse();

function doSomething (callback) {var result = process();callback (result);

}

VBA prefers waiting for things to finish before doing the next

result = process()report (result)doSomethingElse()



Orchestration with promises JavaScript can quickly become a mess with multiple callbacks.

Many developers are now using Deferred promises for better orchestration. For example in jQuery

var promise = doSomething();doSomethingElse();promise.done ( function (result) {

report(result);}).fail ( function (error) {

report (error);});

function doSomething (callback) {var d = $.Deferred();

process ( function (result) { d.resolve(result);}, function (error) { d.reject (error);} );

return d.promise();}



Orchestration in VBA One of the most common is to use withEvents, for example you

can execute ADO asynchronously and handle connection events by declaring your connection with events

Private WithEvents prConnection As ADODB.Connection

Other functions allow a callback function name, but put restrictions on what that can be. For example the XMLHTTP object has an onReadyStateChange property, but first you have to figure out how to create a class with a default member.

pResponse.OnReadyStateChange = pAsynch

Custom classes can declare and raise custom events, but they are underused and complicated

VBA needs syntactic gymnastics to deal with callbacks and often forces the need for a network of global variables



Promises in VBA We’ve seen that VBA has various capabilities.

Could they be used to create a deferred/promise structure ?

Yes they can! Here’s a snippet that will get data from a web

site asynchronously, and populate a range with the result when its done.

Dim callbacks As New yourCallbacks loadData("http://www.mcpher.com", Range("rest!a1"))

.done(callbacks, "populate")

.fail callbacks, "show“

doSomethingElseInTheMeantime()



Difference between cPromise and cDeferredA new cDeferred is created by some task that will complete later. Each

cDeferred has a single cPromise

3 simple rules. Deferred is used by the function doing the work Promise is used by the function receiving the results. Function doing the work returns the promise method of the deferred

Function doing the workreturn deferred.promise()

And laterdeferred.reject( array(.. Some arguments..))deferred.resolve( array(..some arguments..))

Function receiving the resultspromise.fail (callbacks, “handleit”)promise.done(callbacks,”processit”)



Error handlingAn issue with asynch and event processing in VBA is how to

communicate errors to the caller. Using promises its easy. A promise either fails or succeeds. On completion, if it fails the .fail() method is executed, otherwise the .done() method is executed.

And it used like this, where you’ve written a handler in your callbacks class

promise.fail (callbacks, “handleit”)

The arguments you registered at the time of rejection will be passed to your handler

deferred.reject (Array(“it failed”, statusCode, someOtherInfo))



Success handlingSuccess handling and error handling techniques

turn out to be the same when using promises.

You’ve written a handler in your callbacks class promise.done (callbacks, “processIt”)

The arguments you registered at the time of resolution will be passed to your handler

deferred.resolve (Array(“it worked”,data))



Setup – cDeferred and cPromise classes Here’s the classes that are provided

cDeferred – like $.Deferred() – is used to .resolve() or .reject() a promise, and to provide the promise() instance

cPromise – the .done() and .fail() methods set up what to do on resolution or rejection, very much like the jQuery .done() and .fail() methods. I dont provide .then() or $.Where() but may add them later



Setup – creating a function that returns a promise Every one of these will be different since VBA

has multiple ways of dealing with asynchronicity. However they must follow this structure

Set d = new cDeferred (create an instance).. Do something asyncronous (passing a reference to

d)return d.promise()



Setup – doing something asynchronous The asynchronous function must

Signal completion using the deferred instance that returned the promise in the caller function like this

d.resolve(..arguments...)

Or

d.reject (..arguments)



Setup – arguments The asynchronous function should also return

some arguments. These will be passed on to the function that is eventually called on completion of the task

Arguments can be of any type and number, but should be wrapped in an array like this. This protects them from being incorrectly handled when passing through the chain.

d.resolve (Array(.url, .response.responseText, .optionalArgument))



Memory and scopeOne of the problems with asynch and event processing in VBA is that you can end up with many global

or module level variables to communicate. Using promises to pass arguments avoids this need, since the promise resolution records a reference to the results data to be passed, and thus prevents it going out of scope.

When you create an proc that is going to behave asynchronously though, there is a chance that you will find local variables going out of scope and therefore events not firing – this is nothing to do with promises – but a regular issue with orchestration in VBA. One solution is to use module level variables. Another is to make reference to a transient variable in a single, module level object.

I provide a register class to register asynch variables with, which is declared at module level

Private register As cDeferredRegister

And used like this Dim ca As cHttpDeferred

Set ca = New cHttpDeferred

register.register ca

You should create a teardown method in any classes you create that have special memory leak prevention needs. Register.teardown will clean itself up and execute any teardowns with any object instances that have been registered

register.tearDown()



SummaryThese examples start to address how

asynchronicity might be better orchestrated in VBA within the limitations of the available syntax. Over time I will add other promise related functions such as when()

Memory leaks relating to asynchronicity are not resolved by this, but can at least be identified and mitigated by this cleaner orchestration.

For more detail, examples, and to download see Excel Liberation


http://www.mcpher.com/

orchestrating asynchronicity in excel vba using promises

Technology