Excel VBA: Calculate Distance Between Two Addresses or Coordinates

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

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)

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

• Further, simply press ENTER on your keyboard. As a result, you will be able to calculate the distance in miles between addresses named Manhattan and Los Angeles. The distance 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

Download Practice Workbook

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

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