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://www.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://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 AddressNode 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 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.
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
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)
Yes, that would do the trick.
I need to re-write this code like the geocoding one, to take into account all the encoding problems.
Thank you, this combined with frankie man’s comment got me sorted.
Thank you, this combined with Christos’s comment got me sorted.
If GetAddress returns the value “Request was empty or malformed”, it may be because the longitude and latitude were passed as values with “,” instead of “.”, as in my case (German country code). This can be remedied by, for example:
Thanks Christos for great work.
Don’t use the button for “Copy Code”. Mark the text and copy manually.
If you download and try to use the sample file, does it work?
I just checked the file using a valid key, and it worked just fine.
A quick suggestion:
To use this function, you must enable the XML, v3.0 library from the VBA editor (ALT + F11 to switch on it):
Go to Tools -> References -> check the Microsoft XML, v3.0.
Unlike the geocoding code, this snippet needs a reference to work.
I haven’t updated it yet.
I’ve got your Geocoding script working a treat, but this doesn’t work for me – lots of red error text in the script (I can’t work out why, but then I’m not great at VBA!)