# Excel VBA: Calculate Distance Between Two Addresses or Coordinates

Get FREE Advanced Excel Exercises with Solutions!

While working with Microsoft Excel, sometimes we need to calculate the distance between two addresses or coordinates for the convenience of our work. We may calculate the distance between two addresses or coordinates in different formats. Such as Miles, kilometers, Nautical miles, Light year, etc. We can also calculate the distance between two addresses or coordinates using the VBA macros in Excel. This is an easy and time-saving task also. Today, in this article, weâ€™ll learn three quick and suitable steps to calculate the distance between two addresses or coordinates in Excel VBA effectively with appropriate illustrations.

Excel VBA to Calculate Distance Between Two Addresses or Coordinates (Quick View)

``````Option Explicit
Public Function GetDistance(startlocation As String, destination As String, keyvalue As String)
Dim Initial_Value As String, Second_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String
Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"
Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
mitUrl = Initial_Value & startlocation & Second_Value & destination & Destination_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)
End Function``````

## How to Calculate Distance Between Two Addresses or Coordinates in Excel VBA: 3 Quick Steps

Letâ€™s say, we have a dataset that contains information about the latitude and longitude of Manhattan and Los Angeles. We will create an API link and then use those latitudes and longitudes in the VBA macros to calculate the distance between two addresses or coordinates. Hereâ€™s an overview of the dataset for todayâ€™s task.

Letâ€™s follow the instructions below to calculate the distance between two addresses or coordinates using VBA macros!

### Step 1: Create API Key from Map

API stands for Application Programming Interface. Excel uses an API to connect with any map like Google Map or Bing Map to collect data for a place. So, weâ€™ll have to create an API keyÂ first to apply this method. Thatâ€™s quite easy but unfortunately, Google doesnâ€™t provide a free API, only Bing Map provides a free API. So Iâ€™ll show you how to calculate the distance between two addresses or coordinates using the free API key from Bing Map. Then weâ€™ll make a user-defined function using VBA to use the coordinates and API key. Letâ€™s follow the instructions below to create an API in Bing Map!

• First of all, create an account in Bing Maps if you do not have that.
• After that, from the My Account drop-down list, go to,

My accountÂ  â†’ My Keys

• As a result, a My Keys dialog box will appear in front of you. From the My Keys dialog box, firstly, type distance in the Application name typing box. Secondly, select Basic from the Key type drop-down list. Thirdly, select Dev/Test from the Application Type drop-down list. At last, press the Create option.

• After that, you will be able to create an API in the Bing maps which has been given in the below screenshot.

### Step 2: Open Visual Basic Window to Write VBA Code

After creating an API in Bing Maps, we will enable VBA macros to calculate the distance between two addresses or coordinates. Letâ€™s follow the instructions below to calculate the distance between two addresses or coordinates!

• First of all, open a Module, to do that, firstly, from your Developer tab, go to,

Developer â†’ Visual Basic

• After clicking on the Visual Basic ribbon, a window named Microsoft Visual Basic for Applications â€“ Calculation of Distance between Two addresses or Coordinates will instantly appear in front of you. From that window, we will insert a module for applying our VBA code. To do that, go to,

Insert â†’ Module

### Step 3: Run VBA Code with API to Calculate Distance Between Two Addresses or Coordinates

In this step, we will create a User Defined Function using VBA macros. Letâ€™s follow the instructions below to learn!

• Hence, the Calculation of Distance between Two addresses or Coordinates module pops up. In the Calculation of Distance between Two addresses or Coordinates module, write down the below VBA code.
``````Option Explicit
Public Function GetDistance(startlocation As String, destination As String, keyvalue As String)
Dim Initial_Value As String, Second_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String
Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"
Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
mitUrl = Initial_Value & startlocation & Second_Value & destination & Destination_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)
End Function``````

Code Explanation:

• First, we named our Function as GetDistance. We also inserted 3 parameters: Initial_Value As String, Second_Value As String, Destination_Value As String.
• After that, we declared startlocation, Destination, keyvalue, and Outout_Url as String; mitHTTP as Object.
``````Public Function GetDistance(startlocation As String, destination As String, keyvalue As String)
Dim Initial_Value As String, Second_Value As String, Destination_Value As String, mitHTTP As Object, mitUrl As String``````
• Later, we set Initial_Value as the starting of the Url link, Second_Value as Destination, and Destination_Value to Miles.
``````Initial_Value = "https://dev.virtualearth.net/REST/v1/Routes/DistanceMatrix?origins="
Second_Value = "&destinations="
Destination_Value = "&travelMode=driving&o=xml&key=" & keyvalue & "&distanceUnit=mi"``````
• Hence, we set the necessary parameters to create a relation between our VBA code and the API.
• Finally, we established our User Defined Function. Our User Defined Function is GetDistance.
``````   Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
mitUrl = Initial_Value & startlocation & Second_Value & destination & Destination_Value
mitHTTP.Open "GET", mitUrl, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
GetDistance = Round(Round(WorksheetFunction.FilterXML(mitHTTP.ResponseText, "//TravelDistance"), 3), 0)``````

• Hence, run the VBA To do that, go to,

Run â†’ Run Sub/UserForm

• After running the VBA code, you will be able to create a User Defined Function. The User Defined Function is GetDistance.
• Now, we will calculate the distance between two cities such asÂ  Manhattan and Los Angeles using the User Defined Function named GetDistance.
• To do that, select cell E10 and write down the below User Defined Function.
`=GetDistance(E5,E6,C8)`

## Things to Remember

ðŸ‘‰ You can also calculate the distance between two addresses or coordinates in Kilometers. To do that, multiply the Distance in Miles by 1.61.

ðŸ‘‰ You can also pop up Microsoft Visual Basic for Applications window by pressing Alt + F11 simultaneously on your keyboard.

ðŸ‘‰ If a Developer tab is not visible in your ribbon, you can make it visible. To do that, go to,

File â†’ Option â†’ Customize Ribbon

## Conclusion

I hope all of the suitable methods mentioned above to calculate the distance between two addresses or coordinates with VBA code will now provoke you to apply them in your Excel spreadsheets with more productivity. You are most welcome to feel free to comment if you have any questions or queries.

<< 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. Abdur Rahim Rasel

MD. ABDUR RAHIM is a marine engineer proficient in Excel and passionate about programming with VBA. He views programming as an efficient means to save time while managing data, handling files, and engaging with the internet. His interests extend to Rhino3D, Maxsurf C++, AutoCAD, Deep Neural Networks, and Machine Learning, reflecting his versatile skill set. He earned a B.Sc in Naval Architecture & Marine Engineering from BUET, and now he has become a content developer, creating technical content... Read Full Bio

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF