Geocoding Using VBA & Google API (2020 Update)

Share this

June 12, 2014

Geocoding Using VBA Google API


Introduction


According to Wikipedia:

“Geocoding is the process of finding associated geographic coordinates (often expressed as latitude and longitude) from other geographic data, such as street addresses, or ZIP codes (postal codes). With geographic coordinates, the features can be mapped and entered into Geographic Information Systems, or the coordinates can be embedded into media such as digital photographs via geotagging.”

Almost 10 months have passed since my last posts about Google APIs (Trip Distance and Elevation functions accordingly), so, today, we will see how to extract the latitude and longitude of a given address using  Google Geocoding API. The developed VBA function can be utilized directly in Excel, as long as the user provides a valid address. The so-called GetCoordinates function sends a request to the corresponding Google server and, then, uses the server’s response to read the appropriate XML nodes to extract the required information (latitude, longitude).

Based on GetCoordinates, the other two functions were derived: GetLatitude and GetLongitude. As their names imply, they return (as a number) the latitude and the longitude of the given address, in case the user needs only one of the two returned parameters of the GetCoordinates function. If you need the opposite (Reverse Geocoding), check the GetAddress function.

 


VBA code


Below you will find the VBA code of the GetCoordinates, GetLatitude, and GetLongitude functions. Keep in mind that the Google Geocoding API is subject to a limit of 40,000 requests per month, so be careful not to exceed this limit.

2018 Update: the function has been updated to reflect the changes in Google API. To use this VBA function, you will need a valid API key. Check this link that presents a step-by-step guide on how to acquire one for free.

2018 Update 2 (July): The EncodeURL function was added to avoid problems with special characters. This is a common problem with addresses from Greece, Serbia, Germany, and some other countries. At this point, I would like to thank Viacheslav Komarivskyi for this suggestion. However, this function is only available in Excel 2013 and newer versions.

2020 Update: The code was switched to late binding, so no external reference is required.

Option Explicit

