Custom Elevation Function (VBA & Google API)

About a month ago I faced the following problem: I had around 100 coordinates – in format “latitude, longitude” – and I had to find the elevation of each point in order to proceed with some calculations. The input data came from a GPS, but the question was how to use those data in order to find the required elevation data.

I found two possible “manual” ways that could be used: 

Although both ways worked well, the problem was that I had to find too many points (i.e. 100) in order to process them manually. That’s why I tried to find something easier.

---

Solution

---

VBA saved me once again from spending a lot of time on this tedious issue. The solution I found is a custom VBA function that uses the Google Elevation API. What is this API? Copying from Google’s page: “The Elevation API provides elevation data for all locations on the surface of the earth, including depth locations on the ocean floor (which return negative values). In those cases where Google does not possess exact elevation measurements at the precise location you request, the service will interpolate and return an averaged value using the four nearest locations. With the Elevation API, you can develop hiking and biking applications, mobile positioning applications, or low-resolution surveying applications”.

I will not copy the entire Google page here, so for anyone who is interested to learn more about Google APIs he/she can find more information on the pages below. The full description of all APIs is given there.

• https://developers.google.com/maps/documentation/
• https://developers.google.com/maps/documentation/elevation/start

One of the two possible output formats from the Google Elevation API is “/xml”, which returns results in XML, wrapped within an <ElevationResponse> node. The other is a “/json”, which returns results in JavaScript Object Notation (JSON). I decided to go with the first format, so the tricky part about the VBA function was the use of XML, v3.0 library in the code.

---

VBA code

---

Here is the VBA code for the custom Elevation function. Have in mind that the use of the Google Elevation 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.

Option Explicit

Function GetElevation(Latitude As Double, Longitude As Double) As Variant
    
    '-----------------------------------------------------------------------------------------
    'The function returns the elevation from a given location using the Google Elevation API.
    
    'NOTE: As Google points out, the use of the Google Elevation 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.
    
    '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://myengineeringworld.net//////2018/02/how-to-get-free-google-api-key.html

    'Written By:    Christos Samaras
    'Date:          22/08/2013
    '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 ElevationNode   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
        GetElevation = "Invalid API Key"
        Exit Function
    End If
    
    'Checking if the latitude value is within the acceptable limits [-90, 90].
    If Latitude < -90 Or Latitude > 90 Then
        GetElevation = "Incorrect latitude value"
        Exit Function
    End If
    
    'Checking if the longitude value is within the acceptable limits [-180, 180].
    If Longitude < -180 Or Longitude > 180 Then
        GetElevation = "Incorrect longitude value"
        Exit Function
    End If
        
    'Generic error handling.
    On Error GoTo errorHandler
    
    'Create the request based on Google Elevation API. Parameters (from Google page).
    '- Locations: defines the location(s) on the earth from which to return elevation data.
    'This parameter takes either a single location as a comma-separated {latitude,longitude} pair
    '(e.g. "40.714728,-73.998672") or multiple latitude/longitude pairs passed as an array or as an encoded polyline.
    'Here we use it with a "single location" format.
    'Since we don't require a path, (but a single location) the parameter "path" is omitted here.
    
    Request.Open "GET", "https://maps.googleapis.com/maps/api/elevation/xml?" _
    & "&locations=" & Latitude & "," & Longitude & "&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 StatusNode.Text
            
        Case "OK"   'The API request was successful.
            'Get the elevation node value.
            Set ElevationNode = Results.SelectSingleNode("//result/elevation")
            GetElevation = CDbl(ElevationNode.Text)
    
        Case "INVALID_REQUEST"  'The API request was malformed.
            GetElevation = "Request was malformed"
        
        Case "OVER_QUERY_LIMIT" 'The requestor has exceeded limit.
            GetElevation = "Requestor has exceeded limit"
            
        Case "REQUEST_DENIED"   'The API did not complete the request.
            GetElevation = "Request was denied"
        
        Case "UNKNOWN_ERROR"    'An unknown error occured.
            GetElevation = "Unknown error"
        
        Case Else   'Just in case...
            GetElevation = "Error"
        
    End Select
        
    'In case of error, release the objects.
errorHandler:
    Set StatusNode = Nothing
    Set ElevationNode = Nothing
    Set Results = Nothing
    Set Request = Nothing
    
End Function

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

---

Read also

---