Thursday, 22 August 2013

Last updated: 07/03/2018, 2 min read (without the code)

Custom Elevation Function (VBA & Google API)

Problem description

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.

Google Earth Elevation

Google Maps Find Altitude - Daft Logic

I found two possible “manual” ways that could be used: 
  1. Open Google Earth and search point by point in order to find the elevation. The elevation of the address will be displayed at the bottom right corner of the screen. Then, write each elevation value to an empty cell next to latitude and longitude data. 
  2. Use the following page: and again write each elevation value to Excel.
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.


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. Full description about all APIs is given there.
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 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 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 2500 requests per day, so be careful not to exceed this limit. For more info check:

    '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:

    'Written By:    Christos Samaras
    'Date:          22/08/2013
    'Last Updated:  07/03/2018
    '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:
    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):
    '- Sensor: specifies whether the application requesting elevation data is using
    'a sensor (such as a GPS device) to determine the user's location. Here is set to false.
    '- 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", "" _
    & "&locations=" & Latitude & "," & Longitude & "&sensor=false&key=" & ApiKey, False
    'Send the request to the Google server.
    '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, likely because
                                'the requestor failed to include a valid sensor parameter.
            GetElevation = "Invalid sensor parameter"
        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.
    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

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


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