# How to Calculate Driving Distance between Two Addresses in Excel

Microsoft Excel is a very versatile spreadsheet program. It offers to do such a wide range of tasks that you can’t even imagine. You can even calculate the driving distance between two addresses in Excel. If you have a list of addresses to find the difference between them, you can of course use MS Excel. You can also calculate the distance manually. But that will be too time-consuming. As you have hundreds of thousands of distances to calculate. Thus in this article, I will show you how to calculate the driving distance between two addresses in Excel.

## 2 Effective Ways to Calculate Driving Distance between Two Addresses in Excel

### 1. Using Trigonometric Functions to Calculate Driving Distance

Here, I will show you to combine different trigonometric functions to calculate the driving distance between two addresses in Excel.

To give you an example, I’ve taken two addresses. The first address is MacArthur Park, Camden NSW, Australia. Its latitude and longitude are 34.06312149 and -118.2783975 respectively. The second address is Jersey City, New Jersey, USA. Its latitude and longitude are 40.71799929 and -74.04276812 respectively. Now, I will combine the ACOS, COS, SIN, & RADIANS functions to create a formula. The formula will effectively calculate the driving distance between two addresses in miles.

For that,

❶ Select cell D8 first.

❷ Then insert the following formula in the cell.

`=ACOS(COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) * COS(RADIANS(D6-D5))) *3959`

❸ After that, press the ENTER button.

Now, you will see that the formula has calculated the driving distance between the MacArthur Park, Camden NSW, Australia, and Jersey City, New Jersey, USA in miles. Thus, you will see the result in cell D8 which is 2445.270922 miles. Formula Breakdown

• COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) – the RADIANS functions convert the values into radians and the COS function provides the cosine of the values, the cosines for latitude are multiplied then. Output – 0.365377540842758
• COS(RADIANS(D6-D5)) – provides the cosine value for the longitude difference between the two addresses. Output – 0.716476936499882
• SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) – calculates the diversion of longitudes from 90 radians and multiplied the sine values. Output – 0.627884682513118
• Then the ACOS function arccosines the value. Output – 0.617648629071256
• Finally, multiplying the value by 3959 – 0.617648629071256 *3959 provides the result in miles. Output – 2445.270922

### 2. Calculate Driving Distance between Two Addresses Using VBA Code

In this section, I will use a VBA code to create a user-defined function. Then I will use that function to calculate the driving distance between two addresses in Excel.

Here, I’m using two addresses. The first address is MacArthur Park, Camden NSW, Australia. Its latitude and longitude are 34.06312149 and -118.2783975 respectively. The second address is Jersey City, New Jersey, USA. Its latitude and longitude are 40.71799929 and -74.04276812 respectively.

At the I will generate coordinates for each of the addresses. A coordinate is a combination of latitude and longitude. To generate a coordinate,

• Type the latitude of an address first.
• Then insert a comma.
• After that type the longitude of the same address.

So the coordinate of the first address is 34.0631214903094,-118.27839753751. And the coordinate of the second address is 40.7179992930381,-74.0427681204225. The VBA code requires an API of a map to calculate the driving distance. The API stands for Application Programming Interface. You can use an API to connect either the Google Map or the Bing Map whatever you prefer.
But creating the Google Map API is paid. On the contrary, you can create an API of the Bing MAP for free of cost.

Thus, I’m using the Bing MAP API here.

I’ve created an API. I’m attaching the API below:

`AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ-VrzOWptdUwsvj9D3L9F` Now, it’s time to write the VBA code. For that,

• Press ALT + F11 to open the VBA Editor.
• Now go to Insert Module to open a new module. After opening the VBA Editor, insert the following VBA code in the opened module.

``````Option Explicit

Public Function Driving_Distance(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)

End Function`````` Code Breakdown

• Here, I’ve created a user-defined function called Driving_Distance.
• Then I used 3 parameters: startlocationdestination, and keyvalue. These are the position of the two addresses and the API value respectively.
• Then I used several variables such as First_ValueSecond_ValueLast_valuemitHTTP, & mitUrl. These variables are used to store different values.
• Then combined the values (stored within mitUrl) and used several object methods (OpenSetRequestHeaderSend). This is how I managed to calculate the driving distance through the API.

This VBA code generates a user-defined function called Driving_Distance.

The function Driving_Distance requires 3 arguments in total.

Here’s the generic syntax of the Driving_Distance function.

`=Driving_Distance(Start_Location_Coordinate,End_Location_Coordinate, API)`

Now, it’s time to apply the user-defined function. For that,

• Select cell E10.
• Then insert the following formula:
`=Driving_Distance(E5,E6,C8)`
• Now, press ENTER.

