Geocoding using Microsoft Excel 2013 and Google Maps API

Geocoding using Microsoft Excel 2013 and Google Maps API

Geocoding is the process of converting addresses to the x and y coordinate or longitude and latitude. There are various option available in the market to perform geocoding, some are paid and some are free. You can use The Google Maps Geocoding API to retrieve the longitude and latitude for free. Later you can convert this longitude and latitude to your desire coordinate values, there are number of online tools for doing that. So it will be hassle to perform each addresses one at time in google, for that you can use Excel software to perform large number of addresses. You create excel function to perform the geocoding for multiple addresses.

For this task I am using Microsoft Excel 2013, you can use 2007 or 2010 version. First task is to turn on the Developer tab. To find this tab in 2007 or 2010 version you have to play around.

1. Go to the Microsoft File > Options > Customize Ribbon and check Developer


You will notice Developer tab is added on the ribbon.


2. Now click the Visual Basic button on the far left corner which will open the new window. Now we want a new module to insert which will store our new function. For that click on Insert > New Module


Copy the below code and paste into the empty area (code source: policeanalyst.com)

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.DOMDocument
  Dim googleService As New MSXML2.XMLHTTP
  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

This code make use of Microsoft’s XML library, so you have to add references to it. To add the reference go to Tools > References

You will see list of references with the check box, select the Microsoft XML v6.0, after that click Ok button.

Finally save your Visual basic window by pressing save button. Then from the drop down menu of Save as type select Excel Add-In, assign the file name of your desire. Once the file in saved in “*.xlam” extension you can open any new document and use the function. Close all the open excel files and open the new one.

3. Open the new excel file and then go to file > Options > Add-Inns. From the Manage drop down box select the Excel-Add Ins.

After selecting then press the GO button, this will list all the saved Add-ins files, as I have saved as Book1. Xlam I will check that box. Then press Ok after selecting,

4. As everything is done to perform the geocode, we are going to use the function to retrieve the longitude and latitude value. Let us suppose I have address 65 Hawthorn way, Olds, Alberta, Canada then you type that value in excel and use the function MyGeocode (as you type my it will show function on the box).


It will take few seconds to run the function because it has to pull the information from the google server and then result will display.


Now if you have number of addresses and you want to run the geocode then this function will help you. Just keep in mind few things while using this function, after the execution process is done then you can copy and paste the values because each time you open the file then it will processing each cells which might take up few minutes to perform it.

Second things as it is free they will not allow you to do perform thousands of geocode at once, so best way is to have a 10 seconds break between addresses. If Google stop you for retrieving large number of addresses, you will see error saying “Not Found (try again, you may have done too many too fast)”. See below example, I have tried few number of addressee without following 10 seconds rule.


I hope you liked this tutorial, please comment if you have any question regarding this.

