Continuing my last post, on how to get the distance between any addresses using VBA, I wanted to add a capability to my previous Excel file that would allow me to get any address coordinates. How to quickly get the lat. and long. of an address? Again why not use Google API…
Address coordinates in Excel
Sub GetLocation(address As String, ByRef lat As String, ByRef lng As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "http://maps.googleapis.com/maps/api/geocode/json?address="
lastVal = "&sensor=false"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = firstVal & Replace(address, " ", "+") & lastVal
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
If InStr(objHTTP.responseText, """lat""") = 0 Then GoTo ErrorHandl
tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lat"" : ") - 7)
lat = Split(tmpVal, ",")(0)
tmpVal = Right(objHTTP.responseText, Len(objHTTP.responseText) - InStr(objHTTP.responseText, """lng"" : ") - 7)
lng = Replace(Split(tmpVal, "}")(0), " ", "")
Exit Sub
ErrorHandl:
lat = lng = 0
End Sub
This function can easily be used in any VBA algorithm like this:
Dim lat1 as String, lng1 as String
call GetLocation("Al. Jerozolimskie 2, Warsaw", lat:=lat1, lng:=lng1)
In the example above the address coordinates (latitude and longitude) will be returned to lat1 and lng1 variables.
Ever wanted to calculate the distance between two addresses in Excel? I recently had the following issue: from a list of over approx. 50 administration offices across my city I wanted to find the one that is closest to my workplace. Of course open Google Maps and type each location one by one… and then choose the shortest connection. By why bother when we have Google Maps Distance Matrix API!!!
This post includes information on how to calculate the distance in Excel:
Google has a lot of useful API out there and I encourage you to go sometime to the Google API Explorer and have a look at what other information you can easily utilize from Excel or your other applications.
Let’s however focus on getting the distance between two addresses. Google facilitates Google Maps Distance Matrix API for limited usage.
The API is configured using GET HTTP Params. A simple example below. Say we want to get the distance between San Francisco and Victoria BC. We want to get there by bicycle. Copy this link into your browser:
Google Distance between Vancouver, BC, Canada and San Francisco, CA, USA
Google Distance Matrix Builder
Want to quickly test the Google API? Below a simple form for building a quick Distance URL. Simply type in the From an To addresses, select the transportation mode and hit Build URL!. Go here for more options for configuring the Google Maps Distance Matrix API.
From:
To:
Mode:
Google Maps Distance Matrix URL:
Calculate distance between two addresses using Google Maps in Excel
So I knocked up quickly this VBA Function in Excel which uses Google API distance matrix function to calculate the Google Maps distance. See the VBA code here:
Get Google Maps distance in meters
'Calculate Google Maps distance between two addresses
Public Function GetDistance(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=car&language=pl&sensor=false"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
If InStr(objHTTP.responseText, """distance"" : {") = 0 Then GoTo ErrorHandl
Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = """value"".*?([0-9]+)": regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
GetDistance = CDbl(tmpVal)
Exit Function
ErrorHandl:
GetDistance = -1
End Function
Get Google Maps duration (in seconds)
Public Function GetDuration(start As String, dest As String)
Dim firstVal As String, secondVal As String, lastVal As String
firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
secondVal = "&destinations="
lastVal = "&mode=car&language=en&sensor=false"
Set objHTTP = CreateObject("MSXML2.ServerXMLHTTP")
URL = firstVal & Replace(start, " ", "+") & secondVal & Replace(dest, " ", "+") & lastVal
objHTTP.Open "GET", URL, False
objHTTP.setRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
objHTTP.send ("")
If InStr(objHTTP.responseText, """duration"" : {") = 0 Then GoTo ErrorHandl
Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = "duration(?:.|\n)*?""value"".*?([0-9]+)": regex.Global = False
Set matches = regex.Execute(objHTTP.responseText)
tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
GetDuration = CDbl(tmpVal)
Exit Function
ErrorHandl:
GetDuration = -1
End Function
Calculate distance between two coordinates
You can calculate the distance between two coordinates (2 pairs of latitudes and longitudes) using either Google’s API or a simple VBA Function.
Calculate distance between coordinates using a VBA function
Taking into account the elliptic nature of Mother Earth you can easily calculate the distance between coordinates without using Google API .
Warning – no the world is not flat as opposed to what the Flat Earch Society is stating
The function returns the distance and using the unit variable you can state if you want the function to return the distance in Kilometres ("K"), Miles ("M") or even nautical miles ("N").
Public Function GetDistanceCoord(ByVal lat1 As Double, ByVal lon1 As Double, ByVal lat2 As Double, ByVal lon2 As Double, ByVal unit As String) As Double
Dim theta As Double: theta = lon1 - lon2
Dim dist As Double: dist = Math.Sin(deg2rad(lat1)) * Math.Sin(deg2rad(lat2)) + Math.Cos(deg2rad(lat1)) * Math.Cos(deg2rad(lat2)) * Math.Cos(deg2rad(theta))
dist = WorksheetFunction.Acos(dist)
dist = rad2deg(dist)
dist = dist * 60 * 1.1515
If unit = "K" Then
dist = dist * 1.609344
ElseIf unit = "N" Then
dist = dist * 0.8684
End If
GetDistanceCoord= dist
End Function
Function deg2rad(ByVal deg As Double) As Double
deg2rad = (deg * WorksheetFunction.Pi / 180#)
End Function
Function rad2deg(ByVal rad As Double) As Double
rad2deg = rad / WorksheetFunction.Pi * 180#
End Function
Calculate distance between coordinates using Google Maps in Excel
To get Google Maps distance between two coordinates simply use the same GetDistance function as above and replace the start and dest parameters with the coordinates in this format:
Important: Please remember that you need a direct Internet connection! Not via proxy etc.
Follow the steps:
Add new VBA Module
Go to the DEVELOPER ribbon and add select Visual Basic a and new Module to your VBA Project
Insert the VBA code
Insert the code from sections above (notice that the function is “Public” – therefore will be visible in your workbook as a so called UDF (User Defined Function)
Input the function in Excel
Go to the worksheet and input the function as shown below:
=GetDistance("Chicago"; "New York")
Make sure to replace ; with your default formula list separator (e.g. comma in the US)!
Calculate distance using Google Maps between any number of coordinates/addresses in Excel
Now that we know how to leverage our newly learned GetDistance and GetDuration functions we can use them to measure the distance/duration for any routes, with any coordinates/addresses in between our starting point and our destination. For this I created 2 simple procedures MultiGetDistance and MultiGetDuration:
Get distance between any number of locations
By using our GetDistance function we can get the distance between any number of locations using the Visual Basic procedure below:
Public Function MultiGetDistance(ParamArray args() As Variant) As Double
MultiGetDistance = 0
Dim startLoc As String, endLoc As String, i As Long
For i = LBound(args) To UBound(args) - 1
startLoc = args(i): endLoc = args(i + 1)
MultiGetDistance = MultiGetDistance + GetDistance(startLoc, endLoc)
Next i
End Function
And here is the output: Get Google Maps distance between multiple addresses
Get duration between any number of locations
Similarly by using our GetDuration function we can get the duration between any number of locations using the Visual Basic procedure below:
Public Function MultiGetDuration(ParamArray args() As Variant) As Double
MultiGetDuration = 0
Dim startLoc As String, endLoc As String, i As Long
For i = LBound(args) To UBound(args) - 1
startLoc = args(i): endLoc = args(i + 1)
MultiGetDuration = MultiGetDuration + GetDuration(startLoc, endLoc)
Next i
End Function
Parameters for calculating the Google Maps Distance
The following parameters are available in the API query:
Param
Description
key
Your application’s API key. This key identifies your application for purposes of quota management. Learn how to get a key from the Google Developers Console.
mode
(defaults to driving) — Specifies the mode of transport to use when calculating distance. Valid values and other request details are specified in the Travel Modes section of this document. Other modes:
driving (default) for road network.
walking for pedestrian paths & sidewalks (where available).
bicycling for bicycling via bicycle paths & preferred streets (where available).
transit for public transit routes (where available).
language
The language in which to return results. See list here
avoid
Restrictions to the route. Available:
avoid=tolls
avoid=highways
avoid=ferries
units
Unit system to use when expressing distance as text. Available:
units=metric (fordistances in kilometers and meters)
So beware of any mass recalculation of these functions as you may quickly find yourself exceeding these limits.
Is the function returning -1 or simply not working properly? Be sure you have a direct Internet connection! The XMLHttpRequest Object above is not configured to handle proxy servers common for Office environments.
Common issues
The functions above are not full proof and don’t take into account:
That Google won’t find the exact addresses and will approximate with a similar address (see component filtering for more precision)
That Google might return several matches. While the function takes the first one from the returned list
HTTP or timeouts. See my Web Scraping Kit for how I dealt with such
That distances/duration differ depending on which location is set as origin and which is set as destination (one way roads, detours etc.)
You can download an example of the above below and support AnalystCave.com:
Help!
Need help with calculating the distance between two addresses using the approach above? Feel free to comment below or ask a new Question via Questions page.
(4 votes, average: 4.50 out of 5)
Loading...