Public Function GetCoordinates(address As String) As String
    
    '-----------------------------------------------------------------------------------------------------
    'This function returns the latitude and longitude of a given address using the Google Geocoding API.
    'The function uses the "simplest" form of Google Geocoding API (sending only the address parameter),
    'so, optional parameters such as bounds, language, region and components are NOT used.
    'In case of multiple xmlDoc (for example two cities sharing the same name), the function
    'returns the FIRST OCCURRENCE, so be careful in the input address
    'Tip: use the city name and the postal code if they are available).
    
    'NOTE: As Google points out, the use of the Google Geocoding API is subject to a limit of 40,000
    'requests per month, so be careful not to exceed this limit. For more info check:
    'https://cloud.google.com/maps-platform/pricing/sheet
    
    '2018 Update: In order to use this function you will now need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    
    '2018 Update 2 (July): The EncodeURL function was added to avoid problems with special characters.
    'This is a common problem with addresses that are from Greece, Serbia, Germany and other countries.
    'Note that this function was introduced in Excel 2013, so it will NOT work in older versions.
    
    '2020 Update: The code was switched to late binding, so no external reference is required.
    
    'Written By:    Christos Samaras
    'Date:          12/06/2014
    'Last Updated:  16/02/2020
    'E-mail:        [email protected]
    'Site:          https://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables.
    Dim apiKey              As String
    Dim xmlhttpRequest      As Object
    Dim xmlDoc              As Object
    Dim xmlStatusNode       As Object
    Dim xmlLatitudeNode     As Object
    Dim xmLongitudeNode     As Object
       
    'Set your API key in this variable. Check this link for more info:
    'https://www.myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
    'Here is the ONLY place in the code where you have to put your API key.
    apiKey = "The API Key"
    
    'Check that an API key has been provided.
    If apiKey = vbNullString Or apiKey = "The API Key" Then
        GetCoordinates = "Empty or invalid API Key"
        Exit Function
    End If
    
    'Generic error handling.
    On Error GoTo errorHandler
            
    'Create the request object and check if it was created successfully.
    Set xmlhttpRequest = CreateObject("MSXML2.ServerXMLHTTP")
    If xmlhttpRequest Is Nothing Then
        GetCoordinates = "Cannot create the request object"
        Exit Function
    End If
        
    'Create the request based on Google Geocoding API. Parameters (from Google page):
    '- Address: The address that you want to geocode.
    
    'Note: The EncodeURL function was added to allow users from Greece, Poland, Germany, France and other countries
    'geocode address from their home countries without a problem. The particular function (EncodeURL),
    'returns a URL-encoded string without the special characters.
    'This function, however, was introduced in Excel 2013, so it will NOT work in older Excel versions.
    xmlhttpRequest.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
    & "&address=" & Application.EncodeURL(address) & "&key=" & apiKey, False
    
    'An alternative way, without the EncodeURL function, will be this:
    'xmlhttpRequest.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" & "&address=" & Address & "&key=" & ApiKey, False
    
    'Send the request to the Google server.
    xmlhttpRequest.send
    
    'Create the DOM document object and check if it was created successfully.
    Set xmlDoc = CreateObject("MSXML2.DOMDocument")
    If xmlDoc Is Nothing Then
        GetCoordinates = "Cannot create the DOM document object"
        Exit Function
    End If
    
    'Read the XML results from the request.
    xmlDoc.LoadXML xmlhttpRequest.responseText
    
    'Get the value from the status node.
    Set xmlStatusNode = xmlDoc.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case UCase(xmlStatusNode.Text)
    
        Case "OK"                       'The API request was successful.
                                        'At least one result was returned.
            
            'Get the latitude and longitude node values of the first result.
            Set xmlLatitudeNode = xmlDoc.SelectSingleNode("//result/geometry/location/lat")
            Set xmLongitudeNode = xmlDoc.SelectSingleNode("//result/geometry/location/lng")
            
            'Return the coordinates as a string (latitude, longitude).
            GetCoordinates = xmlLatitudeNode.Text & ", " & xmLongitudeNode.Text
        
        Case "ZERO_RESULTS"             'The geocode was successful but returned no results.
            GetCoordinates = "The address probably do not exist"
            
        Case "OVER_DAILY_LIMIT"         'Indicates any of the following:
                                        '- The API key is missing or invalid.
                                        '- Billing has not been enabled on your account.
                                        '- A self-imposed usage cap has been exceeded.
                                        '- The provided method of payment is no longer valid
                                        '  (for example, a credit card has expired).
            GetCoordinates = "Billing or payment problem"
            
        Case "OVER_QUERY_LIMIT"         'The requestor has exceeded the quota limit.
            GetCoordinates = "Quota limit exceeded"
            
        Case "REQUEST_DENIED"           'The API did not complete the request.
            GetCoordinates = "Server denied the request"
            
        Case "INVALID_REQUEST"           'The API request is empty or is malformed.
            GetCoordinates = "Request was empty or malformed"
        
        Case "UNKNOWN_ERROR"            'The request could not be processed due to a server error.
            GetCoordinates = "Unknown error"
        
        Case Else   'Just in case...
            GetCoordinates = "Error"
        
    End Select
        
    'Release the objects before exiting (or in case of error).
errorHandler:
    Set xmlStatusNode = Nothing
    Set xmlLatitudeNode = Nothing
    Set xmLongitudeNode = Nothing
    Set xmlDoc = Nothing
    Set xmlhttpRequest = Nothing
    
End Function

'------------------------------------------------------------------------------------------------------------------
'The next two functions use the GetCoordinates function to get the latitude and the longitude of a given address.
'------------------------------------------------------------------------------------------------------------------

Public Function GetLatitude(address As String) As Double
    
    'Declaring the necessary variable.
    Dim coordinates As String
    
    'Get the coordinates for the given address.
    coordinates = GetCoordinates(address)
    
    'Return the latitude as a number (double).
    If coordinates <> vbNullString Then
        GetLatitude = CDbl(Left(coordinates, WorksheetFunction.Find(",", coordinates) - 1))
    Else
        GetLatitude = 0
    End If

End Function

Public Function GetLongitude(address As String) As Double

    'Declaring the necessary variable.
    Dim coordinates As String
    
    'Get the coordinates for the given address.
    coordinates = GetCoordinates(address)
    
    'Return the longitude as a number (double).
    If coordinates <> vbNullString Then
        GetLongitude = CDbl(Right(coordinates, Len(coordinates) - WorksheetFunction.Find(",", coordinates)))
    Else
        GetLongitude = 0
    End If
    
End Function 

