Excel has a wide range of applications. And while using VBA then it seems that we can do whatever we want in Excel. So of course, we can find the distance between places using a map in Excel. In this article, I’ll show a quick guide to calculate the distance in Excel with Google Maps with sharp steps and clear illustrations.
How to Calculate Distance in Excel with Google Maps: Using a User-Defined Function
Here, we’ll find the distance between two cities such as Las Vegas and Philadelphia using Google Maps.
First, we need to know an important thing. To calculate the distance between two addresses in Excel using Google Maps, we will need an API key. API stands for Application Programming Interface. Excel connects with Google Maps using the API key for collecting required data. Some maps provide free API keys like Bing Maps. But Google Maps doesn’t provide free API. Although you manage a free API somehow, that may not work perfectly. So, you can buy an API key.
I have yet to mention that I have been using the Microsoft 365 version for this article. I have managed a free API key. It works properly. I just used to show it as an example. We’ll use VBA to create a user-defined function named Calculate_Distance to find the distance. It will have three arguments- startlocation, endlocation, and keyvalue. Now let’s start the procedures.
- Select range C4:C5.
- Navigate to the Data tab and click on Geography from the Data Types group.
- Choose C8 and insert the following formula.
=C4.Latitude &", "&C4.Longitude
- Later, drag the Fill Handle icon to C9.
- Next, input the following API key in C11.
- Press ALT + F11 to open the VBE window.
- Next, click Insert > Module to create a new module.
- Later, type the following codes in the VBE module and press Ctrl+S.
Public Function Calculate_Distance(startlocation As String, endlocation As String, keyvalue As String) As Double Dim Initial_Value As String, temp_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String Dim distance As Double Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins=" temp_Value = "&destinations=" Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi" Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP") mitUrl = Initial_Value & startlocation & temp_Value & endlocation & Destination_Value mitHTTP.Open "GET", mitUrl, False mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" mitHTTP.Send ("") distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0) Calculate_Distance = distance End Function
- The function declares several variables: Initial_Value, temp_Value, and Destination_Value are strings that store parts of the URL for the API request.
- The Open method of the mitHTTP object is called to initialize the HTTP request. The SetRequestHeader method sets the User-Agent header.
- The Send method is called to send the HTTP request. The request body is empty in this case.
- The response from the API is received and stored in the mitHTTP.ResponseText property.
- The WorksheetFunction.FilterXML function extracts the value of the TravelDistance element from the XML response. The distance is rounded to three decimal places using the Round function.
- The final distance value is rounded to the nearest whole number using the Round function again.
- The calculated distance is assigned to the Calculate_Distance function.
- Now you see, our function is ready to use. Choose cell C13 and type the following formula.
- Finally, press the ENTER button to get the distance. It will show the distance in the Miles units.
Pros and Cons While Calculating Distance with Google Maps
- You must have a valid API key.
- The above code will give the output in the Miles units.
- The user-defined function does not use place names directly. Here, it needs to use Lat and Long values.
- Make sure you have used a valid place.
Advantages and Disadvantages of Calculating Distance with Google Maps
- For a large couple of places, it is quite feasible because we can use the Fill Handle tool to copy the formula. That is not possible in Google Maps
- It’s a pretty faster way.
- It can only work with coordinates.
- You won’t get the map or route, just you will get the distance.
- It won’t work with the approximate match of the place names.
Download Practice Workbook
You can download the free Excel workbook from here and practice on your own.
I hope the procedures described above will be good enough to calculate the distance in Excel with Google Maps. Feel free to ask any questions in the comment section, and please give me feedback.