VBA Code to Delay Sending Request to Google API Maps

[VballRef]

I have a long list of Zip Codes (approximately 350) that I need to generate mileage to about 65 locations. I found VBA code that works perfectly to generate the mileage; however, the Google API limits make it where about 1/3 of the cells end up returning zero when I try to AutoFill the formulas. Is there a VBA code that would add some time between sending the request to Google?

Here is the current VBA code:

Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
G_DISTANCE = 0
On Error GoTo exitRoute
Origin = Replace(Origin, " ", "%20")
Destination = Replace(Destination, " ", "%20")
Set myRequest = New XMLHTTP60
myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
& Origin & "&destination=" & Destination & "&sensor=false", False
myRequest.send
Set myDomDoc = New DOMDocument60
myDomDoc.LoadXML myRequest.responseText
Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
If Not distanceNode Is Nothing Then G_DISTANCE = (distanceNode.Text / 1000) * 0.62137119 'convert from km to miles
exitRoute:
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function

And the array formula I am using to calculate mileage:

=ROUND(G_DISTANCE($D3,L$2),0)

I am wanting to AutoFill from L3:BW350 without having to fill only a few cells at a time.

I would really appreciate any ideas for this!

 

[Andrew Fergus]

This article is useful to see some options you have:
http://analystcave.com/vba-sleep-vs-wait/

HOWEVER, could it be that there is a reason for a non-response? For example, potential duplicate destinations?

 

[John_w]

I wouldn't use a UDF because you have less control over when Excel recalculates the formulas and sends the requests.

Also, use the Distance Matrix API instead because it would need fewer requests than the Directions API - https://developers.google.com/maps/documentation/distance-matrix/

 

[VballRef]

Thanks! This didn't work for the code in question, but I was able to use the delay feature to build a simple copy/paste macro that achieves the same purpose.

 

[VballRef]

Thanks! This didn't work for the code in question, but I was able to use the delay feature to build a simple copy/paste macro that achieves the same purpose.​

​