copy

Thursday, 12 June 2014



Last updated: 05/04/2018, 1 min read (without the code)

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 the Google Geocoding API. The developed VBA function can be utilized directly from Excel, as long as the user provides a valid address. The so-called GetCoordinates function sends a Geocoding API request to the corresponding Google server and, then, uses the Geocoding response to read the appropriate XML nodes in order to extract the required information (latitude, longitude).

Based on GetCoordinates 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. Have in mind that the use of the Google Geocoding API is subject to a limit of 2500 requests per day, 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.

Option Explicit

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 results (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 2500
    'requests per day, so be careful not to exceed this limit. For more info check:
    'https://developers.google.com/maps/documentation/geocoding/usage-limits
        
    'In order to use this function you must enable the XML, v3.0 library from VBA editor:
    'Go to Tools -> References -> check the Microsoft XML, v3.0.
    
    '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
    
    'Written By:    Christos Samaras
    'Date:          12/06/2014
    'Last Updated:  24/03/2018
    'E-mail:        xristos.samaras@gmail.com
    'Site:          https://www.myengineeringworld.net
    '-----------------------------------------------------------------------------------------------------
    
    'Declaring the necessary variables. Using 30 at the first two variables because it
    'corresponds to the "Microsoft XML, v3.0" library in VBA (msxml3.dll).
    Dim ApiKey          As String
    Dim Request         As New XMLHTTP30
    Dim Results         As New DOMDocument30
    Dim StatusNode      As IXMLDOMNode
    Dim LatitudeNode    As IXMLDOMNode
    Dim LongitudeNode   As IXMLDOMNode
       
    '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
    ApiKey = "Your API Key goes here!"
    
    'Check that an API key has been provided.
    If ApiKey = vbNullString Or ApiKey = "Your API Key goes here!" Then
        GetCoordinates = "Invalid API Key"
        Exit Function
    End If
    
    'Generic error handling.
    On Error GoTo errorHandler
    
    'Create the request based on Google Geocoding API. Parameters (from Google page):
    '- Address: The address that you want to geocode.
    
    Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
    & "&address=" & Address & "&key=" & ApiKey, False
    
    'Send the request to the Google server.
    Request.send
    
    'Read the results from the request.
    Results.LoadXML Request.responseText
    
    'Get the status node value.
    Set StatusNode = Results.SelectSingleNode("//status")
    
    'Based on the status node result, proceed accordingly.
    Select Case UCase(StatusNode.Text)
    
        Case "OK"   'The API request was successful. At least one geocode was returned.
            
            'Get the latitdue and longitude node values of the first geocode.
            Set LatitudeNode = Results.SelectSingleNode("//result/geometry/location/lat")
            Set LongitudeNode = Results.SelectSingleNode("//result/geometry/location/lng")
            
            'Return the coordinates as string (latitude, longitude).
            GetCoordinates = LatitudeNode.Text & ", " & LongitudeNode.Text
        
        Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
            GetCoordinates = "The address probably not exists"
            
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
            GetCoordinates = "Requestor has exceeded the server limit"
            
        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"    'Indicates that the request could not be processed due to a server error.
            GetCoordinates = "Unknown error"
        
        Case Else   'Just in case...
            GetCoordinates = "Error"
        
    End Select
        
    'In case of error, release the objects.
errorHandler:
    Set StatusNode = Nothing
    Set LatitudeNode = Nothing
    Set LongitudeNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function

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

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 number (double).
    If Coordinates <> vbNullString Then
        GetLatitude = CDbl(Left(Coordinates, WorksheetFunction.Find(",", Coordinates) - 1))
    Else
        GetLatitude = 0
    End If

End Function

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 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 uses the "simplest" form of Google Geocoding API (sending only the address parameter), so, optional parameters such as bounds, key, language, region and components are NOT used. In 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: use the city name and the postal code if they are available. Since I have no intention to copy the entire Google page, anyone who is interested to learn how the Google Geocoding API works, he/she can visit the corresponding page.

Be careful with special characters: Some languages (Russian, Serbian, Danish, Greek, and others) contain special characters (e.g. ã, ø, æ, å, α, β, etc.). To use the above VBA functions correctly, simply remove these characters from the address you need to geocode. You can either use the FIND and REPLACE functionality of Excel, or you can incorporate the REPLACE or SUBSTITUTE functions when calling the VBA functions.
Example:
=GetCoordinates(SUBSTITUTE(SUBSTITUTE(Α2;"å";"a");"ø";"o")) 
Where cell A2 contains an address from Denmark.


Did you like this post? If yes, then share it with your friends. Thank you!


Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info