This Post Has 104 Comments

  1. Hello,
    I am working on a final year project and after finding the longitudes and latitudes i want to get a function that can provide the distance between them by car from google maps to form a matrix.
    Thanks

  2. Hi thanks for this. My excel sheets give me a compile error…User-defined type not defined. Have searched but cannot get it to work – any ideas?

    1. Well Thanks, I will see that.

  3. Hi. Thanks for this, but I get the same error as Stuart. Do you already have a solution by now?

    1. I will see tonight and send you message, is this your email address mme……

      1. Just thought to ask you which Microsoft excel version you are using it, I wrote this for 2013 version but will check later to confirm for other version

  4. My excel sheets give me a compile error…User-defined type not defined. Have searched but cannot get it to work – any ideas?
    i am using excel 2016 version

  5. Keep getting an error that file is not a valid Add-In. Running Excel 2010. Any thoughts?

  6. Getting the same error too;

    googleResult As New MSXML2.DOMDocument (This line seems to be highlighted)

  7. Today I checked on Microsoft 2010 and the code is working, yes MSXML2.DOMDocument get highlighted if you forget to checked in Microsoft XML v6.0, please see above steps. Also do not forget to enable Macros security. If you guys have any problem on 2010 and 2013 versions let me know, becasue these two I have checked. Just make sure you follow all the steps it will work. Thank you for the patience and my apology for not responding on time.

  8. I get the error on the ‘Dim googleResult As New MSXML2.DOMDocument’ line with Excel 2013 and I can confirm that I do have the Microsoft XML v6.0 library checked off.

  9. Here is the solution for those in the future:

    Dim googleResult As Object
    Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)

    Dim googleService As Object
    Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)

    Also, for those concerned about the timing… create a macro with 1 second delay between each iteration of the formula. Also, Google restricts an IP address to just 2500 instances with their GeoCoding service within a 24 hour period. I’ve attached a sample below… .for my example I had my addresses listed in column H and my formula =MyGeocode was used in column I… just adjust this according to your worksheet. I also changed the CodeName of the worksheet to GeoCode.

    Sub GeoCode()
    Dim GeoCode As Worksheet
    Set GeoCode = ThisWorkbook.Sheets(“GeoCode”)
    Dim i As Integer

    i = GeoCode.Cells(GeoCode.Rows.Count, 9).End(xlUp).Row
    For i = i To (i + 2400)
    GeoCode.Range(Cells(i, 9), Cells(i, 9)).Formula = “=MyGeoCode(H” & i & “)”
    Application.Wait (Now + TimeValue(“0:00:01”))
    GeoCode.Range(Cells(i, 9), Cells(i, 9)) = GeoCode.Range(Cells(i, 9), Cells(i, 9)).Value
    Next

    End Sub

    1. Thanks Colin

    2. If you’re copying and pasting this code, watch out for the smart quotes. You’ll need to replace them with the straight up-and-down type.

  10. Thank you so much! it works perfect!!!

  11. I am a novice with excel with limited knowledge. Will you send me the syntax of the actual formula you are typing in? Thank you in advance.

  12. Thanks for this–your code along with Colin’s notes worked perfectly.

    I’ve been trying to figure out how to do something similar, except to get the Zip Code when given just the street, city and state. Can you show me how to do this?

    Thanks.

  13. Hello,

    I am trying to use my own api key with this tool.

    I tried replacing

    strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”

    with

    strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?key=YOUR_API_KEY”

    and it does not work. Any ideas?

  14. I got the function to work with Exel 2016 with Colin’s adjustments. But if you recalculate the sheet and you have many lines of address you get the “Not Found” error probably because you have requested either too many in the day or too fast. So if an “if” statement could be added before the “googleservice get” to test the contents of the current cell and if “Not Found” then execute else skip then upon open/close/recalculate of the XLS or automatic or manual recalc not every address would then be reevaluated. Not a VBA guru. Anyone have the sample code to do that?

  15. Thanks for the code – works great. However, I would like to overcome the time/daily max limits so I signed up for the Google billing. How do I go about using my “Key” for the API?

    1. Can anybody help Howie, Please? Let me know Howie if you did not receive answer.

  16. Main Boulevard ,Lahore,Punjab #VALUE!

    I’m working on my thesis and i need coordinates of about 3000 locations but the excel is showing this VALUE error. i’ve used the same address in the tutorial for a test run but the error is same for “65 Hawthorn way, Olds, Alberta, Canada” location also. Any solution to this problem?

    1. =MyGeocode() will give you an error. Put your address cell reference inside parentheses:
      =MyGeocode(A1) given cell A1 contains address.

  17. put this
    Sheets(“GeoCode”).Cells(i, 9).Copy

    Sheets(“GeoCode”).Cells(i, 9).PasteSpecial xlPasteValues

    before the wait time in colins code to past the values over the forumla so next time you open excel it wont refresh what you already geomapped

    1. Thanks

      1. He agregado todo habilitado macros, agregado microsoft v6 y mi código no me marca error pero al ejecutar la función en la celda se cicla y ya no responde mi exel.

  18. I am trying to perform reverse geocoding in Excel. Would you happen to have the code for that?

  19. This was working great for me for months, I came back into work after having the weekend off and now I am getting a number of errors. Sometimes Excel will hang up and crash, then sometimes it will just list #VALUE! Not sure what happened. Tried removing and reinstalling the add-in with no luck. Any suggestions?

    1. I will try

  20. i want to use google map api and not bing. While using the code you shared, and after adding
    Dim googleResult As Object
    Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)

    Dim googleService As Object
    Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)

    it is still giving error of ‘syntax error’ under compile error.

    Pls help.

  21. ¿Hello, is it possible to something like this to get the next data?: Address, sublocality, sublocality_level_1 and administrative_area_level_1 . I would provide latitude and longitude in excel. Thanks.

  22. Thank you so much. It works perfectly!! In Excel 2013, I have used it, and earlier an error message popped up, which got rectified as I checked v6.0 Microsoft XML

    1. Thank you

  23. Is there anyway to have the myGeoCode function automatically replace the formula in the cell with its value? I tried the macro, but I could not get it to work and I am completely lost with VBA.

  24. How mucho would you charge me to write something that allows me to get the next data in excel:

    neighborhood
    locality
    Sublocality
    sublocality_level_1
    administrative_area_level_1

    I have latitude, longitude and address

  25. Your website is very helpful to me.
    I was running my Excel vba program and it downloaded zip code. But stopped as exceeded daily limit. So I’ve an API Key for Google geocoding API. Please e-mail me how I can use this API key to download about 20,000 zip code in 2-3 days. Thank you for your help.

    1. Ok Momtaz, I will send you an email. Thank you

      1. Hi,

        we have a google for works client id. Could you sent us the vba code to geocode with using the api-key?

        Thank you,
        Andreas

  26. Thanks, this works great. Is there a way to have results show latitude and longitude in separate columns? Thanks.

    1. I will try to see.. Thank you

  27. Does this calculate straight line distance or driving distance? I’ve gone through a lot of these VBA’s and each has done straight line and I am looking for driving distance.

    1. I have to see, thanks for your comment

  28. Seems to work well. Thanks!!!

  29. Thanks,

    It work very well in English language. However, if my address is another language such as Thai language, could you please suggest me to find latitude and longitude from them. Unless I have to translate all data in English.

    Google map also has Thai language.

    Regards,

    1. Change the line strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?” to strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?&language=th”. Try this and let me know 🙂

      1. I tried to use this for Greek language since I have the same problem but does not work. Can you help?

        Seel below
        I add &language=el

        ‘Assemble the query string
        strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?&language=el”
        strQuery = strQuery & “address=” & strAddress
        strQuery = strQuery & “&sensor=false”

        I tried for the following address
        Address :
        Αθανάτων30,Aχαρναί

        Result:
        Not Found (try again, you may have done too many too fast)

        I put the same code (URL) in chrome and is working fine

        http://maps.googleapis.com/maps/api/geocode/xml?&language=el&address=Aθανάτων30, Aχαρναί &sensor=false

        Any idea?

        1. @Thimios Where you able to solve it?

  30. hi, how can i doit backwords, i have the latitude and longitude but need to get the address back.

    1. I will see that and will update you.

  31. If you are getting a user defined type not defined error around this section of the code –> MSXML2.DOMDocument , try unchecking the Microsoft XML v6.0 reference and checking the Microsoft XML v3.0 one instead and so forth.

    1. Thanks Karen

  32. This was working for me until yesterday. Now I am getting #Value! errors. I have logged in an out of my API account. Tried requesting a new API Key. I have tried the above XML related suggestions. Nothing is working. Does anyone have any suggestions?

  33. As I was trying to debug it, deleting and rewriting the codes, noticed that VB now takes these new integrated handles instead. Note the “60” at the end of each line below with reference to the MS.XML6.0. Just adding 60 at the end of these lines got it working.

    Dim googleResult As New MSXML2.DOMDocument60
    Dim googleService As New MSXML2.XMLHTTP60

    Good luck everyone!

    1. Thank you 🙂

  34. Its awesome!! Thanks a ton..

    1. Thank you Dipti

  35. Can we get the longitude and latitude separately?

    1. Oh, I have success to get the longitude and latitude separately with amending the source and make different module for longitude and latitude.

      1. Hi Jaeho, Great Job, we will appreciate if you can add your solution on the comment so others know how to do it. Thank you once again

  36. Thank you very much, your website and this post are really useful with me. I have a other project involve with geocoding and google API. I want to find all the nearest places like ATM, Banks, restaurants…with given my location or latitude, longitude.

    Thanks for your time, and hope you reply my answer.

    1. It will much easier if you easy Arcgis software to perform analysis.

  37. Hello,

    I am running EXCEL 2016, followed intructions and I am getting
    Dim googleResult As New MSXML2.DOMDocument type no defined
    appreciate any help on this.
    best regads, Hector

  38. SOLVED! with

    Dim googleResult As Object
    Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)

    Dim googleService As Object
    Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)

    Thank you!

  39. I too am using the GoogleMaps geocoding service to geocode over 1,000 addresses. Got the same message “Not Found (try again, you may have done too many too fast).” Set up a billable Google Maps Geocoding API account, but don’t know how to use it with the Excel routing. Same question as Andres Gomez, which was answered in an off line email. Anyone have a solution here?

  40. Hi. Works great. Thanks for the code. Saw a couple of posts:
    1. How do I modify to separate Long and Lat into 2 fields?
    2. If I have a Google API key, how can I add to the StrQuery statement?
    Thanks in advance.
    RC

  41. I am using Excel 2010. I made the corrections published by Colin 30 Aug 2016 for proper dimensioning of googleResult and googleService. Even when I extended this to oNodes and oNode. I continue to get #VALUE! error no matter the format of the input address. The remark is that “A value used in the formula is of the wrong data type”. What are your suggestions to correct this?

  42. insert your cell address between parentheses like this:
    =MyGeocode(A1)

  43. Hey, Guys, i got it to work by adding 60 to both “Dim googleResult As New MSXML2.DOMDocument” and ” Dim googleService As New MSXML2.XMLHTTP”. Now no matter what I do i keep getting the message Not Found (try again, you may have done too many too fast)

    I am connected to the internet L.O.L. I cannot figure out the problem kindly advice me on how to resolve the issue

    1. Can anybody help this gentleman, please

  44. Hey! I believe I figured out how to use your Google API alongside this macro add on. Just adjust the macro as below. Essentially you are adding your API key to the end of all of the strings, so you want to add it to the last StrQuery.

    ‘Assemble the query string
    strQuery = “http://maps.googleapis.com/maps/api/geocode/xml?”
    strQuery = strQuery & “address=” & strAddress
    strQuery = strQuery & “&sensor=false” & “&key=YOUR_API_KEY”
    ‘define XML and HTTP components

    Let me know if this works for you as well.

    1. Later I had some trouble with this, but removing the &key= before my Api key seemed to rectify that which makes me wonder if my api key is just being ignored.

  45. OK! Sorry Everyone. I didn’t have it working but now I do. See below for the changes you need to make in the add-on in order to make the API key work. What I was missing earlier is that the web address must be https://

    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 = “https://maps.googleapis.com/maps/api/geocode/xml?”
    strQuery = strQuery & “address=” & strAddress
    strQuery = strQuery & “&sensor=false”
    strQuery = strQuery & “&key=MY_API_KEY”
    ‘define XML and HTTP components

    1. Thank you 🙂

  46. I have no errors on execution but i don’t get a result i just have =MyGeocode(L2) typed in my cell, what i’m missing ?
    Thanks in advance

  47. Hi, I got an error “in My excel sheets give me a compile error…User-defined type nodefined”. Could anyone get any ideas?

  48. It works great! Many Thanks! Is there a way to get also “Result quality” and “Returned address” in other columns? Example
    “Address”,Latitude,Longitude,Quality,”Returned address”
    “corso lodi 13, milano”,45.450693,9.205117,Rooftop,”Corso Lodi, 13, 20135 Milano MI, Italia”

    1. Thanks Fabio

    2. MyGeocode function wont compile because of MSXML2.DOMDocument and probably MSXML2.XMLHTTP. Will appreciate your advice.

  49. hi may i have the solution for this problem, because in xls 2007 the error and didnt works any one can help tx

    1. Help him please 🙂

  50. for everybody having difficulty with this, I strongly suggest you take the admin’s suggestion and use the excel sheet recommended on: http://grindgis.com/software/microsoft-excel/geocoding-excel-and-bing

    it uses Bing to get the geocodes because Bing doesn’t have a 1,000 records/day limit like Google and then it maps them with google maps. best of both worlds!. I downloaded it, created a Bing maps key in under 60 seconds and presto it worked perfectly!! thank you so much for this!!!

    1. Thank you Jeff 🙂

  51. Using the reverse coding to get an address from Lat / Long. Issue… I need to have it return an address with county.

    How can I make the “Formatted_address” include county.
    below is my code for GEOAddress

    Function GEOAddress(dblLatitude As Double, dblLongitude) As String

    Dim strJSON As String
    Dim strAddress As String
    Dim lngTemp As Long
    Dim objXml As Object
    Dim strURL As String

    strURL = “http://maps.googleapis.com/maps/api/geocode/json?latlng=” & dblLatitude & “,” & dblLongitude & “&sensor=false”
    Set objXml = CreateObject(“Microsoft.XMLHTTP”)
    With objXml
    .Open “GET”, strURL, False
    .send
    strJSON = .responseText
    End With
    Set objXml = Nothing

    lngTemp = InStr(1, strJSON, “formatted_address”)
    strAddress = Mid(strJSON, lngTemp + 22, InStr(lngTemp, strJSON, “””,”) – (lngTemp + 22))
    GEOAddress = strAddress

    End Function

  52. I’m using Excel 2016. I have two questions:
    1. How can I set the module to insert the coordinates in two separate cell? a latitude column and a longitude column?
    Is it possible to use this module if my wordbook is dynamic? new addresses being added and removed.

    regards
    Yoni

  53. I have another question:
    can it recognize addresses in Hebrew?
    and how can i set it to read an address from two cells (city: a1, address: b1)?

  54. I performed all the steps, at the time of geocoding the address in EXCEL appears: Compile Error. The user-defined type was not defined.

    this is the command with the error: Dim googleResult As New MSXML2.DOMDocument.

    the addresses are for Brazil, and I use WINDOWS 2016.

  55. Every time I try using the =MyGeocode() function I am met with the “Not Found (try again, you may have done too many too fast)” error even though I am only trying to geolocate one address as a test. I am using Windows 2016 I am wondering if that is the issue.

    I have followed all of the steps to the letter. Here is the Visual Basic script I used as it needed a fix included in the comments above:

    “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 Object
    Set googleResult = CreateObject(“MSXML2.DOMDocument.6.0”)

    Dim googleService As Object
    Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
    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”

    Anyone have any thoughts or suggestions? Thanks very much!

    1. Thank you for your comment, can anybody help Jake please?

      1. Jake I will try to check with 2016 version.

        1. Any luck testing with excel 2016? I recently upgraded my machine and OS to Windows 10 and I also reinstalled Excel 2013 but am still encountering issues. I am only receiving the error “Not Found” in both excel 2013 and 2016. Have there been any updates to Googles API that could be affecting this? Any help is greatly appreciated. Thank you.

  56. Hi, thanks so much for the script!
    I used it a lot in another computer, in April, and now I’m trying to use it in a different one, and I always get the message “Not Found …”
    I’m using Office 2010, made a xlam, with the suggesting of Dexter (API key, httpS, ..) and with the 6.0 versions.
    The xml 6.0, http, internet, everything seems active and working fine, macros are enabled.
    Using a query string directly on browser, like “https://maps.googleapis.com/maps/api/geocode/xml?key=MY_KEY&address=1900-104Lisboa&sensor=false” gives a good response and result, but on excel, the same address (or other ones), always give me the error “Not Found (try again, you may have done too many too fast)”.
    Any clues of what could be wrong?

  57. I think Google updated their cloud services requiring you to have payment information on file so they can charge you if you use their service more that the free.

  58. In case it is useful for anyone, steps I went through to get this working on Google.
    1. Google Cloud Account
    Yes you need one now. You get $300 worth free. You have to give your credit card but they are very clear that you will never be charged without you authorising it. This is needed to get a personal ‘API key’. This goes into the code and is sent with every call.
    2. Compile Error as others mentioned above.
    The trick from Nate above fixed this for me:
    Dim googleResult As New MSXML2.DOMDocument60
    Dim googleService As New MSXML2.XMLHTTP60
    3. API Key usage
    The help from Dexter worked for me:
    i) Use https://
    ii) Use strQuery = strQuery & “&key=MY_API_KEY”
    BUT be careful with Cut and Paste!
    The quotation marks may not copy correctly so you need to re-type them. This showed as a compile error in VBA.
    Also somehow I added a space before the = sign. Watch for that.
    MY_API_KEY was replaced with the ~40chars key that Google assigned to me.
    4) “Not Found (try again, you may have done too many too fast)”
    After I’d fixed the above issues the function was working correctly sometimes but not others.
    By stepping through the code I found that Google was returning something but still the error was occurring.
    In the below code the If statement was returning the message not because oNodes.Length was zero but because it was more than 1.
    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

    Changing to ‘ If oNodes.Length => 1 Then’ fixed this.
    An interesting case was an address in Germany that worked fine with the ‘=1’ code if it had a street/City/PostCode/Country structure, but adding an extra address line between street and City caused it to only work with the ‘=> 1’ code.
    Anyone know why?

    A suggestion to Admin is to supply a development macro version of the code with the variables sent to a worksheet. That can be tried first and the sent/return variables evaluated.
    Big thanks to Admin and all other contributors.
    Regards
    Dave

    1. Thank You Dave 🙂

    2. Thanks Dave! It worked perfectly!

      Just one question… Do you happen to know what is the daily limit of searches with the free trial?

  59. This doesn’t give me any errors but I get a blank cell.

    I had errors, I added the 60 to the end of those 2 lines which got rid of the errors but now it just doesn’t return anything.

    I have tried changing the type of the column, but still no results

    Any ideas?

  60. Hi i have followed your steps and i am ok with it. But now the question is how to use the Function in excel cell. I am using it like =MyGeocode(“Address Cell”) but it gives error

  61. Does anyone use this on non-Western alphabets? If I try for Jordan or Morocco in local language can it give a result?

  62. I got everything working but i want to enforce English language.
    when i’m adding & “language=en” it doesn’t work.

    any idea?

    Code:
    Function ReverseGeocode(lat As String, lng As String) As String
    Dim strAddress As String
    Dim strLat As String
    Dim strLng As String
    Dim strQuery As String
    Dim strLatitude As String
    Dim strLongitude As String

    strLat = URLEncode(lat)
    strLng = URLEncode(lng)

    ‘Assemble the query string
    strQuery = gstrGeocodingURL
    strQuery = strQuery & “latlng=” & strLat & “,” & strLng & “language=en”
    If gintType = 0 Then ‘ free Google Geocoder
    strQuery = strQuery & “&sensor=false”
    ElseIf gintType = 1 Then ‘ Enterprise Geocoder
    strQuery = strQuery & “&client=” & gstrClientID
    strQuery = strQuery & “&signature=” & Base64_HMACSHA1(strQuery, gstrKey)
    ElseIf gintType = 2 Then ‘ API Premium Plan
    strQuery = strQuery & “&key=” & gstrKey
    End If

    ‘define XML and HTTP components
    Dim googleResult As Object
    Set googleResult = CreateObject(“Msxml2.DOMDocument.6.0”)
    Dim googleService As Object
    Set googleService = CreateObject(“MSXML2.XMLHTTP.6.0”)
    Dim oNodes As MSXML2.IXMLDOMNodeList
    Dim oNode As MSXML2.IXMLDOMNode

    ‘create HTTP request to query URL – make sure to have
    googleService.Open “GET”, gstrGeocodingDomain & strQuery, False
    googleService.send
    googleResult.LoadXML (googleService.responseText)

    Set oNodes = googleResult.getElementsByTagName(“formatted_address”)

    If oNodes.Length > 0 Then
    ReverseGeocode = oNodes.Item(0).Text

    Else
    ReverseGeocode = “Not Found (try again, you may have done too many too fast)”
    End If

    End Function

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Close Menu