3

I found this fantastic vba script which takes an address and returns the latitude and longitude using google's maps API. However, google has rate-limiting features, and I want to do this for a large number of addresses (approx 3 mil). The script, on the other hand, has no rate-limiting, making them rather incompatible. Is it possible to edit this script so that it doesn't move on to the next cell until the previous request has been completed?

    Function MyGeocode(address As String) As String
  Dim strAddress As String
  Dim strQuery As String
  Dim strLatitude As String
  Dim strLongitude As String
  strAddress = URLEncode(address)
  'Assemble the query string
  strQuery = "http://maps.googleapis.com/maps/api/geocode/xml?"
  strQuery = strQuery & "address=" & strAddress
  strQuery = strQuery & "&sensor=false"
  'define XML and HTTP components
  Dim googleResult As New MSXML2.DOMDocument60
  Dim googleService As New MSXML2.XMLHTTP60
  Dim oNodes As MSXML2.IXMLDOMNodeList
  Dim oNode As MSXML2.IXMLDOMNode
  'create HTTP request to query URL - make sure to have
  'that last "False" there for synchronous operation
  googleService.Open "GET", strQuery, False
  googleService.send
  googleResult.LoadXML (googleService.responseText)
  Set oNodes = googleResult.getElementsByTagName("geometry")
  If oNodes.Length = 1 Then
    For Each oNode In oNodes
      strLatitude = oNode.ChildNodes(0).ChildNodes(0).Text
      strLongitude = oNode.ChildNodes(0).ChildNodes(1).Text
      MyGeocode = strLatitude & "," & strLongitude
    Next oNode
  Else
    MyGeocode = "Not Found (try again, you may have done too many too fast)"
  End If
End Function
Public Function URLEncode(StringVal As String, Optional SpaceAsPlus As Boolean = False) As String
  Dim StringLen As Long: StringLen = Len(StringVal)
  If StringLen > 0 Then
    ReDim result(StringLen) As String
    Dim i As Long, CharCode As Integer
    Dim Char As String, Space As String
    If SpaceAsPlus Then Space = "+" Else Space = "%20"
    For i = 1 To StringLen
      Char = Mid$(StringVal, i, 1)
      CharCode = Asc(Char)
      Select Case CharCode
      Case 97 To 122, 65 To 90, 48 To 57, 45, 46, 95, 126
        result(i) = Char
      Case 32
        result(i) = Space
      Case 0 To 15
        result(i) = "%0" & Hex(CharCode)
      Case Else
        result(i) = "%" & Hex(CharCode)
      End Select
    Next i
    URLEncode = Join(result, "")
  End If
End Function

Any help would be greatly appreciated, you guys are the best!

  • 3
    This code provides functions to use but does not actually do anything by itself. You need to apply the rate limiting in whatever you are using to call these functions. – qasdfdsaq Sep 22 '16 at 13:10
  • Please include the code where you use this function – thilina R Sep 26 '16 at 13:15
1

The code you've posted simply tells Excel how to query Google Maps, it doesn't actually do it - you need to call the MyGeocode function and tell Excel which addresses you want to query.

First press Alt+F11 to access the Visual Basic Editor (VBE). Right-click the name of your workbook in the left panel and select Insert > Module. Paste the VBA into this new module.

To run this code you need to add a reference to the Microsoft XML, v6.0 library. In the VBE click Tools > References, scroll down and tick the box next to the library. Click OK.

Now back to Excel itself.

Let's say you have a subset of your 3 million addresses in columns A:D.

enter image description here

In column E concatenate all your address fields so you get the whole address in one cell:

=A2&" "&B2&" "&C2&" "&D2

Then in column F we can call the co-ordinates from Google as follows:

=MyGeocode(E2)

There is no need to tell Excel to pause in between calls - you control this by the number of times you tell Excel to run =MyGeocode() - ie how many rows in Excel you copy the formula down.

To be safe, run this on no more than 2,500 lines a day to comply with Google's T&Cs (thanks Seth), unless you're willing to spend some cash to increase the number of calls you can make to 100,000 a day.

| improve this answer | |
  • 1
    Looks like it could be this particular API: 2.5k Requests a day for premium 100k. If those are the right numbers it would take between 1200 and 30 days if you don't purchase credits. – Seth Sep 23 '16 at 5:18
  • Good find. I'll edit accordingly. – Andi Mohr Sep 23 '16 at 8:30
  • Thank you so much for your help, and I will keep the rate limiting in mind to be compliant with the T&Cs. Your help is really appreciated. – Lukas Ljungstrom Sep 26 '16 at 7:02
0

You can make VBA halt a program for, say, 5 seconds as follows:

Application.Wait(Now + TimeValue("0:00:05")) 

Which might work for you

| improve this answer | |
  • Hey that seems like exactly what I'm looking for; but could you put it in the appropriate place in the program - I am a total beginner! :) – Lukas Ljungstrom Sep 22 '16 at 13:14
  • It doesn't belong in any of the code you posted. – Seth Sep 22 '16 at 13:21
  • Does that mean I can't use that method? – Lukas Ljungstrom Sep 22 '16 at 13:22
  • You can use it put your code is only a fragment and usually you'd put it in whatever loop that code is called from. – Seth Sep 23 '16 at 5:14
0

If you have three million addresses you might want to consider getting a commercial access that lets you use the service without rate limiting. Otherwise use something like:

Msgbox "Click to go on", , "Example"

As from the Terms of Use:

d. API Limitations

Google sets and enforces limits on your use of the APIs (e.g. limiting the number of API requests that you may make or the number of users you may serve), in our sole discretion. You agree to, and will not attempt to circumvent, such limitations documented with each API. If you would like to use any API beyond these limits, you must obtain Google's express consent (and Google may decline such request or condition acceptance on your agreement to additional terms and/or charges for that use). To seek such approval, contact the relevant Google API team for information (e.g. by using the Google developers console).

| improve this answer | |
  • Thanks for the quick response, I don't think I'll need commerical access, it can just run for a few days and be done; however, is there a way for it to limit itself rather than having a human click go on manually? Additionally, I would really appreciate it if you added it to the code, as being the noob that I am, I have no idea where I would put that line in the whole mess that is the code I already have. Thanks again Seth! – Lukas Ljungstrom Sep 22 '16 at 12:58
  • With a dataset of three million entries you are a commerical user most likely. Aside from that go and pick up a book about programming to learn what to do. Superuser is not the place to ask for work to be done for you. In fact this should be on stackoverflow and even there it would be wrong to ask in this way. The "mess" is copied from somewhere. Go and start learning what it does. – Seth Sep 22 '16 at 13:18
  • I'm sorry if I'm not asking in the right way and if I had to do many things like this then of course I would learn. However it's not particularly efficient for me to learn how to code for one request? You and CallumDA3 seemed happy to help only a few moments ago, I'm sorry to have irritated you. – Lukas Ljungstrom Sep 22 '16 at 13:27
  • You are asking the right way. But you're essentially asking "I have this work to do, would someone do it for me for free?". If you don't have the time to spend on this pay someone to do it. You're talking about a data set of three million entries (assuming European standards) that's 3.000.000 entries. That is some serious work even if you only need to do it once! – Seth Sep 23 '16 at 5:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.