Using Excel VBA to Calculate the Distance Between Two Addresses or Coordinates – 3 Steps

This is an overview:

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

The sample dataset contains information about the latitude and longitude of Manhattan and Los Angeles.

To calculate the distance between these two addresses or coordinates, create an API link and use the latitudes and longitudes in the VBA macro.

Follow the instructions below to calculate the distance between two addresses or coordinates using VBA macros!

Step 1 – Create an API Key from a Map

API stands for Application Programming Interface. Excel uses an API to connect with any map, like a Google Map or Bing Map to collect data.

Use a Bing Map to create a free API key.

• Create an account in Bing Maps.
• In My Account, go to:

My account  → My Keys

• In My Keys, enter the distance in Application name.
• Select Basic in Key type.
• Select Dev/Test in Application Type.
• Click Create.

• The API is created.

Step 2 – Open Visual Basic Window to Use a VBA Code

• Select the Developer tab and go to:

Developer → Visual Basic

• Click Visual Basic and in Microsoft Visual Basic for Applications – Calculation of Distance between Two addresses or Coordinates, insert a module:

Insert → Module

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

Create a User Defined Function.

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

• Run the VBA:

Run → Run Sub/UserForm

Calculate the distance between two cities: Manhattan and Los Angeles using the User Defined Function (GetDistance).

• Select E10 and enter the User Defined Function.

• Press ENTER.
• The distance in miles between Manhattan and Los Angeles is 2800 miles.

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 open the Microsoft Visual Basic for Applications window pressing Alt + F11.
•  If the Developer tab is not visible on the ribbon, enable it:

File → Option → Customize Ribbon

Download Practice Workbook

Download the practice workbook.

<< Go Back to Distance | Formula List | Learn Excel