How to Calculate a Distance in Excel Using Google Maps

Method 1 – Using a User-Defined Function

Here, we’ll find the distance between the cities of Las Vegas and Philadelphia using Google Maps.

Steps:

Select cells C4:C5.
Navigate to the Data tab and click Geography from the Data Types group.

Select cell C8 and insert the following formula:

=C4.Latitude &", "&C4.Longitude

Drag the Fill Handle icon to cell C9.

Enter the following API key in cell C11:

AoCgFc5qOKVpyHuiGyPBgzDk8RgQnGGMvNqwcmtxfj7VnHEm-bpqH2GkRpoSJSAD

Note

The API Key will become an invalid one If 3 months or 10K transactions are reached.

Press ALT + F11 to open the VBE window.
Click Insert > Module to create a new module.
Enter 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

Choose cell C13 and type the following formula:

=Calculate_Distance(C8,C9,C11)

Press ENTER to get the distance. It will show the distance in Miles.

Download Practice Workbook

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

Calculate Distance with Google Maps.xlsm

<< Go Back to Distance | Formula List | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

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

12 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.

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.

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

DeAnna Jun 15, 2023 at 11:04 AM

This did not work either … UGGGGG *****FRUSTRATION OVERLOAD****
- Lutfor Rahman Shimanto Jun 20, 2023 at 4:44 PM
  Hello DeAnna
  
  Thanks for reaching out. To overcome the situation, I will introduce another method. Here you must convert the locations into Geography data types. Later, you will find the Lat Long values for each location. Lastly, you have to call the below User-defined function in a cell.
  
  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
```
  API:
  
  AoCgFc5qOKVpyHuiGyPBgzDk8RgQnGGMvNqwcmtxfj7VnHEm-bpqH2GkRpoSJSAD
  
  STEPS:
  Select range B3:B4 >> go to Data tab >> click on Geography.
  
  Choose cell C3 >> apply the below formula >> drag the Fill Handle to C4.
  
  =B3.Latitude &”, “&B3.Longitude
  
  Press Alt+F11 >> go to Insert >> click on Module >> insert the mentioned code.
  
  Choose cell C9 >> apply the below formula.
  
  =GetTotalDistance2Locations(C3,C4,A7)
  
  This concept will assist you in reaching your goal. I’ve also attached the Solution Workbook to help you understand it better. Best wishes.
  
  Download Workbook
  
  Regards
  Lutfor Rahman Shimanto

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.
- Reply
  Lutfor Rahman Shimanto Jun 20, 2023 at 1:00 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.
  Download Workbook
  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.

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:

OUTPUT:

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

Marco Aug 23, 2023 at 4:35 PM

Hello!
I get #NAME? error. May you guys help me out? I’d like also to get distance in KM instead of miles.

Many thanks in advance.
- Reply
  MD Tanvir Rahman Aug 28, 2023 at 12:11 PM
  
  Dear MARCO, Thanks a ton, and my heartfelt gratitude to you.
  Query 1: #NAME? error
  Considering you are trying the code mentioned in the content. However, there are several reasons for getting #NAME? error in Excel.
  Issue 1: The code contains custom custom-created Public Function. So, when you download the file from our site, by default it it may be blocked by your local administration. Macro remains disabled in the blocked file. So, you must unblock the file by selecting File > Right-Click on Mouse > Properties > Check Unblock.
  Issue 2: Spelling mistake in the function name shows #NAME? error.
  Issue 3: Incorrect range and cell references also lead to #NAME? error.
  To learn more about #NAME? error, go through #NAME? error in Excel.
  
  Query 2: Convert Km instead of Miles
  The mentioned code returns the outcome in Miles. However, you can convert Miles into Kilometers by inserting the following formula.
  =(Calculate_Distance(C8,C9,C11))*1.61
  or,
  =CONVERT(Calculate_Distance(C8,C9,C11),”mi”,”km”)
  
  Thanks for reaching out. We team Exceldemy are here to assist you. Please let us know if you face any other shortcomings.
  
  Regards,
  MD Tanvir Rahman
  Excel and VBA Content Developer
  Exceldemy, Softeko.
Mx A Huskins Oct 2, 2023 at 11:41 PM

This works well, only thing I noted not mentioned is that you will probably need to set up your own Bing API code, since their provided one probably won’t work for you, which is easy and free to do.
- Reply
  Lutfor Rahman Shimanto Oct 3, 2023 at 11:45 AM
  
  Hello MX A HUSKINS
  
  Thank you for reaching out and posting your comment. You are right about setting up your own valid Bing API to work properly.
  
  The API Key will become an invalid one If 3 months or 10K transactions are reached. The API mentioned in this article may have reached 10 K transactions.
  
  Regards
  Lutfor Rahman Shimanto

How to Calculate a Distance in Excel Using Google Maps

Method 1 – Using a User-Defined Function

Leave a reply Cancel reply