NOTE: The GetCoordinates function uses the “simplest” form of Google Geocoding API (sending only the address parameter), so optional parameters such as bounds, language, region, and components are NOT used. In the case of multiple results (for example, two cities sharing the same name), the function returns the first occurrence, so be careful in the input address you use. Tip: apart from the city name, use also the postal code, if it is available. Since I have no intention to copy the entire Google page, interested in learning how the Google Geocoding API works can visit the corresponding page.

WARNING: The code will NOT work on a Mac!!!

 


Test your geocoding API key


Since many people had trouble applying the API key, I decided to develop a small “validator.” You can check if your key can work with the above VBA functions. Paste your API key in the text box and press the button. After a few seconds, you will receive a response from the Google server.

Server Response:

Apart from OK, any other value that you will get (e.g., REQUEST_DENIED), it will automatically mean that either your key is invalid or you have not enabled the correct API (in this case, the Geocoding API). If this occurs, ensure that you followed exactly these instructions to get your API key.

 


Downloads


Download

The file can be opened with Excel 2007 or newer. Please enable macros before using the spreadsheet.

 


Read also


How To Get A Free Google API Key
Reverse Geocoding Using VBA & Google API
Custom Trip Distance Function (VBA & Google Directions API)
Custom Elevation Function (VBA & Google API)

Page last modified: June 11, 2021

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

Christos E. Samaras

  • Christos,
    Thanks for this.
    I’ve been using it for some time, but seemingly all of a sudden, it is not working.

    I can paste the geocode request into my browser and I get an OK full response.

    In excel, I’m not getting any response. No API key error, no Case errors. Just returns an empty cell.

    Thoughts?
    (I am using the 2020 code copied from your site).

  • Hi, Jay,

    As I wrote to Kevin below, try to create a new key and re-test the code.

    Best Regards,
    Christos

  • Hi, Kevin,

    Probably the best advice that I can give you is to create a new key.
    I created some keys 2+ years ago, and about a few months ago were no longer working.
    So, I ended up creating new ones.
    I think there was a policy change that affected existing API keys.

    Best Regards,
    Christos

  • Hello, thanks for your coding it has been very helpful. I used this without any problems most of last year but unfortunately I am now getting an #NAME? error on my GetLatitude and GetLongitude functions. My APIkey is valid. Any suggestions?

  • Kevin Bowie says:

    I keep getting “Request-denied” when checking my API. Google is telling me the Geocoding API is enabled. When trying to geocode in excel with your code and my API I get “#VALUE!” returning for latitude and longitude. On my google project dashboard I can see the traffic of my requests. About a year ago I used these steps and it worked fine. Now I’m having issues, and not sure what to do.

  • Hi, Lone,

    When you use the validator utility on this page, what response do you get?
    Do you get an OK or a REQUEST_DENIED?
    If it is the latter, then your API key is invalid.

    Best Regards,
    Christos

  • Lone Araki says:

    I have a error message “Server denied the request”
    what wrong with it and please advise

  • Hi, Manuel,

    A quick fix will be to replace this line:

    GetCoordinates = xmlLatitudeNode.Text & ", " & xmLongitudeNode.Text

    With this one:

    GetCoordinates = xmlLatitudeNode.Text / 100 & ", " & xmLongitudeNode.Text / 100

    I hope it helps!

    Best Regards,
    Christos

  • Thanks for the amazing work! It works like a charm but I have one problem: the latitude as well as longitude coordinates are not returned with a dot or comma but just as a long number. For example: The latitude of New York is “40.712784”, however, in my excel-sheet it appears as “40712784” without the dot. It also doesnt matter if I change the separator in the settings from dot to comma. I am located in Germany. Can someone pls help me?

    Thanks!

  • Thanks Christos, what if I want to display multiple locations from teh very excel sheet where I carry out the conversion of addresses to geo coordinates using your code? One example I saw online uses Google Maps JavaScrpt API. Here is the link:

    https://medium.com/@limichelle21/integrating-google-maps-api-for-multiple-locations-a4329517977a

    Do you think this is a god approach to have a button on excel where the user can click on it and Google Maps opens and displays multiple locations?

  • Hi, Aloisio,

    First of all, thank you very much for your kind words!
    It’s good to know that this code saved you some time.

    It’s also good to learn that some fellow-engineer is digging into programming.
    I think that the engineering background helps in software development too.
    In any case, good luck with your adventure in programming.

    Best Regards,
    Christos

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block
    >