I’ve been playing around with creating a promise based framework for VBA for a while now, mainly just to see if it was possible. Here’s a practical implementation that gets the data from a large number of google spreadsheets into Excel, all at the same time.
Here’s some of the objectives
- Orchestrate asynchronous and timer actions using a similar approach to jQuery promises
- Pure VBA only (although I do use a couple of Windows API)
- Simple to use (although difficult to grasp at first as it turns out)
A different approach.
An example
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 ' add a playing worksheets if not already there addPlaySheets Array("rest") ' 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 then 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
Something more complicated
This time we’ll go to Google Spreadsheets, read the schema of a workbook, then retrieve each of the worksheets in that workbook at the same time. Many web services, Google Docs included, will fail if too many requests come at once or if they are busy. So in addition, exponential backoff is also implemented. All this is orchestrated through promises. Here is the calling procedure.
Option Explicit ' example application using promises to get multiple data streams from google docs Public Sub asynchDocs() Dim url As String, key As String key = "0At2ExLh4POiZdFd0YUhpZVRPUGxFcW85X2xkMm1vY2c" url = "https://spreadsheets.google.com/feeds/worksheets/" + key + "/public/basic?alt=json" ' kick this off in the background doDocsWithBackoff url ' ' we can go and do something else now Debug.Print "Im doing something else - have a nice day" End Sub Public Sub doDocsWithBackoff(url As String) Dim b As cBackoff, p As cPromise, prs As cPromise, _ process As cDocsImporter, doneSchema As cDeferred, doneSheets As cDeferred ' always to this for a fresh start - it will clear up any memory/timers from previously clearRegister ' this class knows how to get google spreadsheet Set process = New cDocsImporter Set doneSchema = New cDeferred ' first phase, we get the schema, and release up memory Set b = New cBackoff b.execute(url) _ .done(process, "getSchema", doneSchema) _ .fail process, "show" ' although this will be done synchronously, using a promise means we can know its done Set prs = deleteAllTheSheets ' we'll have resolved doneSchema when the previous .done is executed ' we'll need another deferred to tell us when all this is over Set doneSheets = New cDeferred when(Array(prs, doneSchema.promise)) _ .done(process, "getSheets", doneSheets, url) _ .fail process, "show" ' now we can clear everything up doneSheets.promise _ .done(register, "teardown") _ .done(process, "teardown") _ .fail process, "show" End Sub
More information
For more information on how this is implemented, or to download the workbook see Excel Liberation. This is fairly extreme VBA so this early version will be a little fragile and buggy Check back often for new versions.
Plugins classes
So far I’ve created event driven timers, getting data from a web service and asynchronous ADO operations. I may add others over time, and welcome any contributions you make. The details of how to create these and the code for the promises structure can also be found on the Excel Liberation site.
For more stuff like this see Excel Liberation