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. I posted something on how to do What is Exponential backoffExponential 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 asynchronicityThat 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 ApproachVBA 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.
The test caseIn 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. |
Services > Desktop Liberation - the definitive resource for Google Apps Script and Microsoft Office automation > Classes > Promises in VBA >