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: October 1, 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.

  • Hi, Panagiotis,

    It’s good to know that you solved your problem.
    Unfortunately, these kinds of firewall problems are difficult to detect.

    In general, another way to troubleshoot issues with this VBA code is to add this line:

    Debug.Print "https://maps.googleapis.com/maps/api/geocode/xml?" & "&address=" & Application.EncodeURL(address) & "&key=" & apiKey

    Just before these lines:

    'Send the request to the Google server.
    xmlhttpRequest.send

    In the Immediate window of the VBA editor, a URL will be generated.
    If you copy the URL on your browser, you will get either the requested XML results or an XML error.

    In your case (firewall issue), I bet that you will get an error.

    Best Regards,
    Christos

  • Hi Christos,

    thank you for your reply to my request and for testing the specific address.

    Eventually, I found the problem for not be able to use your code. I work for a big European Organisation and until now I was trying to run the code from a PC inside the organisation network. It seems that some kind of firewall was preventing the code to call the Google APIs. I managed to run your code when I run it before logging in the organisation.

    Once again, I want to thank you for sharing your code, I must say that it helped me enormously in a big project of European addresses visualisation that I am working currently.

    Best regards,
    Panagiotis

  • Hi, Panagiotis,

    When I tried the address in the sample workbook, I got:
    44.9075070, 8.6211240

    So, I would suggest downloading the sample file, put your API key there and try the address you need to search.

    Best Regards,
    Christos

  • Dear Mr Samaras,

    First of all I would like to thank you for offering your code.

    I want to use this code to find the the latitude and longitude of a given address in a format like: “Spalto Gamondio 3, Alessandria, Italy” which I know that has a Latitude = 44.907660 and Longitude = 8.620970

    I used the GetCoordinates function and all the lines in the code are executed until the line xmlhttpRequest.send
    which is not executed. The function GetCoordinates then exits without any returning value.

    I have a valid API Key which I have checked with you verification tool in this page.

    I have enabled in my Excel the Microsoft XML v6.0 (I tested as well with Microsoft XML v3.0)

    I have used the alternative command that you propose for the GET: ‘xmlhttpRequest.Open “GET”, “https://maps.googleapis.com/maps/api/geocode/xml?” & “&address=” & address & “&key=” & apiKey, False

    Do you have any idea why the code stops execution when reaching the command “xmlhttpRequest.send” ?

    Thank you very much,
    Panagiotis

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