Reverse Geocoding Using VBA & Google API

Introduction

After updating the existing VBA functions that dealt with Google APIs, it is time to introduce a new function that can be used for Reverse Geocoding, a short definition of which, based on Wikipedia, is the following:

“Reverse geocoding is the process of back (reverse) coding of a point location (latitude, longitude) to a readable address or place name. This permits the identification of nearby street addresses, places, and/or areal subdivisions such as neighborhoods, county, state, or country”.

The developed VBA function can be utilized directly from Excel, as long as the user provides a valid pair of latitude and longitude. The GetAddress function sends a (reverse geocoding) request to the Google server, and, then, uses its XML response to read the appropriate information (the formatted address in particular). If you are curious how the server response looks like, see the picture below, where the main nodes are highlighted in red boxes. This function does the exact opposite of the GetCoordinates function that was written a few years ago.

Apart from latitude and longitude, the function incorporates an optional third parameter (ResultTypeFilter). This parameter acts as a filter, by reducing/filtering the results returned from the server. In the code comments, you will find the values that are supported.

VBA code for reverse geocoding

Below you will find the VBA code for the GetAddress function. Bear in mind that 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. 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 GetAddress(Latitude As Double, Longitude As Double, Optional ResultTypeFilter As String) As String
 
    '-----------------------------------------------------------------------------------------------------------------------
    'This function returns the address of a given latitude, longitude pair using the Google (Reverse) Geocoding API.
 
    'The optional paramter ResultTypeFilter is a filter of one or more address types, separated by a pipe (|).
    'If the parameter contains multiple address types, the API returns all addresses that match any of the types.
    'A note about processing: The result_type parameter does not restrict the search to the specified address type(s).
    'Rather, the result_type acts as a post-search filter: the API fetches all results for the specified latlng,
    'then discards those results that do not match the specified address type(s).
 
    'The following values are supported:
    'street_address: indicates a precise street address.
    'route: indicates a named route (such as "US 101").
    'intersection: indicates a major intersection, usually of two major roads.
    'political: indicates a political entity. Usually, this type indicates a polygon of some civil administration.
    'country: indicates the national political entity, and is typically the highest order type returned by the Geocoder.
    'administrative_area_level_1: indicates a first-order civil entity below the country level. Within the United States,
        'these administrative levels are states. Not all nations exhibit these administrative levels.
        'In most cases, administrative_area_level_1 short names will closely match ISO 3166-2 subdivisions and other
        'widely circulated lists; however this is not guaranteed as our geocoding results are based on a variety of
        'signals and location data.
    'administrative_area_level_2: indicates a second-order civil entity below the country level. Within the United States,
        'these administrative levels are counties. Not all nations exhibit these administrative levels.
    'administrative_area_level_3: indicates a third-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_4: indicates a fourth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'administrative_area_level_5: indicates a fifth-order civil entity below the country level.
        'This type indicates a minor civil division. Not all nations exhibit these administrative levels.
    'colloquial_area" indicates a commonly-used alternative name for the entity.
    'locality: indicates an incorporated city or town political entity.
    'ward: indicates a specific type of Japanese locality, to facilitate distinction between multiple
        'locality components within a Japanese address.
    'sublocality: indicates a first-order civil entity below a locality. For some locations may receive one of the
        'additional types: sublocality_level_1 to sublocality_level_5. Each sublocality level is a civil entity.
        'Larger numbers indicate a smaller geographic area.
    'neighborhood: indicates a named neighborhood
    'premise: indicates a named location, usually a building or collection of buildings with a common name
    'subpremise: indicates a first-order entity below a named location, usually a singular building within
        'a collection of buildings with a common name
    'postal_code: indicates a postal code as used to address postal mail within the country.
    'natural_feature: indicates a prominent natural feature.
    'airport: indicates an airport.
    'park: indicates a named park.
    'point_of_interest: indicates a named point of interest. Typically, these "POI"s are prominent local entities that
        'don't easily fit in another category, such as "Empire State Building" or "Statue of Liberty."
 
    'The function ignores parameters such as language and location_type and returns always the FIRST RESULT.
 
    '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
 
    '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.
 
    'Moreover, to use this function you will also need a valid API key.
    'Check the next link that guides you on how to acquire a free API key:
    'https://myengineeringworld.net/2018/02/how-to-get-free-google-api-key.html
 
    'Written By:    Christos Samaras
    'Date:          17/03/2018
    'Last Updated:  09/08/2018
    'E-mail:        [email protected]
    'Site:          https://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 AddressNode     As IXMLDOMNode
 
    'Set your API key in this variable. Check this link for more info:
    'https://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
        GetAddress = "Invalid API Key"
        Exit Function
    End If
 
    'Check the input variables:
    'The valid range of latitude in degrees is -90 and +90 for the Southern and Northern hemisphere respectively.
    If Latitude < -90 Or Latitude > 90 Then
        GetAddress = "Invalid Latitude value"
        Exit Function
    End If
 
    'Longitude is in the range -180 and +180 specifying coordinates West and East of the Prime Meridian, respectively.
    If Longitude < -180 Or Longitude > 180 Then
        GetAddress = "Invalid Longitude value"
        Exit Function
    End If
 
    'Generic error handling.
    On Error GoTo errorHandler
 
    'Create the request based on Google's (Reverse) Geocoding API. Parameters:
    '- latlng: The latitude and longitude values specifying the location for which you wish to obtain the closest, human-readable address.
    '- key: Your application's API key. This key identifies your application for purposes of quota management.
    'Differentiate the request if a filter is provided.
    If ResultTypeFilter = vbNullString Then
        Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
                            & "latlng=" & Latitude & "," & Longitude & "&key=" & ApiKey, False
    Else
        Request.Open "GET", "https://maps.googleapis.com/maps/api/geocode/xml?" _
                            & "latlng=" & Latitude & "," & Longitude & "&result_type=" & ResultTypeFilter & "&key=" & ApiKey, False
    End If
    '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 result was returned.
 
            'Get the formatted address of the first result.
            Set AddressNode = Results.SelectSingleNode("//result/formatted_address")
 
            'Return the address.
            GetAddress = AddressNode.Text
 
        Case "ZERO_RESULTS"   'The geocode was successful but returned no results.
            GetAddress = "The address probably not exists"
 
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded the limit of 2500 request/day.
            GetAddress = "Requestor has exceeded the server limit"
 
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetAddress = "Server denied the request"
 
        Case "INVALID_REQUEST"  'The API request is empty or is malformed.
            GetAddress = "Request was empty or malformed"
 
        Case "UNKNOWN_ERROR"    'Indicates that the request could not be processed due to a server error.
            GetAddress = "Unknown error"
 
        Case Else   'Just in case...
            GetAddress = "Error"
 
    End Select
 
    'Release the objects.
errorHandler:
    Set AddressNode = Nothing
    Set StatusNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
 
End Function 

NOTE: In case of multiple results, the function returns the first result, so be careful with your inputs. Tip: use the ResultTypeFilter parameter to reduce the results that are returned. For anyone interested to learn how the Google (Reverse) Geocoding API works, he/she can visit the corresponding page.

Downloads

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

Read also 

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