Tag Archives: UDF

excel webservice function

Excel WEBSERVICE and FILTERXML functions explained

1 Star2 Stars3 Stars4 Stars5 Stars (1 votes, average: 5.00 out of 5)
Loading...

One of the incredible new features coming to Excel 2013 are the Excel WEBSERVICE and Excel FILTERXML Worksheet functions. Excel 2013 introduces over 50 new Worksheet functions but I will tell you why the 2 above-mentioned rock my world ever since I learn of them. It is incredible that the introduction of this awesome function was almost unnoticed by most Excel gurus out there…

Excel up till now has been mostly an offline application. Although, you can use VBA, PowerQuery or other similar data tools in Excel to gain access to Internet and Online data sets these could not have been easily used with the help of Third Party AddIns such as my Scrape HTML AddIn. With the introduction of the WEBSERVICE function we gain immediate and easy access to any REST WebAPI.

In today’s post I will show you several examples of how to use these functions in practice. And how to extract data from an XML REST WebApi.

How to use the Excel WEBSERVICE function

The WEBSERVICE function downloads the HTTP response of the provided URL.

excel webservice function
The Excel WEBSERVICE Worksheet function

Try running the Excel WEBSERVICE function on Google:

=WEBSERVICE("https://www.google.com")

What do you get? If all goes well you should get the HTML response for the Google Web Page.
Now past the above formula to cell A1 and the below to cell A2:

=MID(A1;FIND("<title>";A1)+LEN("<title>");FIND("</title>";A1)-FIND("<title>";A1)-LEN("<title>"))

The result of this Worksheet Excel Formula should be:

Google

Using FILTERXML in tandem with WEBSERVICE

Another function introduced in Excel 2013 is the FILTERXML function. It is designed to work in tandem with the Excel WEBSERVICE function.

What does the FILTERXML function do?

The Excel FILTERXML Worksheet function parses a XML string (string containing XML document) and returns a single element (node or attribute) provided by a XPath.

excel filterxml
The Excel FILTERXML Worksheet Function

Lots of odd words there right? So let us start breaking it down. XML is markup language for encoding documents. As a matter of fact HTML is based on XML and share a lot of similarities. Unfortunately for us HTML does not need often have to be as strictly parsed/validated as an XML does. Although some HTML could pass as XML files – in fact many Websites don’t validate as XML.

Want to learn how to manage XML documents in VBA instead? Read my VBA XML Tutorial

Now what is XPath? XPath is a query language for selecting XML elements such as nodes and attributes. XPath works for XML and HTML.

How to use the FILTERXML function

Now that we know what the FILTERXML functions let’s use it on a simple XML file. W3Schools fortunately has a lot of simple XML examples – let us use a simple XML Food Menu:

Example: WEBSERVICE and FILTERXML function

filterxml and webservice example 1
Using WEBSERVICE and FILTERXML in tandem – Formulas

Example: WEBSERVICE and FILTERXML result

filterxml and webservice example 2
Using WEBSERVICE and FILTERXML in tandem – Result

Explanation

xml example
W3Schools XML Example Food Menu
Now to explain what happens above. The XML file includes a couple of nodes – each one nested within the previous one. We start with node which hosts nodes. Each food node represents a single item in the menu. A food node contains , and node – which describe each menu item.

Now the FILTERXML functions used the following XPath: //food/name. This translates to: take the first food tag and return the contents of it name node.

XPath is an easy query language to learn. I personally recommend the WSchools XPath tutorial

Scraping a whole XML document

Now the example above is fine when you need just a single node from your XML document URL. What if you want to scrape the whole contents of that XML? Thankfully we can combine the WEBSERVICE and FILTERXML functions with Array Formulas.

In the example below I will show you how to acquire all the names of the food items in the menu. You can use a similar technique to get other items.

Input the FILTERXML formula

Input the FILTERXML formula as shown below:

=FILTERXML(B2;"//food/name")

filterxml and webservice example 4

Drag the formula down

Drag the formula down to row 8:
filterxml and webservice example 5

Hit CTRL+SHIFT+ENTER

Hit the following key combo to create and Array Formula: CTRL+SHIFT+ENTER.
That is it. Now in each row you should see the name of a food item from the menu.

Don’t like using Array Functions? You can also use the XPath node index instead:

=FILTERXML(B2;"//food[2]/name")

will return the name of the second food menu item. To replicate this across all items use this example:

=FILTERXML(B$2;"//food[" & (ROW()-ROW(B$4)+1) &"]/name")

Provide you entered this function in cell B2, simply drag it down – the items should automatically complete.

WEBSERVICE functions Restrictions

