How to Calculate Distance in Excel with Google Maps

Get FREE Advanced Excel Exercises with Solutions!

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.

Table of Contents hide

Download Practice Workbook

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

Pros and Cons While Calculating Distance with Google Maps

Advantages and Disadvantages of Calculating Distance with Google Maps

Advantages

Disadvantages

Conclusion

Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.

Calculate-Distance-with-Google-Maps.xlsm

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

Here, we’ll find the distance between MacArthur Park and Jersey City using Google Maps.

First, we need to know an important thing. To calculate the distance 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 will not work perfectly. So, you will have to buy the API key from this link.

Here, I have managed a free API key. It doesn’t work properly, just used to show 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- Starting Place, Destination, and API key. Now let’s start the procedures.

Steps:

Press ALT + F11 to open the VBA window.

Next, click as follows: Insert > Module to create a new module.

Later, type the following codes in the window-

Public Function Calculate_Distance(start As String, dest As String, Alink As String) As Double
Dim first_Value As String, second_Value As String, last_Value As String
Dim mitHTTP As Object
first_Value = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
second_Value = "&destinations="
last_Value = "&mode=car&language=pl&sensor=false&key=" & Alink
Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = first_Value & Replace(start, " ", "+") & second_Value & Replace(dest, " ", "+") & last_Value
mitHTTP.Open "GET", Url, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
If InStr(mitHTTP.ResponseText, """distance"" : {") = 0 Then GoTo ErrorHandl
Set mit_reg = CreateObject("VBScript.RegExp"): mit_reg.Pattern = """value"".*?([0-9]+)": mit_reg.Global = False
Set mit_matches = mit_reg.Execute(mitHTTP.ResponseText)
tmp_Value = Replace(mit_matches(0).Submit_matches(0), ".", Application.International(xlListSeparator))
Calculate_Distance = CDbl(tmp_Value)
Exit Function
ErrorHandl:
Calculate_Distance = -1
End Function

Then nothing, just go back to your sheet.

Code Breakdown:

First, I used a Public Function procedure Calculate_Distance.
Then declared some variables first_Value, second_Value, and last_Value for the arguments of our user-defined function.
Set the values for the variables (each value is self-descriptive), and set the mitHTTP object in ServerXMLHTTP to utilize the GET method (used later, this object property will allow using the POST method as well).
Url is the combination of all the values set earlier, the open property of the mitHTTP object used it.
After assigning the values library function does the rest of the calculation.

Now you see, our function is ready to use.

In Cell C8, type the following formula-

=Calculate_Distance(C4,C5,C6)

Finally, just press the ENTER button to get the distance. It will show the distance in the Meter unit.

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 Meter unit.
The user-defined function uses place names directly, no need to use coordinates.
Make sure, you have used a valid place.

Advantages and Disadvantages of Calculating Distance with Google Maps

Advantages

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.
No need to use coordinates.

Disadvantages

It can’t 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.

Conclusion

I hope the procedures described above will be good enough to calculate the distance in Excel with Google Maps. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.

Public Function Calculate_Distance(start As String, dest As String, Alink As String) As Double Dim first_Value As String, second_Value As String, last_Value As String Dim mitHTTP As Object first_Value = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" second_Value = "&destinations=" last_Value = "&mode=car&language=pl&sensor=false&key=" & Alink Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP") Url = first_Value & Replace(start, " ", "+") & second_Value & Replace(dest, " ", "+") & last_Value mitHTTP.Open "GET", Url, False mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)" mitHTTP.Send ("") If InStr(mitHTTP.ResponseText, """distance"" : {") = 0 Then GoTo ErrorHandl Set mit_reg = CreateObject("VBScript.RegExp"): mit_reg.Pattern = """value"".*?([0-9]+)": mit_reg.Global = False Set mit_matches = mit_reg.Execute(mitHTTP.ResponseText) tmp_Value = Replace(mit_matches(0).Submit_matches(0), ".", Application.International(xlListSeparator)) Calculate_Distance = CDbl(tmp_Value) Exit Function ErrorHandl: Calculate_Distance = -1 End Function

Public Function GetTotalDistance2Locations(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) GetTotalDistance2Locations = distance End Function

6 Comments

Jan T Nov 30, 2022 at 3:25 AM

sorry but this can’t work. Your VBA code accepts only 2 arguments, and you use 3 arguments.
full of errors.
- Reply
  Tanjima Hossain Dec 1, 2022 at 12:52 PM
  Hello JAN T,
  Hope you are doing well. I think by following the below-stated procedures you can make your code work.
  • After going to your VBE window, go to the Tools tab >> References option.
  
  Then, the References – VBAProject window will appear.
  • Check the following options.
  o Microsoft Scripting Runtime
  o Microsoft WinHTTP Services, version 5.1
  • Press OK.
  
  • Now, type the following code.
```
 Public Function Calculate_Distance(start As String, dest As String, Alink As String) As Double
    Dim first_Value As String, second_Value As String, last_Value As String
    Dim mitHTTP As Object
    first_Value = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
    second_Value = "&destinations="
    last_Value = "&mode=car&language=pl&sensor=false&key=" & Alink
    Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
    Url = first_Value & Replace(start, " ", "+") & second_Value & Replace(dest, " ", "+") & last_Value
    mitHTTP.Open "GET", Url, False
    mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
    mitHTTP.Send ("")
    If InStr(mitHTTP.ResponseText, """distance"" : {") = 0 Then GoTo ErrorHandl
    Set mit_reg = CreateObject("VBScript.RegExp"): mit_reg.Pattern = """value"".*?([0-9]+)": mit_reg.Global = False
    Set mit_matches = mit_reg.Execute(mitHTTP.ResponseText)
    tmp_Value = Replace(mit_matches(0).Submit_matches(0), ".", Application.International(xlListSeparator))
    Calculate_Distance = CDbl(tmp_Value)
    Exit Function 
ErrorHandl:
    Calculate_Distance = -1
End Function  
```
  Make sure to add a third argument in your code and use it in the indicated place.
  • Finally, go to your sheet and use the following function.
  One thing to mention is that make sure to use a valid API address, otherwise, you will get an error.
  
  Regards
  Tanjima Hossain
D Kelly Jan 27, 2023 at 9:51 PM

I need code for three locations. For example the distance from your starting point to the 1st location and then the distance from the 1st location to the 2nd location. So that would be 3 different distance fields to be included in the total distance calculation. Can you show an example of that please? Thank you so much.
Lutfor Rahman Shimanto Jan 31, 2023 at 1:02 PM

Hi D KELLY,
Thanks for reaching out! I’d be happy to help you with your code and provide a solution. To solve this issue, we built a procedure called GetLatLong and a user-defined function named GetTotalDistance3Locations using VBA. You can input your desired locations in the GetLatLong procedure. The model calculates the total distance for three locations. Please click the link underneath this section to get a copy of the illustration workbook.
https://www.exceldemy.com/wp-content/uploads/2023/01/Total-Distance-Calculation-for-Three-Locations.xlsm
Best regards,
Lutfor Rahman Shimanto
(ExcelDemy Team)
Samir May 16, 2023 at 12:26 AM

Does it still work in 2023? I made an API but it doesnt work.
- Reply
  Lutfor Rahman Shimanto May 16, 2023 at 1:46 PM
  Hello SAMIR
  
  Thanks for reaching out and posting your query. Regarding your question, I can assist you with a User-defined function programmed in Excel VBA that will take three arguments. Among these arguments, the first and second will be the Latitude and Longitude of the start and end location. And the third argument must be an API Key. I am giving you an API for demonstration which should work. However, you may use your API as well as the third argument. I am attaching the Workbook used to investigate the described issue.
  
  Excel VBA Code:
```
Public Function GetTotalDistance2Locations(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)
    
    GetTotalDistance2Locations = distance

End Function
```
  OUTPUT:
  
  WORKBOOK:
  
  SOLUTION WORKBOOK
  
  The User-defined function and API will meet your requirement. Don’t hesitate to contact us again with any other inquiries or concerns.
  
  Regards
  Lutfor Rahman Shimanto

How to Calculate Distance in Excel with Google Maps

Download Practice Workbook

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

Pros and Cons While Calculating Distance with Google Maps

Advantages and Disadvantages of Calculating Distance with Google Maps

Advantages

Disadvantages

Conclusion

Related Articles

Md. Sourov Hossain Mithun

How to Create a Database in Excel with Pictures (with Easy Steps)

How to Format Slicer in Excel (7 Common Options)

How to Create Timeline Slicer with Date Range in Excel

How to Use Excel UserForm as Date Picker (with Easy Steps)

Leave a reply Cancel reply