Custom Elevation Function (VBA & Google API)

Share this

August 22, 2013

Last updated: 09/08/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. The full description of 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 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:

'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: 09/08/2018
'E-mail: [email protected]

'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).
'- 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 & "&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.
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.
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

Page last modified: January 6, 2019

Christos Samaras

Hi, I am Christos, a Mechanical Engineer by profession (Ph.D.) and a Software Developer by obsession (10+ years of experience)! I founded this site back in 2011 intending to provide solutions to various engineering and programming problems.

  • Hello Friend! Thanks for the feedback. I know it’s very busy, if you can help me, it will be very good for my work, because I just need to make the worksheet work to finish some work here. I just sent the spreadsheet with the API API to your email.
    Thank you!

  • Help Christos!
    Mine is showing invalid API, even putting my API key.
    You sent a copy of the spreadsheet with the key to your email. If you can take a look and indicate where I’m going wrong, it will help me a lot, because this tool has helped me a lot at work.
    I take the opportunity to congratulate you on your excellent work! Very good!

  • Thank you so much…You saved almost 1 day of my life by reducing my work.

  • {"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}
    Add Content Block