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.


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.

Starting Place and Destination

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.

Steps:

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

Geography Data Types

  • Choose C8 and insert the following formula.

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

  • Later, drag the Fill Handle icon to C9.

Calculate Lat Long

  • Next, input the following API key in C11.

AoCgFc5qOKVpyHuiGyPBgzDk8RgQnGGMvNqwcmtxfj7VnHEm-bpqH2GkRpoSJSAD

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

Free API Key

  • 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

VBE Module

Code Breakdown
  • 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.

=Calculate_Distance(C8,C9,C11)

  • Finally, press the ENTER button to get the distance. It will show the distance in the Miles units.

Calculate Distance in miles


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

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.

Disadvantages

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


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 questions in the comment section, and please give me feedback.


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

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Md. Sourov Hossain Mithun
Md. Sourov Hossain Mithun

Md. Sourov Hossain Mithun, an Excel and VBA Content Developer at Softeko's ExcelDemy project, joined in October 2021. Holding a Naval Architecture & Marine Engineering degree from BUET, Mithun showcases expertise during his two-year tenure. With over 200 published articles on Excel topics, he earned a promotion to Team Leader, excelling in leading diverse teams. Mithun's passion extends to Advanced Excel, Excel VBA, Data Analysis, and Python programming, contributing significantly to the innovative and dynamic environment of ExcelDemy... Read Full Bio

12 Comments
  1. sorry but this can’t work. Your VBA code accepts only 2 arguments, and you use 3 arguments.
    full of errors.

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

      4

      Then, the References – VBAProject window will appear.
      • Check the following options.
      o Microsoft Scripting Runtime
      o Microsoft WinHTTP Services, version 5.1
      • Press OK.

      1

      • 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  

      2

      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.

      3

      Regards
      Tanjima Hossain

      • This did not work either … UGGGGG *****FRUSTRATION OVERLOAD****

        • Lutfor Rahman Shimanto
          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.

          Geography Data Types

          Choose cell C3 >> apply the below formula >> drag the Fill Handle to C4.

          =B3.Latitude &”, “&B3.Longitude

          Lat Long Values

          Press Alt+F11 >> go to Insert >> click on Module >> insert the mentioned code.

          VBE Module

          Choose cell C9 >> apply the below formula.

          =GetTotalDistance2Locations(C3,C4,A7)

          Calculate Distance

          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

  2. 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
      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)

  3. Does it still work in 2023? I made an API but it doesnt work.

    • Reply Lutfor Rahman Shimanto
      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:

      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

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

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo