VBA Code to Delay Sending Request to Google API Maps

VballRef

New Member
Joined
Feb 12, 2018
Messages
6
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!
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
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.
 
Upvote 0
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.​





 
Upvote 0

Forum statistics

Threads
1,223,895
Messages
6,175,257
Members
452,625
Latest member
saadat28

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top