Excel Visual Basic, How to stop freezing due to many API calls?

Question

I am using Visual Basic with excel to get some data from an API. I am running around 100 API calls in every iteration of a For-Each loop, each iteration will fill one row of data.

The problem is that, my excel freezes after completing around 4-5 rows!

I think the best solution for this problem is to have a delay of around 3 seconds between each API calls. This will also prevent rate-limiting errors from my API.

This is my current code -

For Each BidRow In Range("A2:A102")
        MyRequest.Open "GET", "https://api.someapi.com/values/" & Range("B" & RowNo)
        MyRequest.Send
        Set JsonBid = JsonConverter.ParseJson(MyRequest.ResponseText)
        Range("D" & RowNo).Value = JsonBid("key")   
        RowNo = RowNo + 1
Next BidRow

JsonConverter is just another module.

Anyway, I do not know any way to do a delay of 3000 ms in Visual Basic, its quite easy to do that in Javascript with settimeout() function. Or maybe I have to some asynchronous coding technique here. I am lost trying a bunch of things now!

Any idea how to solve this?

Possible duplicate of [Access/Excel VBA - Time delay](https://stackoverflow.com/questions/30174395/access-excel-vba-time-delay) — Ken White, Dec 16 '17 at 04:56
Did you try adding `DoEvents` just after the `For Each BidRow In Range("A2:A102")` line? — Subodh Tiwari sktneer, Dec 16 '17 at 05:19

ashleedawg · Accepted Answer · 2017-12-17T18:08:11.033

You could use this sub to pause for a specified number of "whole" seconds:

Sub Pause(delaySeconds As Integer)
    Dim startTime As Single
    startTime = Timer
    Do While Timer < startTime + delaySeconds
        DoEvents    ' Yield to other processes
    Loop
End Sub

For example, Pause(3) will pause for 3 seconds.

However, are you sure that Excel is "freezing" in a non-recoverable way? I use a lot of API's and I know that when I'm looping through a few hundred, it sure looks like Excel is crashing, but with a little patience, the process indeed finishes up.

Instead of pausing (and extending execution time of your sub by 100 × 3 seconds = 5 minutes), what if you added a line just before Next BidRow:

DoEvents

Edit re: API limit of 60 calls per second...

If the site that provides your API has a limit of 60 calls per minute, then your 100 calls should run in no less than 100 seconds. I bet you're below that but you could make sure by timing the procedure.

Before your loop you could add:

Dim LoopStart as Single

LoopStart = Timer

...and then after the loop is completely finished, add a line:

MsgBox "Loop finished in " & Round(Timer - LoopStart,0) & " seconds."

If you exceed the limit, most API's would simply return an error and resume normally in a second or two. I'm sure there are exceptions out there depending on the fine print of the API you're using like in theory the site could charge your credit card for the excessive calls (which would need to be agreed upon in advance) or could potentially ban your IP after repeated abuse (which I've never seen.)

For example, the Google Maps Geocoding API has a very generous "standard" limit:

2,500 free requests per day, and,

50 requests per second.

By default, calls in excess of this will return a (temporary) error, unless you Enable Billing to unlock higher quotas:

2,500 free requests in a day, and then, you will be billed at $0.50 USD / 1000 additional requests, up to 100,000 daily

_(Source)

You could make sure each iteration of the loop takes at least one second with something like this:

For Each BidRow In Range("A2:A102")
    loopStart = Timer 'reset timer for each loop
    MyRequest.Open "GET", "https://api.someapi.com/values/" & Range("B" & RowNo)
    MyRequest.Send
    Set JsonBid = JsonConverter.ParseJson(MyRequest.ResponseText)
    Range("D" & RowNo).Value = JsonBid("key")
    RowNo = RowNo + 1
    Do While Timer < loopStart + 1
        DoEvents    ' Yield to other processes if under 1 second has passed
    Loop
Next BidRow

My problem was that the UI just pauses until the execution is complete, it did not fully stop working. And I thought the solution for that was to use a delay between API calls. But just adding a line named DoEvents worked perfectly! Thanks! — Nifal Adam, Dec 17 '17 at 04:19
Btw is it required to have some delay? Because I am using a public API and they were saying that they rate limit API calls to 60 in a minute. — Nifal Adam, Dec 17 '17 at 04:21

Excel Visual Basic, How to stop freezing due to many API calls?

1 Answers1