Now the WEBSERVICE function unfortunately has several restrictions that will cause the function to return a #VALUE! error instead of the string:

  • If you don’t have a working Internet connection (or you are working with a proxy server)
  • Incorrect arguments or URL address
  • If HTTP result is not valid or contains more than the cell limit of 32767 characters
  • URL is a string that contains more than the 2048 characters that are allowed for a GET HTTP request
  • Protocols that aren’t supported, such as ftp:// or file://

Excel WEBSERVICE summary

The WEBSERVICE and FILTERXML functions are a great step forward to enabling access to Internet resources. These still have unfortunately a lot of limits. Especially when most websites have HTML files that exceed the 32727 character limit and often don’t parse as XML files.

This is where my VBA Web Scraping Kit fills the gap together with my Scrape HTML AddIn. The Kit has all the Web Scraping scenarios I consider possible in Excel where as the Scrape HTML AddIn extends a little the constrains of the WEBSERVICE and the FILTERXML functions

Want to learn Web Scraping?

Not satisfied? Want to know more about Web Scraping in Excel using VBA? Read my zero-to-hero Web Scraping Tutorial.

regex

Excel Regex Tutorial (Regular Expressions)

1 Star2 Stars3 Stars4 Stars5 Stars (9 votes, average: 4.56 out of 5)
Loading...

Regular expressions are ubiquitous in the developer world. They are used to validate website input, search for word patterns in large strings/texts and for many other uses. In Excel, Regular Expressions (RegEx or simply Regex) are not much advertised. Most users are good with using simple LEFT, RIGHT, MID and FIND functions for their string manipulation. These functions are, however, quite primitive and limited. Knowing how to use Regular Expressions (Regex) in Excel will save you a lot of time. This Excel Regex Tutorial focuses both on using Regex functions and in VBA. Let’s, however, not forget that VBA has also adopted the Like operator which sometimes allows you to achieve some tasks reserved for Regular Expressions.

excel regex level
Using the right tools

What I aim with this tutorial is to make sure you learn to use the right tools at the right time!

LVL 1: Manipulating Strings

Before jumping into Regular Expressions I encourage you to spend a moment on learning the basic string manipulation functions available both as Excel functions and in VBA. Click here to go to the VBA String Functions reference:

VBA String Functions
VBA String Functions

These functions, when used in combination, allow you to achieve most often required string manipulation tasks.

Read more on the VBA String Functions here

LVL 2: Like Operator

Before we move on to Regular Expressions let’s focus on a bit on the Like Operator which often go unmentioned as some of us are so at home with writing Regular Expressions that we don’t want to make the extra effort to learn this.

If "Animal" Like "[A-Z]*" then 
   Debug.Print "Match: String starting with Capital letter!"
End If
Read more on the VBA Like Operator here

LVL 3: Regular Expressions (Excel Regex – RegExp)

A Regex (Regular Expression) is basically a pattern matching strings within other strings. Let’s demonstrate this with a simple Regex example. Let us assume we have the text below. And we want to capture just the numbers.

Hello 134, World 3424, 04234 Some Text

Without knowing ahead how the text looks like it would be hard to extract these numbers both using Excel Functions and VBA.
But with a Regex we can extract the numbers with a simple pattern:

[0-9]+

The captured numbers:

134, 3424, 04234

What does the [0-9]+ pattern represent? It translates to the following: capture any pattern matching the following range of characters ([ ]), being numbers from 0-9, in a sequence of at least 1 or more (+). As you can see a Regex uses a certain code to translate your pattern.

Regular Expression Language

The Regular Expression language (Regex) is quite elaborate but allows you to match virtually any regular language. Below a quick reference:

Matching characters

Syntax Description Example Example match
. Any character except newline (vbNewLine) d.g “dog” in “My dog is named dingo”
[characters] Matches any provided character between brackets [ ] [af] “a” , “f” in “alfa”
[^characters] Matches any character not being one of the provided between brackets [ ] [af] “a” , “f” in “alfa”
[startend] Matches any character belonging to the character range specified between brackets [ ] [0-9] “1” and “2” in “12”
w Any word character (letters, modifiers, digits, punctuation and connectors) w “I”, “a” “m” “J” “o” “h” “n” in “I_am.John”
W Any non-word character w “_” and “.” in “I_am.John”
s Any white space character s ” ” in “Hi There!”
S Any non-white space character S “M” and “e” in “M e”
d Any decimal digit d “1” and “2” in “12”
D Any non-decimal digit D “d”, “_”, “.” in “d_.”
Followed by any special character – escapes special characters . “.” im “d.g”
r Tab (vbTab) r
n Carriage return / new line (vbNewLine)

Quantifiers

Quantifiers allow you to specify the amount of times a certain pattern is supposed to matched against a string. It is important to understand the difference between GREEDY and non-GREEDY quantifiers:

