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.

**Table of Contents**hide

## Download Practice Workbook

You can download the Excel file from the following link and practice along with it.

## 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

**SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5))**– becomes 0.627884682513118 * 0.716476936499882.**Output**– 0.449864893802199

**COS(RADIANS(90-C6)) *COS(RADIANS(90-C5)) +SIN(RADIANS(90-C6)) *SIN(RADIANS(90-C5)) *COS(RADIANS(D6-D5))**– becomes 0.365377540842758 * 0.449864893802199.**Output**– 0.815242434644958

- 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

**Read More: ****How to Calculate Miles between Two Addresses in Excel (2 Methods)**

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

- To create a free
**Bing MAP API**,**click 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:
**startlocation**,**destination**, and**keyvalue.**These are the position of the two addresses and the**API**value respectively. - Then I used several variables such as
**First_Value**,**Second_Value**,**Last_value**,**mitHTTP**, &**mitUrl.**These variables are used to store different values. - Then combined the values (stored within
**mitUrl**) and used several object methods (**Open**,**SetRequestHeader**,**Send**). 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.

**Read More: ****How to Calculate Distance Between Two Addresses in Excel (3 Ways)**

## 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

**How to Calculate Distance Between Two Cities in Excel****Calculate Distance between Two GPS Coordinates in Excel****How to Calculate Distance in Excel with Google Maps****Calculate Coordinates from Bearing and Distance in Excel****How to Calculate Mahalanobis Distance in Excel (Step by Step)****Calculate Distance Between Two Coordinates in Excel (2 Methods)****How to Calculate Manhattan Distance in Excel (2 Suitable Ways)**

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

Hi BIANKA,

Thanks for your comment. I am replying to you on behalf of ExcelDemy. If you want to get the distance in

KILOMETERSthen you can use the followingVBAcode and the rest of the procedures will be the same.I hope this will help you to solve your problem. Please let us know if you have other queries.

Regards

Mashhura Jahan

ExcelDemy.

This was great. Thank you.

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

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:

Here, we renamed the function to

Driving_Time. And changed themitUrlvariable.Also, we modified the last line of the code to:

Here, we divided with

60to get the time inminutes. Otherwise, it’ll return the time inseconds.Hope, you find it helpful. Btw, are you Spanish? I like the spelling of your name. Thanks again.

Regards,

SHAHRIAR ABRAR RAFIDTeam

ExcelDemyHello, 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!

Thank you, Jeb, for your query. You can take the

APIas a String value to yourVBAcode. 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:Here we have changed the first two line of the given code. The previous code was as like:

But we have removed the third argument which represents the

APIvalue. Alternately, We declare the variable outside and put theAPIas a string value.Now here comes to your second question. You have to create a customized

Add-into store yourVBAcode 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.