Promises in VBA

Page Content hide
1 Orchestration of asynchronicty in Excel
2 Deferred promises
3 Deferrals in VBA
4 An Example
5 Here’s a walkthrough –
6 yourCallBacks
7 Details of implementation
8 Share with your network

Orchestration of asynchronicty in Excel

Compared to languages such as javaScript, VBA lacks the syntax to effectively deal with asynchonicity. Javascript is all about starting something and getting a callback when done. VBA has limited, and inconsistent callback capabilities, and event processing is limited to VBA types, or through complex custom event gyrations. Here’s a few slides on a VBA implementation of promises to improve callback orchestration. Since this is in the early stages of development the code is in a separate workbook (promises.xlsm) and can be downloaded here Orchestrating asynchronicity in Excel VBA using promises from Bruce McPherson Orchestrating asynchronicity in Excel VBA using promises from Bruce McPherson

In javascript, routinely we use a construct like this, where the doSomething function would callback when complete.

doSomething ( 
    function(data) {  
        process(data) 
    } );

and the doSomething function would look like this

function doSomething (fCallback) {
// make some data
    var data = { …. };

// callback to process
       callBack (data);
}

Creating something simple like that in VBA would demand some gymnastics.

Deferred promises

Even in js though, orchestrating multiple asynchronous events can get messy. That’s why we are seeing the use of promises becoming very common in frameworks such as jQuery. I use this a lot, and you’ll find many examples on this site. Here’s an example of a function that returns a deferred promise. What that means is that it will return control to the calling function while its performing some asynchronous action – in this case getJSON – along with a reference to a promise of completion. In the meantime the caller can get on with something else. When getJSON is done, the deferred object is either resolved (it worked), or is rejected (it failed) and the referenced promise can then receive the data or the error message.

function getPromiseData(url,proxyUrl){        var deferred = $.Deferred();        var u = proxyUrl ? proxyUrl + “?url=”+encodeURIComponent(url) : url + “&callback=?”;            $.getJSON(u, null,             function (data) {                deferred.resolve(data);            })            .error(function(res, status, err) {                deferred.reject(“error ” + err + ” for ” + url);            });                  return deferred.promise();    }

  the caller looks like this

    var promise = getPromiseData(someUrl);

      //.. get on with some other stuff    

    promise.done ( 
        function (data) {
            process (data) 
    } )

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

Now at first glance that doesn’t look like a huge improvement over using regular callbacks – but where you have a few callbacks going on at the same time, it completely detangles the flow.

Deferrals in VBA

I wanted to see if the something like this could be done with VBA. Although there are not that many async opportunities, following a pattern like this could certainly demystify their use and make it easier to make the conceptual transition to javascript like languages.

An Example

Let’s say we want to get data from a web site asynchronously, and do something else while its happening (including giving control back to Excel). Here’s how simple it will look using VBA promises.

Private Sub smallTest()    ‘ you have to add your callbacks in a class as below    Dim callbacks As yourCallbacks, load As cPromise        ‘ in case anything old async remnants are hanging around    clearRegister        ‘ whatever you will be calling back should be defined here    Set callbacks = New yourCallbacks         ‘ we’ll pass the range so that it gets carried forward when resolved    Set load = loadData(“https://ajax.googleapis.com/ajax/services/search/patent?v=1.0&rsz=8&q=mouse”, Range(“rest!a1”))        ‘ and we can do these things when it’s all over    load.done(callbacks, “populate”) _        .done(register, “tearDown”) _        .fail callbacks, “show”     ‘ when we get here, all the previous activity is still probably going on.    Debug.Print “i could be doing something else while Im waiting”     End Sub Private Function loadData(url As String, Optional a As Variant) As cPromise        ‘ get data from a web site    Dim ca As cHttpDeferred    Set ca = New cHttpDeferred        ‘ this returns a promise that will get resolved when completed    Set loadData = ca.getPromise(url, Array(a)) End Function

Here’s a walkthrough –

  • loadData() will use a custom class I’ve provided – cHttp. This is a ‘promise enabled’ class which can get data asynchronously. loadData() just creates an instance of that class and passes back its promise.
  • back in the caller, we can queue up things to happen if cHttp succeeds or fails – 
                load.done( … do something good …)
                        .fail  …report something bad..
  • We then exit, and in the meantime the data is still loading, and .done and .fail are waiting for something to happen.

yourCallBacks

Because of syntactical limitations in VBA, we cant easily pass a function to be executed when the promise is resolved. Instead we have to centralize all callbacks in a single class – I’ve called it yourCallBacks, with methods to process the result of the .done or .fail.  

For example, this means that  callbacks.populate() should be run when the promise returned by loadData() is resolved
load.done(callbacks, “populate”) 

Here’s what callbacks.populate() looks like – it will take the data returned by the promise resolution and pass it on.

Public Sub populate(a As Variant, Optional defer As cDeferred, Optional args As Variant)    Dim r As Range, lb As Long    lb = LBound(a)    Set r = a(lb + 2)(lb)    r.value = a(LBound(a) + 1)    Debug.Print ("ive populated the sheet from " & CStr(a(LBound(a) + 0)))End Sub

Details of implementation

Here’s some example promise enabled classes you can use, modify or copy.

  • asynch ADO using promises
    SetTimer and VBA
  • Complex aynchronous web services
  • VBA promise implementation

Since this is in the early stages of development the code is in a separate workbook (promises.xlsm) and can be downloaded.