Greedy vs Non-Greedy Regular Expression
Greedy vs Non-Greedy Regular Expression

Syntax Description Example Example match
* Zero or more of (GREEDY). Matches as many as possible W.*W “_dogs_cats_” in “_dogs_cats_”
+ One or more of (GREEDY). Matches as many as possible Ww+W “_dogs_cats_” in “_dogs_cats_”
? Zero or once (GREEDY). Matches as many as possible d? “1” in “Live1”
{n} “n” many times d{2} “21” and “12” in “212”
{n,} At least “n” times (GREEDY) d{2,} “12” and “123” in “1_12_123”
{n,m} Between “n” and “m” times (GREEDY) d{3,4} “123” and “1234” in “1_12_123_1234”
*? Zero or more of (non-GREEDY). Matches as few as possible W.*?W “_dogs_” and “_cats_” in “_dogs_cats_”
+? One or more of (non-GREEDY). Matches as few as possible W.+?W “_dogs_” and “_cats_” in “_dogs_cats_”
?? Zero or once (non-GREEDY). Matches as few as possible d?? “1” in “Live1”
{n,}? At least “n” times (non-GREEDY). Matches as few as possible d{2,} “12” and “123” in “1_12_123”
{n,m}? Between “n” and “m” times (non-GREEDY). Matches as few as possible d{3,4} “123” and “1234” in “1_12_123_1234”

Grouping

Below the basic grouping expressions:

Syntax Description Example Example match
(expression) Group and capture the expression within the parenthesis ( ) ([0-9]*) Captures “123, “345” and “789” within “123-456-789”
(?:expression) Group BUT DON’T CAPTURE the expression within the parenthesis ( ) (?:[0-9]*)([A-Z]*)(?:[0-9]*) Captures only “hello” in “123hello456”

Using Regex in VBA

To use Regex in VBA you need to use the RegExp object which is defined in the Microsoft VBScript Regular Expressions library. To start using this object add the following reference to your VBA Project: Tools->References->Microsoft VBScript Regular Expressions. Otherwise, if you don’t want to reference this library every time you can also create the RegExp object using the CreateObject function.

Option 1: Referencing the library Microsoft VBScript Regular Expressions

Dim regex as RegExp
Set regex = New RegExp

Option 2: Using the CreateObject function

Dim regex as Object
Set regex = CreateObject("VBScript.RegExp")

I personally prefer using the CreateObject function as it does not require referencing the library every time the Workbook is opened on a new workstation.

The RegExp object has the following properties:

  • Pattern – The pattern (written in Regex) which you want to match against (e.g. “(.*)”)
  • IgnoreCase – Ignore letter case (captial/non-capital letters)
  • Global – Do you want to find all possible matches in the input string? If false, only match the first found pattern. Set false if you need just the first match for performance
  • MultiLine – Do you want to match the pattern across line breaks?

The RegExp object facilitates the following 3 operations:

  • Test (string) – returns True if the pattern can be matched agaist the provided string
  • Replace (search-string, replace-string) – replaces occurrences of the pattern in search-string with replace-string
  • Execute (search-string) – returns all matches of the pattern against the search-string

Regex: Test pattern against a string

The Test function allows you to test whether the selected Pattern provides any match against the string.

Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

With regex
  .Pattern = "[0-9]+"
End With
    
str = "Hello 123 World!"
Debug.Print regex.Test(str) 'Result: True

str = "Hello World!"
Debug.Print regex.Test(str) 'Result: False    

Regex: Replace pattern in a string

The Replace function will replace the first (if Global = False) or all matching patterns (if Global = True) within a certain string with another string of your choosing.

Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

With regex
  .Pattern = "123-[0-9]+-123"
  .Global = True 'If False, would replace only first
End With
    
str = "321-123-000-123-643-123-888-123"
Debug.Print regex.Replace(str, "<Replace>") 
'Result: 321-<Replace>-643-<Replace>

Regex: Match pattern in a string

The Execute function will match the first or all instances of a certain pattern within a certain string. You can also “capture” parts of the patterns as so called “Submatches”.

Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

With regex
  .Pattern = "123-[0-9]+"
  .Global = True
End With
    
str = "321-123-000-123-643-123-888-123"

Set matches = regex.Execute(str)
    
For Each match In matches
  Debug.Print match.Value 'Result: 123-000, 123-643, 123-888
Next match

As you can see we have managed to capture 3 instances of the 123-[0-9]+ pattern in the string. We can also define a “capture” within our pattern to capture parts of the pattern by embracing them with brackets “()”. See the example below:

Dim regex As Object, str As String
Set regex = CreateObject("VBScript.RegExp")

With regex
  .Pattern = "123-([0-9]+)" 'Notice the () around the second sequence
  .Global = True
End With
    
