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.
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
- 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.