Example of use of JSON in VBA: Geographical Distance checker


Background

Everybody knows the fantastic Google Maps. However, not everybody knows that Google also offers the possibility to interact with Google Maps from your own applications, by means of the Google Maps API. The API is public and free of charge.

Please, be responsible when you use the API.

In what follows, we will give a detailed example of how to use one of the Google Maps API functions, in VBA. The example might be interesting for several reasons.

  • It shows that the big IT players use JSON.
  • It shows how to use an external API in VBA.
  • It shows that our JSON class can make coding easier.

You can download the workbook via the button hereunder (or via the Downloads page). As the VBA code needs to communicate with the Google Maps API, an internet connection is required to make the code work correctly.

Download the Workbook

Objective

Let's say we want to have a function in Excel that returns the driving distance between any two places, using the Google Maps API. Say, for instance, that places are the cities Brussels and Prague, and that our Excel worksheet looks as follows.

Image showing excel layout, input

The objective is to build a user defined function in VBA, say GetGoogleMapsDrivingDistance() that takes as input the names of the two places and returns the driving distance.

Image showing excel layout, required output

Solution, the principle

Thanks to Google, the solution is easy. All we need to do is to send the following URL to the Google Maps API.

https://maps.googleapis.com/maps/api/distancematrix/json?origins=Brussels&destinations=Prague&mode=driving&language=en-GB&v=3&sensor=false

The Google Maps API will respond, and the response will be a JSON text.

{
"destination_addresses" : [ "Prague, Czechia" ],
"origin_addresses" : [ "Brussels, Belgium" ],
"rows" : [
{
"elements" : [
{
"distance" : {
"text" : "904 km",
"value" : 904330
},
"duration" : {
"text" : "8 hours 44 mins",
"value" : 31458
},
"status" : "OK"
}
]
}
],
"status" : "OK"
}

Then, it is up to us to parse out the distance.

Solution, the VBA code

First, let's try to send the URL with VBA and obtain the response.

Open the VBA editor of the workbook (push Alt + F11). Then, add a module and copy the following VBA code into it.

The MSXML2.XMLHTTP60 object is part of the Microsoft XML reference (see here for details. To make the code work, the reference must be checked (in the VBA editor, Tools, References..., search in the list and check it). The JSON text that the Google Maps API returns will be in oHTTPRequest.responseText and is in fact a simple String. Try the code by going to the worksheet and confirming the formula in cell D6. The full JSON text should be shown in the cell D6. If you see this, it means that your communication with Google works!

Image showing excel layout, output try 1

Second, let's now extract the distance from the JSON text.

Let's do this in separate function, called ExtractDistanceFromResponseText. Change the VBA code as follows.

There are several possible ways to extract the distance from the JSON text. One option is to use basic string search functions, like Instr(). After a bit of trying, we will get there for sure. There is nothing wrong with that approach, however, there may be an easier solution. Let's try the JSON class 🙂

All the code is standard, except for the line D.Item("rows").Item("0").Item("elements").Item("0").Item("distance").Item("text") which depends on the structure of the JSON text. Take a minute to study the structure of the JSON text that the Google Maps API sends back and focus on the objects {} and the arrays []. The line D.Item("rows").Item("0").Item("elements").Item("0").Item("distance").Item("text") simply reflects the structure of the JSON text.

Let's now try the code again (go to the worksheet, confirm the formula in cell D6). You should obtain this. Objective achieved!

Image showing excel layout, output try 2

Solution, the code revisited

Case 1

Note that even when you put spelling errors in the two city names, the code works fine. All credit for this is for Google Maps. Google Maps recognizes the spelling errors and handles them fine.

Image showing excel layout, output spelling error

Let's see what happens if we exaggerate.

Image showing excel layout, output spelling error

The VBA code generates and error and the function returns #VALUE!. There is nothing wrong with this behavior. However, it is interesting to have a closer look at the JSON text that the Google Maps API has sent back in this case.

{
"destination_addresses" : [ "" ],
"origin_addresses" : [ "Brussels, Belgium" ],
"rows" : [
{
"elements" : [
{
"status" : "NOT_FOUND"
}
]
}
],
"status" : "OK"
}

First of all, the JSON text that the Google Maps API has sent back is valid, i.e. it matches the JSON grammar rules. However, the content is slightly different than before. The distance field does not exist. Because of the exagerated spelling error, the "destination_addresses" is an array that contains only an empty string. The status field in the array associated to "elements" has been set to "NOT_FOUND".

Let's take another case.

Case 2

Delete the contents of cell D4. It is as if someone forgets to pass a destination.

Image showing excel layout, output empty destination

The VBA code again generates and error and the function GetGoogleMapsDrivingDistance returns #VALUE!. Once again, there is nothing wrong with this behavior. Let's have a closer look at the JSON text that the Google Maps API has sent back.

{
"destination_addresses" : [],
"origin_addresses" : [],
"rows" : [],
"status" : "INVALID_REQUEST"
}

Once more, the JSON text that the Google Maps API has sent back is valid, matching the JSON grammar rules. But again, the content is different than in the normal case. Because of the empty input, the "destination_addresses" and the the "origin_addresses" are empty arrays. The status field - another one than the one of the previous case - has been set to "INVALID_REQUEST".

Let's take one more case.

Case 3

Try to find the driving distance between Washington and Moscow.

Image showing excel layout, output no driving possible

Once more, the VBA code again generates and error and the function GetGoogleMapsDrivingDistance returns #VALUE!. This is not surprising as it is impossible to drive from Washington to Moscow (there is an ocean in between). What JSON text did the Google Maps API sent in this case?

{
"destination_addresses" : [ "Moscow, Russia" ],
"origin_addresses" : [ "Washington, USA" ],
"rows" : [
{
"elements" : [
{
"status" : "ZERO_RESULTS"
}
]
}
],
"status" : "OK"
}

Again, a valid JSON text but with slightly different content. The JSON text looks a lot like the JSON text of case 1. The difference is that the status field in the array associated to "elements" has been set to "ZERO_RESULTS" in stead of "NOT_FOUND".

Better code

Even though there is a different cause, for each of the three cases, the function ExtractDistanceFromResponseText returns function returns #VALUE!. In itself, there is nothing wrong with that. But suppose we don't want that. Suppose, when something goes wrong, we would like to have a hint on the cause. Here is where doing the data extraction from the JSON text by using the JSON class comes in handy.

Replace the content of the function ExtractDistanceFromResponseText by this.

The extra code simply adds error management and handles special cases. Admitted, we can also work it out using basic string search functions. No problem. However, the JSON class does make it very easy, no? And, the resulting code remains quite readable. The If structure beginning with If (JSON.IsOk()) Then protects against the Google Maps API sending an invalid JSON text. Given the quality of the Google products, the probability that this will happen is low, so you can omit that line if you like. It would simplify the code even more.

For the three cases, the outputs are now different.

Image showing excel layout, output spelling error
Image showing excel layout, output empty destination
Image showing excel layout, output no driving possible

Closing remark

Would we obtain the same result in the browser? Go to https://www.google.com/maps and ask for directions from Brussels to Prague.

Image showing directions input in Google Maps

At the moment of writing, Google maps returns us this.

Image showing directions from Brussels to Prague in Google Maps

The distance of the driving route is 907 km. The distance that the Google Maps API returned to VBA was 904 km. We have tested some more and sometimes, we obtain the exact same distance, sometimes not. There will probably be a logic behind it, but we don't know what it is. Nothing really wrong with that. Just so you know.