str = "321-123-000-123-643-123-888-123"

Set matches = regex.Execute(str)
  
For Each match In matches
  Debug.Print match.Value 'Result: 123-000, 123-643, 123-888
  If match.SubMatches.Count > 0 Then
    For Each subMatch In Match.SubMatches
      Debug.Print subMatch 'Result: 000, 643, 888
      Next subMatch
  End If
Next match

Regex: Using Regex as an Excel Formula

Excel does not natively provide any Regex functions which often requires creating complex formulas for extracting pieces of strings otherwise easy to extract using Regular Expressions. Hence, to facilitate Regex in Excel you need to use User Defined Functions – functions defined in VBA but accessible as regular functions in Excel. Below find 2 basic UDF functions created just for this use:

'Returns the number of matches found for a given regex
'str - string to test the regex on
'reg - the regular expression
Public Function RegexCountMatches(str As String, reg As String) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg: regex.Global = True
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        RegexCountMatches = matches.Count
        Exit Function
    End If
ErrHandl:
    RegexCountMatches = CVErr(xlErrValue)
End Function

'Executes a Regular Expression on a provided string and returns a selected submatch
'str - string to execute the regex on
'reg - the regular expression with at least 1 capture '()'
'matchIndex - the index of the match you want to return (default: 0)
'subMatchIndex - the index of the submatch you want to return (default: 0)
Public Function RegexExecute(str As String, reg As String, _
                             Optional matchIndex As Long, _
                             Optional subMatchIndex As Long) As String
    On Error GoTo ErrHandl
    Set regex = CreateObject("VBScript.RegExp"): regex.Pattern = reg
    regex.Global = Not (matchIndex = 0 And subMatchIndex = 0) 'For efficiency
    If regex.test(str) Then
        Set matches = regex.Execute(str)
        RegexExecute = matches(matchIndex).SubMatches(subMatchIndex)
        Exit Function
    End If
ErrHandl:
    RegexExecute = CVErr(xlErrValue)
End Function

Now for an example:

Regex Excel: Regex UDF functions example
Regex UDF functions example

…and the result:
Regex Excel: Regex UDF functions result
Regex UDF functions result

Download Excel Regex example

You can download a working example with the Regex UDF function here:

ReFiddle – Online testing your Regex!

Want to test quickly a Regular Expression (Regex)? Use ReFiddle. It is a great tool to quickly validate if a Regex works and to be able to quickly share your regex with others!

Keep in mind, however, that the VBA Regular Expression language (supported by RegExp object) does not support all Regular Expressions which are valid in ReFiddle.

Learn Regex (Regular Expression) the Fun way

Want to learn building Regex (Regular Expressions) and have some fun at the same time?

Try Regex Golf:
Regex Golf

regex golf

google maps distance

Excel VBA Calculate distance between two addresses or coordinates

1 Star2 Stars3 Stars4 Stars5 Stars (23 votes, average: 4.57 out of 5)
Loading...

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:

To get the COORDINATES of any ADDRESS read this post

Using the Google Maps Distance Matrix API

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:

https://maps.googleapis.com/maps/api/distancematrix/json?origins=Vancouver+BC|Seattle&destinations=San+Francisco|Victoria+BC&mode=bicycling&language=en
Google Distance between Vancouver, BC, Canada and San Francisco, CA, USA
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:

41.43216,-81.49992

Final call example:

Debug.Print GetDistance("41.43206,-81.38992", "41.43216,-81.49992")

Same goes for the duration function:

Debug.Print GetDuration("41.43206,-81.38992", "41.43216,-81.49992")

How to set it up in Excel?

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 calculate distance macro

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) add calculate distance macro

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 between addresses or coordinates

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

Below example usage:

=MultiGetDistance("Chicago";"New York";"San Jose")

And here is the output:

Calculate distance using Google Mapsbetween multiple addresses
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

Below example usage:

=MultiGetDuration("Chicago";"New York";"San Jose")

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)
  • units=imperial

For other parameters see the original Google Distance Matrix API page:
The Google Distance Matrix API

Limits and common issues

Read more on the limitations of the Google Distance Matrix API here:
The Google Distance Matrix API

Google limits the amount of free queries to the following:

  • 100 elements per query.
  • 100 elements per 10 seconds.
  • 2 500 elements per 24 hour period.
There is a way around these limitation via the use of HTTP Proxies. Read more here.

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

Having trouble with matching a large dataset with distances and durations? Reach out for a free quote.

Download an example

You can download an example of the above here:

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.

Next steps

Want to get the geolocation (coordinates) of any address?
Excel: Get geolocation (coordinates) of any address
Want to add maps and other cool Google Charts in Excel?
Excel: Google Charts Tool
Want to use Google translate in Excel?
Excel: Google Translate functionality