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

Table of Contents hide

Download Practice Workbook

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

Step 1: Create API Key from Map

Step 2: Open Visual Basic Window to Write VBA Code

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

Things to Remember

Conclusion

Download Practice Workbook

Download this practice workbook to exercise while you are reading this article.

Calculation of Distance Between Two Addresses.xlsm

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

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

Similar Readings

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

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

Further, simply press ENTER on your keyboard. As a result, you will be able to calculate the distance between Manhattan and Los Angeles. The distance between Manhattan and Los Angeles is 2800 miles which has been given in the below screenshot.

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.