Formula Breakdown

• E5 is the Start_Location_Coordinate.
• E6 is the End_Location_Coordinate.
• C8 is the API of the Bing MAP. The function calculates the driving distance between two addresses in miles. Check out cell E10. It will see the number, 2790.

So the driving distance between MacArthur Park, Camden NSW, Australia, and Jersey City, New Jersey, USA is 2790 miles. ## Practice Section

You will get an Excel sheet like the following screenshot, at the end of the provided Excel file where you can practice all the methods discussed in this article. ## Conclusion

To sum up, I have discussed 2 ways to calculate the driving distance between two addresses in Excel. And don’t hesitate to ask any questions in the comment section below. We will try to respond to all the relevant queries asap. And please visit our website Exceldemy to explore more.

## Related Articles #### Mrinmoy Roy

Hi! I'm Mrinmoy Roy. I'm an Excel and VBA content developer. I write blogs relating to Microsoft Excel on Exceldemy.com. I've completed my graduation in Electronics and Communication Engineering from Khulna University of Engineering & Technology. I've expertise in Excel functions, formulas, Pivot Table, Power Query, Visual Basic, etc. I write blogs to lessen people's hassles while working on Microsoft Excel.

1. Reply Bianka Balint Feb 9, 2023 at 1:54 PM

Hi,

I am interested in the second solution: Calculate Driving Distance between Two Addresses Using VBA Code, but i would need to know the distance in KILOMETERS and using a bing map API. Could you help me with that perhaps?

Thanks,
Bianka

• Reply Mashhura Jahan Feb 16, 2023 at 5:55 PM

Hi BIANKA,
Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in KILOMETERS then you can use the following VBA code and the rest of the procedures will be the same.

``````Option Explicit

Public Function Driving_Distance(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3) * 1.60934, 0)

End Function``````

Regards
Mashhura Jahan
ExcelDemy.

2. Reply This was great. Thank you.

Do you know if there is a way to get driving time?

• Reply Shahriar Abrar Rafid Mar 1, 2023 at 5:27 PM

Hi JORGE,
You asked for an interesting extension of this documentation. It’s all the same. Just have to make some modifications. Follow the code below:

``````Option Explicit

Public Function Driving_Time(startlocation As String, destination As String, keyvalue As String)

Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value & "&routeAttributes=routeDuration"

mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")

Driving_Time = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDuration"), 3) / 60, 0)

End Function``````

Here, we renamed the function to Driving_Time. And changed the mitUrl variable.

``mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value & "&routeAttributes=routeDuration"``

Also, we modified the last line of the code to:

``Driving_Time = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDuration"), 3) / 60, 0)``

Here, we divided with 60 to get the time in minutes. Otherwise, it’ll return the time in seconds.
Hope, you find it helpful. Btw, are you Spanish? I like the spelling of your name. Thanks again.

Regards,
SHAHRIAR ABRAR RAFID
Team ExcelDemy

3. Reply Hello, very interesting article. Thanks for taking the time to put this up. I have a couple of quick questions. How can one build the API Key into the VBA code so that you do not have to put it into a cell in Excel? Also, where can I put the VBA code so that it is available to any workbook I create?

Thanks!

• Reply Mohammad Shah Miran Mar 19, 2023 at 3:03 PM

Thank you, Jeb, for your query. You can take the API as a String value to your VBA code. Thus you don’t need to put the API value in your Excel sheet. So the existing code given in the workbook here can be modified as follows:

`````` Option Explicit
Public Function Driving_Distance(startlocation As String, destination As String)
Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String, keyvalue As String

First_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Last_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"

Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")

mitUrl = First_Value & startlocation & Second_Value & destination & Last_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
Driving_Distance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)
End Function``````

Here we have changed the first two line of the given code. The previous code was as like:

`````` Public Function Driving_Time(startlocation As String, destination As String, keyvalue As String)
Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String ``````

But we have removed the third argument which represents the API value. Alternately, We declare the variable outside and put the API as a string value.

`````` Public Function Driving_Distance(startlocation As String, destination As String)
Dim First_Value As String, Second_Value As String, Last_Value As String, mitHTTP As Object, mitUrl As String, keyvalue As String

keyvalue = "AhFG0hk5nKCcQlk80MRaSk1ZtoYUYsX98BCLWi7p7MKZ - VrzOWptdUwsvj9D3L9F" ``````

Now here comes to your second question. You have to create a customized Add-in to store your VBA code and therefore allow you to execute the code in any workbook. Hope you get your answer. However, if you have any further query, please let me know.  5 Excel Hacks You Never Knew  