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 coordinates:

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