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

excel vba calculate distance between two addresses or coordinates


Download Practice Workbook

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


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.

excel vba calculate distance between two addresses or coordinates


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.

excel vba calculate distance between two addresses or coordinates

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

Read More: How to Calculate Coordinates from Bearing and Distance in Excel


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

excel vba calculate distance between two addresses or coordinates

  • 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

Read More: How to Calculate Driving Distance between Two Addresses in Excel


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)

excel vba calculate distance between two addresses or coordinates

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

excel vba calculate distance between two addresses or coordinates

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

excel vba calculate distance between two addresses or coordinates

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


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.


Related Articles

Md. Abdur Rahim Rasel

Md. Abdur Rahim Rasel

Hi! I'm Md. Abdur Rahim Rasel. Welcome to my Profile. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. I have completed my graduation in Naval Architecture and Marine Engineering(NAME) from Bangladesh University of Engineering and Technology(BUET). I have a passion for learning new things with my knowledge through perseverance and hard work.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo