Exponential backoff with VBA promises

I posted something on how to do exponential backoff using jQuery promises a while ago. At the time, I never thought I would be implementing that in VBA because of all the asynchroncity and callbacks needed, but since implementing a basic promises for VBA, I realized that a VBA implementation would now be achievable.

Page Content hide
1 What is Exponential backoff
2 Combining with asynchronicity
3 Solution Approach
4 The test case

What is Exponential backoff

Exponential backoff is the recommended way to deal with certain web service temporary failures (according to Google developers’ best practices anyway). The idea is pretty straightforward. If you get a failure due to server too busy or inadequate client throttling, then wait for increasingly long, (but with a random element) amounts of time (exponential amounts) , then try again. Repeat till success or you give up.

Combining with asynchronicity

That in itself is a pretty good strategy for painlessly handling errors like that for a single, synchronous fetch – of the type that we usually limit ourselves to in VBA. But if you want to do many fetches at the same time, asynchronously, and you also combine that with handling backoff and retrying, then the whole thing becomes way too complex to orchestrate using normal VBA techniques. Here’s how to implement it in VBA

Solution Approach

VBA syntax limitations get in the way and complicate things a bit, but we still end up with a digestible solution, even though the background components are a little messy. Here’s some of the complications.

  • Promises for orchestration. These use custom VBA events along with callByName to custom classes to execute completion callbacks.
  • Asynchronous xmlHttp. This needs some fiddling around to create a custom class with a default member.
  • SetTimer. This uses windows api calls to callback time sequenced recursive functions.
  • JSON. This is dealt with extensively on this site in the jSon section. See How to use cJobject for how to parse and organize the returned JSON data.

The test case

In University Rankings visualized, I pick up a large number of worksheets directly from a Google Docs workbook for Google Visualization. I’m going to replicate this, sourcing the data from Google Docs Directly, but creating a chart in Excel. The main demonstration here though, is how to retrieve about 40 worksheets simultaneously and asynchronously, whilst handling orchestration of server overload rejections, using VBA promises.