# Excel VBA Calculate distance between two addresses or coordinates

(22 votes, average: 4.55 out of 5)

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:

## 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
```

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

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
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
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
dist = WorksheetFunction.Acos(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

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.

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
```

Below example usage:

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

And here is the output:

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

## Limits and common issues

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

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.

## 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 use Google translate in Excel?

## 131 thoughts on “Excel VBA Calculate distance between two addresses or coordinates”

1. Yama says:

Hi AnalystCave,

Thanks for the nice post,

For some reason i am getting the result as -1 .

1. Yama says:

Error Message : “tination_addresses” : [],
“error_message” : “You have exceeded your daily request quota for this API.”,
“rows” : [],
“status” : “OVER_QUERY_LIMIT”
}

Is there any way we can have unlimited query limit ?

2. I am in urgent need of a tool that does exactly this for a network study. It worked temporarily for samples I was putting in manually.

However, I have a long list of addresses origin – destination, they are all in Europe and when I put in =getdistance(a1, b1) it gives me -1.

After a couple of tries it also ended up returning -1 for any other manual attempt.

Having a tool like this at the moment would be vital to me. Any help greatly appreciated. Loved it for the few times it worked for me :_

1. AnalystCave says:

Hi Raphael, glad it can be of use. I don’t see any reason as to why it shouldn’t work. How many times did you try it? Remember to look at this section:
http://www.analystcave.com/excel-calculate-distances-between-addresses/#Limits_and_common_issues where I mention the limits of using the Google Distance Matrix API. Maybe you exceeded the limit of 100 calls/10 sec? Try running it from VBA instead of as an UDF as this will give you more control over the number of executed API calls. Also keep in mind that the API call might not work if executed behind a proxy.

Is it only for long distance destinations? If so try analyzing the API call itself (by pasting the URL String from line 7 in your browser window) and see what is returned.

2. Fábio says:

If I want to calculate the time instead the distance. What do I need to change in the script?

3. Jackb says:

this is very good however it is not returning decimal point so 37.5 km is returned as 375 km any help would be aprechiated

1. AnalystCave says:

Hey Jackb,
see the Important section as to why this is happening – it’s probably a problem with your locale.

1. Mark says:

I’m getting the same issue as Jackb. I have verified that “.” is the active character for decimal symbol but the output returns 591 (neither . nor , as separator) when it should be 59.1. Thanks.

1. Mark,
Replace the following code shown on line 14:
`tmpVal = Replace(Replace(Replace(Split(tmpVal, " km""")(0), ",", ""), ".", ","), " ", "")`
with this code:
`tmpVal = Replace(Replace(Split(tmpVal, " km""")(0), ",", "."), " ", "")`
This did the trick for me.

1. AnalystCave says:

Hi Greg,
thanks for helping out! I will include this in the updated post.

2. Jim Bean says:

Hi Greg,

I seem to have the same issue where KM are not being separated like Mark described. However I am having an issue with your replacement line. I replaced line 14 with your suggested and I get a #Value error. but I think it’s because the line 14 I am replacing is nothing like your line 14, I used the one provided in the code above: tmpVal = Replace(matches(0).SubMatches(0), “.”, Application.International(xlListSeparator)). Should I be injecting the code you recommend into this line as the replacement doesn’t work?

Thank you :)

4. Mo says:

Hi,

First thx for the great work! How can i get back meters instead of km?

1. AnalystCave says:

I updated the post – see both methods for KM and M.

5. Mitchel says:

Hi,
Thank you for the script it is working fine for me. However I also want do use the element duration. Basically the same question as Fábio. Can this be done by changing something in the script and make the function GetDuration? Thanks!

1. AnalystCave says:

Sure – just a slight change needed. I added the GetDuration function. Check out the post

6. Dean says:

I can’t seem to get it to work. Getting #Value!. Is it because I’m using Office 2011 on OS X?

1. AnalystCave says:

Hi Fredzînho,

try debugging the actual function line by line (F8) and report which line is throwing an error. Remember that the XMLHttpRequest object above is not configured for proxy so you need a direct Internet connection (corporate networks usually run via proxy). I am planning to add proxy configs for the future.

1. AnalystCave says:

1. Hi, first of all thank you for your code and explanation: it’s very elegant and clear.
However, as I work behind a proxy I have been trying to get around the limitation of XMLHttpRequest to try and run the function on my work computer. I have experimented with WinHttp.WinHttpRequest.5.1 but had no success. Have you ever had a chance to work a version of your function that can authenticate a proxy?

7. Wojtek says:

I’m getting #ARG!. I’ve turned on the “Microsoft XML 6.0” reference and I have no more idea, what to do to make it works…

1. AnalystCave says:

Did you try debugging the code? On which line of code are you getting an Error?

8. PW says:

This is fantastic!

I hate to ask, but is it possible to get a US localized version that uses miles and is set to use “.” as the decimal as opposed to “,”? I’ve been trying to tinker with the code above, but to no avail.

Hello ,

I really liked the code. This is somewhat similar to what i was looking for.
It would be great if you could help me with a code to Getdistance between latitude and longitude of multiple addresses.

Thank you,
Kavya

Hey Thomas,

Thank you!! There is no error while running the code.
But, it takes around 20 seconds to process and finally gives me #Value! error in the cell.

Can you please suggest what needs to be done?

Thanks,
Kavya

1. AnalystCave says:

Try the fixed version. I replaced the string manipulation with RegExp altogether

10. Hi, I set this all up in my spreadsheet yesterday and it was working wonderfully but today, when I opened my spreadsheet, all my distance cells show -1! I enabled macros and everything and since I’m new to VBA and coding, I haven’t the slightest clue where to even start looking for the problem…..please help!

Thank you in advance….I love this function!

1. AnalystCave says:

Hi Sonya,
That suggests there was an error in the VBA code. Did you lose you Internet connection? Are you connecting via a Proxy (office/public WLAN often) – if yes then try changing it. Otherwise add a breakpoint to the VBA code (on the second line best) and step through the code (hitting F8). Share the info on which line of code there was an error thrown.

This might also happen due to there being a momentary break in your Internet connection therefore you might need to recalculate the values manually (edit and hit ENTER on the cell). If you want to make the VBA UDF function to refresh automatically try adding this code at the beginning of the VBA:

`Application.Volatile`

11. This is soooo cool!

Quick question, I am trying to copy text from a Cell or Text Box, into the Functions.
I tried:
To no avail.

My VBA is limited, but it needs to be edited right? Something around here…?
URL = firstVal & Replace(start, ” “, “+”) & secondVal & Replace(dest, ” “, “+”) & lastVal

P.S: This is a fantastic API. I am amazed!!

1. AnalystCave says:

Hi Joshua,
glad you like it. First let me know if the example on my page works for you.
Most certainly text from others cells can be an argument for the GetDistance Google function like below:

`=GetDistance(A1, "Chicago")`

Not sure however bout the TextBox, however, you can go around this creating and UDF for the TextBox (ActiveX) like below:

``` Public Function GetTextBoxText() GetTextBoxText= Worksheets("Sheet1").TextBox1.Text End Function```

Make sure to replace the Sheet1, name of the textbox and put the UDF in a VBA Module (not the Sheet).

This is an example effect:

`=GetDistance(GetTextBoxText(), "Chicago")`

Hi Tom,

Thanks for your code. Now I have a list (>1000) records of lat and long of addresses and I need to get the distance between two lat long with just a click of a button.
And this should repeat for the >1000 rows present.

It needs to connect to Bing maps , to obtain the distance .Can you please help with a code for this?

Thanks,
Kavya

13. Greetings! Love it! HUGE time saver. I have a question.

I’m using this find distance and time from one origin to many destinations. When I had it in use between 1000 to 2000 rows, excel became groggy due to processes/calculations. My computer is a nice one.
Does this automatically update? Does it require continuous internet even after original calculation? Any way to change it so after first look up it stay static?

1. AnalystCave says:

Hi Chris,

glad you find it useful. The function should not update every time (unless you have `Application.Volatile=True` set at the beginning of the function which you should replace with `Application.Volatile=False`). If for some reason you are seeing some unnecessary recalculations happening I can suggest several options:

1. Check if you are not refreshing any of the parameters of the functions by updating other cells
2. Simply copy and replace the formula with the current value
3. Change the calculation method to manual and write a macro that would manually recalculate only the needed cells or turn on automatic recalculation only when needed
4. Extend the function VBA to use a global dictionary of connections (before connecting to Google you would need to look up the connection in the dictionary for an existing connection)
1. Thanks for the reply, I’ve been going with option 2 for now. I have little experience with macros and VBA. haha

14. Hi,

how to get this line of code “If InStr(objHTTP.responseText, “””distance”” : {“) = 0 Then GoTo ErrorHandl”
to get the distance Text Value “15Mi “not the Distance Value “24663”.
“rows” : [
{
“elements” : [
{
“distance” : {
“text” : “15.3 mi”,
“value” : 24663
},
“duration” : {
“text” : “23 mins”,
“value” : 1406
},
“status” : “OK”
}
]
}
],
“status” : “OK”

1. I fixed myself, being stupid, change this line of code to this and work as charm, Set regex = CreateObject(“VBScript.RegExp”): regex.Pattern = “””text””.*?([0-9]+)”: regex.Global = False

15. hi! i am an amateur but your instructions were very obvious. Thanks a lot.
i’ve noticed that there is a difference between the distance from this macro and that from google map. Is there any explanation? in a distance of 20 km there is a difference of 2.5 km. In other example of distance 1 km the difference is 0.5.
sometimes bigger and sometimes smaller. Am i doing smth wrong?
i feel grateful anyway!!

1. AnalystCave says:

check the query parameters most probably yours are different in the browser and in the Excel query e.g. car vs. bike, avoiding tolls, highways etc.

16. Thank you for sharing this nice solution

I use it and it work perfectly until yesterday. Now it gives me only 0. I don’t understand why.
I think that is because google changes his programmation. Somebodyelse have same problem ?

Before it crashed, I noted problems like ifotiad : little differences between manual and excel estimations. Where do you change query parameters in the code ?

1. AnalystCave says:

Did you check the restrictions? Checkout the bottom of the post where I mention the restrictions Google puts on the usage of this query (limited amount of queries a day etc.). Let me know if this helps.

17. and now it’s working…
Don’t understand informatic sometimes…

thank you

1. AnalystCave says:

Again – I encourage you to checkout the limits of using the Google query.

18. I’m novice in VBA (less than one week) so I don’t understand some things. When cells dependent to the function refresh ? Because I have more than 50 queries and if it refresh automaticaly at same time, it can explain why sometimes, all datas become 0.

I have an other question, I probably missed something, but where in the code you change “PARAMETERS FOR CALCULATING THE GOOGLE MAPS DISTANCE”.

1. AnalystCave says:

I’m novice in VBA (less than one week) so I don’t understand some things. When cells dependent to the function refresh ? Because I have more than 50 queries and if it refresh automaticaly at same time, it can explain why sometimes, all datas become 0.

Cells refresh when opening / closing / saving / recalculating / modifying a cell. Automatic recalculation refreshes so called “dirty” cells – e.g. cells for which dependent parameters have been modified (being other cells) or if for some other reason Excel decides to refresh them. If you modify a parameter of all 50 queries I would expect they will refresh instantly in Automatic calculation mode. I personally prefer more control and have a macro that goes through the list of locations/coordinates. This way I can control when and how often these queries will occur.

I have an other question, I probably missed something, but where in the code you change “PARAMETERS FOR CALCULATING THE GOOGLE MAPS DISTANCE”.

This is done when building the URL. Look through my code for this part. See the mode, car and sensor?

```firstVal = "http://maps.googleapis.com/maps/api/distancematrix/json?origins=" secondVal = "&destinations=" lastVal = "&mode=car&language=pl&sensor=false"```

19. Ok, thank you for your help !

About the parameters, you should give some exemple of the url changes. Because I see where you can change car to bike, but where do you add avoiding highway or others parameters ?

1. AnalystCave says:

Thx for the tip. I was sure this was straightforward looking at the other GET params.

To add params simply append the params to the url preceded with a “&” e.g. add &avoid=highways to the URL

20. This made my week. Month? Summer? I have to calculate distances between ~30,000 coordinate sets, and after reading this I had it working in a few minutes. Suddenly not a scary task! Thank you so much.

21. This has worked great for me until this morning where it parses -1 all the time now.

Haws the Google API changed. doubtful but i just cant get it to work now!
Is there a way to force it to calculate again?
Thanks

1. AnalystCave says:

Couple of common issues to check:
1. You must have a direct internet connection (no office proxy as the XMLHttpRequest object is not configured to handle one)
2. You may have overrun the Google API limit if you tried recalculating a lot of these functions at once (see the section on the usage limits)

Let me know if this is none of the above.

1. AnalystCave says:

Hi Dirk,

sure you can. Simply add a few lines with a Dictionary object where the keys are strings named by destination and starting point e.g. in a format “[Start address][End Address]”. Then the function instead of querying Google Distance Matrix API should first verify if there is an existing connection in the Dictionary.

This will still need to recalculate when you close & reopen your Workbook as VBA project data will be lost. If you want your calculations to be done once you have at least two options:
1. Save your Dictionary to a binary file on Workbook.BeforeClose event. Load it on Workbook.Open event.
2. Instead of running the function as a UDF (an Excel formula) run it via VBA procedure (one-off calculation).

22. Hello,

if I run the VBA-Code, it says, that the “Argument is not optional” in this line:
URL = firstVal & Replace(start, ” “, “+”) & secondVal & Replace(dest, ” “, “+”) & lastVal
Have you an idea what the problem is?

Thanks

Philipp

1. AnalystCave says:

Hi Philipp, seemed liked indeed I got an error when running the code (I admit to making a minor tweak recently). However, it turned out to be in another line than you suggested. I ran both procedures (GetDistance and GetDuration) and they returned both correct results.

Try rerunning the code and see if it works for you too. Otherwise the error you suggested (Argument not optional) happens when a VBA procedure is provided with too little arguments – in this case Replace requires 3 arguments. However in the line you suggested it doesn’t seem like any procedure is missing an argument.

1. AnalystCave says:

Thanks! I will be following up with other useful Web API soon so be sure to follow me / subscribe!

23. Hi, this script is fantastic… But i’m Looking for a solution for run It in my office where is a proxy, any idea?

Thanks, Alex

24. I am trying to get the code to work but it always comes back with #value! as the error. Any thoughts what might be happening?

Thank you,

Tony

1. AnalystCave says:

Hi Tony,

yes this can happen if you are executing the script via proxy (see this section). You need a direct internet connection.

Otherwise try maybe debugging the script (F8 in VBA Project) line by line and share the line in which you are getting an error.

1. AnalystCave says:

Hi Tony, glad it started working. It calculates in meters not in miles. Feel free to execute the code from VBA and copy the URL variable to your browser you will have the direct output from Google Distance Matrix.

Change the “language=pl” to e.g. “language=en” or “language=en-US” of the Google Query in VBA. I believe the language abbreviations are correct otherwise – see full list of country abbreviation here. This should change the values from meters to miles or other US metric.

25. Hi this is a really wonderful tool. Thanks for putting this together.

Do you know what the output duration is? I’m curious if this is average duration time or it is calculated differently (including traffic or not). I see that real-time duration data (including traffic) seems to be reserved for Google Maps API for Work customers only.

Thank you!

1. AnalystCave says:

Hi Charles,
sorry for finding your comment late. Glad it helped. Unfortunately this is the only thing shown on the official Google Distance Matrix API page:

The length of time it takes to travel this route, expressed in seconds

If you do find out – be sure to share.

26. I signed up for a Google Maps API key, but every time I add it to the “lastVal” line it creates an error.
lastVal = “&mode=car&language=en-US&sensor=false&key=###########”

Is there somewhere else I should be adding it?
Thanks, Tom

1. AnalystCave says:

Hi Tom,
frankly I don’t use a API key at all (notice my examples lack the key). The key is needed if you subscribed to Google API for a Premium Usage Limit or want to use the Pay as you go service. Try removing the key and see if the query link works (generates a proper JSON result). If it’s just the key Contact Google Sales for help.

Let me know!

2. Jim Bean says:

Hi Tom,

Did you ever figure out how to use the key as well? I need to make over 40,000 calls to the service which will be quickly blocked I am sure as I exceeded the 2500 hits in less than a minute. So I too also signed up for the pay as you go and I put my key # in the same location you referenced and I get -1 values as my result.

I am open to thoughts and suggestions.

Thank you

1. Hi Jim, to use the key you need to create a account at Google – see the links in my post. Alternatively you can use proxy servers (there are free ones you can find online) – read my post on proxy servers.

27. Hi

i am trying to use your code within my excel sheet. I have activated MS XML 6.0 in VBA.
But nevertheless the code is rather unstable. I am calculating distance for three different start and destination sets and after some time the system gives me #Value error.

I have searched through your forum but did not find anything to resolve this problem. Kindly help.

Regards
Amit

1. No i dont think so since the system starts responding after a few minutes before it goes into the sleep mode again. When you refer to 100 elements per query, do you mean 100 requests of get distance. As i said my program asks for only 3 values.

Thanks
Amit

1. AnalystCave says:

Create a thread on my forum and post your code there – I will have a look.

28. Hi,
The code is awesome, however I could calculate the distance only between two places (“New York”; “Las Vegas”).

Indeed, if I try to include one more place (“New York”; “Las Vegas”;”Miami”), I’ll get back an error…

How could I resolve it?

Tnks so much!

1. AnalystCave says:

Hi Alessandro,
great idea! Updated my post to accommodate distance/duration between multiple places. See post above.

29. Great idea!
Thank you!

There is just one more thing whom I wish to be fixed as soon as possible….

If I use the standard web-based google maps service, and I search for a place, I’ll get the one (in line with the name that I write…) closest to the starting point that I wrote.

However, your useful script do not use the same procedure!

Indeed, looking for the distance between a place and another one whom have the same name of many others, the result could be wrong!

There is a way to fix it?

Tnks

1. AnalystCave says:

Hi Alessandro,
the Google Maps services is more complex than the simple Google Distance Matrix API. I am assuming that it is possible to use other Google API to achieve what you are suggesting. It would probably be quite a complex procedure, hence I am not planning currently to explore these options. What is more I think it is not a good practice to assume that Google will always get the location right if you are not providing it with sufficient details (address or coordinates).

But I certainly recommend looking into Google API and looking for some ideas to tackle this issue: https://code.google.com/apis/explorer/. Let me know if you do manage to solve this somehow!

30. hi,

I love your code but i’m not that good with this kind of things.
Wat should i change to get km instead of meters and go from car to bike?
thank you so much for posting this you are making my live a lot better.

1. AnalystCave says:

Hi Magellan,
1000 m = 1 km. Simply divide by 1k to get km. To get results for bike use my Google Distance Matrix Builder above or add “&mode=bicycling”. Hope that helps

31. Hi, I’ve received a “Compile error: Variable not defined”

-> Set objHTTP = CreateObject(“MSXML2.ServerXMLHTTP”)

Also, we’re unable to move on to IE 11 as some of our older apps can only work on IE 8.

Is it possible to change the code to use Chrome or Firefox and bypass the use of IE 8?

Cheers!

32. Hello together
i need this VBA-skript for MAC Excel2011, can you help me, please?
Thank you very much!!!
Daniel

Public Function Entfernung _
( _
Start_Strasse As String, Start_Stadt_PLZ As String, _
Start_Bundesland As String, Start_Staat As String, _
Ziel_Strasse As String, Ziel_Stadt_PLZ As String, _
Ziel_Bundesland As String, Ziel_Staat As String _
)

Dim surl As String
Dim oXH As Object
Dim bodytxt As String

Replace(Start_Strasse, ” “, “+”) & “+” & Replace(Start_Stadt_PLZ, ” “, “+”) & “+” & _
Replace(Start_Bundesland, ” “, “+”) & “+” & Replace(Start_Staat, ” “, “+”) & _
“&destinations=” & _
Replace(Ziel_Strasse, ” “, “+”) & “+” & Replace(Ziel_Stadt_PLZ, ” “, “+”) & “+” & _
Replace(Ziel_Bundesland, ” “, “+”) & “+” & Replace(Ziel_Staat, ” “, “+”) & _
“&mode=driving&sensor=false&units=metric”

Set oXH = CreateObject(“msxml2.xmlhttp”)

With oXH
.Open “get”, surl, False
.send
bodytxt = .responseText
End With

bodytxt = Right(bodytxt, Len(bodytxt) – InStr(1, bodytxt, “”) – 5)
bodytxt = Right(bodytxt, Len(bodytxt) – InStr(1, bodytxt, “”) – 6)
Entfernung = Left(bodytxt, InStr(1, bodytxt, “”) – 1) / 1000

Set oXH = Nothing

End Function

33. Hello,
I am a completely new user of VBA. I found your website searching for the information regarding measuring the distance between coordinates based on GPS mode. In my opinion you do a great job!
I managed to copy and activate the code you pasted for distance between two or more adresses. And it worked. Unfortunately I am not able to: “get Google Maps distance between two coordinates”.
I would be gratefull for your kind help :)
Thanks and best regards,
Marcin

34. Thanks so much for this code! I am novice at VBA, but have it working. I have read through all the posts, and searched code for the parameter to change from meters to miles and cannot figure it out. Could you share how to make this change in the code? Thanks

1. Hi Shelli, glad it helped. Simply multiply the km assuming 1 km = 0.621371 mile. Or 1 m = 0.000621371 mile.

Google does allow you to change the units in the query (changing the text response approximated to miles) but provides the detailed value response still in meters. Hence it makes more sense simply to multiply the km or m to convert to miles by self.

35. This is a great little snippet of code. I’m currently behind a corporate firewall but will play with duration when I get home. Is there a way to set a departure or arrival time, instead of just using the current time? Further to this, is it possible to opt for the options ‘best guess’, ‘optimistic’, and ‘pessimistic’?

Thanks!

36. Hello!
I used your code to get distances and it worked perfect! However when I use the code for duration it always comes back with #value!
It is weird that my internet connection is working with one code but not with the other, do you know why this might be happening and what can I do to solve it?

Thank you!

1. I had created a new Module for the duration code and then I tried again putting the code in the same Module as the distance code and it worked. For some reason that first thing didn’t worked. Anyway, thank you very much!

1. Glad it worked ;). Do subscribe for updates. Will be planning to most more on Google API and Web Scraping in coming weeks.

37. Hello,

I’m a complete novice with VBA and am having some issues with your code. I’m using this code specifically for calculating distance between locations in the UK. The issue I’m having is that when I try to calculate the distance between towns or cities in the UK where there is a place with the same name in the US, (such as Cambridge), it defaults to the US and throws up an error as it obviously cant find a route from the US to the UK. I can get around the problem by typing +UK at the end of my inputs, but the spreadsheet has other functionality reliant on my not typing +UK at the end – I essentially need to force the VBA function to always add “+UK” to the end of the inputs, how do I do this?

38. Hello, loving the VBA coding, I am thinking of trying to analyse commuter distance/time using home address and work location, and I think this could be extremely useful.

One point which I didn’t see mentioned above, and it will likely be to do with Google Maps and not your coding (or it could be an issue with me), but =GetDistance(“Chicago”; “New York”) results in a different answer to =GetDistance(“New York”; “Chicago”), not massively, 1270085 compared to 1271382, but it probably is something to note, as with a few other tests it comes back with varying answers when flipping the destinations around.

Many thanks
Andrew

1. Hi Andrew,

yeah this does seem to concern Google’s algorithm. But anyway it makes sense for me as you can’t always return via the same route both ways. Even trains often can have different trips both ways.

39. Cool stuff but I am running Excel 2011 on a Mac and whenever i call the function Getdistance I am getting the #value! error code back. Any ideas?

40. Just dusted off my PC and it works fine there. Doesn’t seem like this is supported in Excel 2011 for the MAC.

41. Nope. The ServerXMLHttp request object is not available on Mac. What is more there is nothing remotely close to that. Only option to use QueryTables like this:
``` With ActiveSheet.QueryTables.Add(Connection:="URL;http://somewebsite.com/somepage", Destination:=Range("A1")) .PostText = "origins=Chicago&destinations=New+York&mode=driving&language=en" .RefreshStyle = xlOverwriteCells .SaveData = True .Refresh End With ```

42. Hello,

This is some incredible stuff! I love it! However, I’m having an issue when it comes to longer distances. Whenever I try to get the distance from say, Seattle to Chicago, I only get a response back of 2. But whenever I put in something closer, say Naperville, IL to Chicago, it works just fine. And I get the same results whether I use an address, a zip code, or just the name of the city.

Any ideas what is causing this?

Thanks!

```Debug.Print matches(0).SubMatches(0)
```

after/before this one:

```tmpVal = Replace(matches(0).SubMatches(0), ".", Application.International(xlListSeparator))
```

and post a new thread on my Forum (VBA) so we don’t junk this post with comments.

43. First of all, thanks. I could have never done this myself. Second, a FYI for the other folks- apartment numbers seem to screw up the mapping calculation, so clean the data of them if you can.
Scot

44. I got it to work, despite getting a #NAME error at first. I solved this by creating a Add In and then using it that way. That seemed to solve the problem, though I don’t know why. It wouldn’t recognize the UDF any other way.

But it still doesn’t quite do what I want it to. I need to fetch the longest driving distance. Google maps gives three options of driving distance and I need the longest one.

Is there a parameter or some way to return that value?

I am creating an invoice for my process serving (yes, I serve legal papers) and I charge mileage that is the furthest distance as calculated by Google Maps. That is in my contract (it allows for driving around to find someone on obscure addresses)

Its a pain switching between windows and doing this all manually. I want my invoice spreadsheet to do it automatically.

This is sooooooo close though! Thanks!

Maybe you can help me get what I need from this. It seems like a tiny tweak.

Thank for you help!

45. Please help! Is there a vba code that will calculate the mile or kilometers driven in each state passed through? For example, if traveling from Detroit, MI to St. Louis, MO =
MI=53.09
OH=91.53
IN=248.17 …

1. Not out of the box. You could potentially use Google Maps Directions API to generate the necessary coordinates. Then translate coordinates to states. And lastly use Google Distance Matrix to measure distances between coordinates and add them to necessary state. This will be quite a lot of work. But perfectly feasible. Reach out to me if you want a quote

46. Hi – First of all, thanks for sharing it. It’s a great tool.

However for some reason it has stopped working and now it returns -1 in all cases.

Thanks,

47. Hi,

This is genius.

I registered to say thank you,

great job, keep it up :)

1. Hi Karlo appreciate the kind words. Glad it works for you. Be sure to subscribe to my Weekly Newsletter or one of my Social accounts for latest posts!

48. Hi!  Can anyone help me with this problem.

I want to edit the code so that it provides me with distance state wise.  For eg if I am traveling from California to Dallas! Then I want it to show me total distance in California traveled then depending on the route distance in that state traveled till I reach Dallas.

Thanks

!

49. Having trouble reading in values from a cell when calling MultiGetDistance with lng and lat coords.

If I call using MultiGetDistance(“=A1,B1″,”=A2,B2″,”=A3,B3”)

or

GetDistance(“=A1,B1″,”=A2,B2”)

I get a #VALUE error. With A1 and B1 being longitude and latitude integer values.

Great code for helping me get into VBA, thanks for any help

50. If I type

=MultiGetDistance(“30.36338397,-97.739843″,”31.08381182,-97.3639436″,”30.38730324,-97.7062796”)

I get the desired result, but I am looking to simplify the process so that I am able to type

=MultiGetDistance(“A1,B1″,”A2,B2″,”A3,B3”)

Any help is greatly appreciated

51. I have thousands of addresses in an Excel file that I need to calculate the distance. Can the VBA codes be modified to process a batch of addresses instead of inputting them one by one? For example, I need to calculate distance between addresses in Column A and column B.

Many thanks!

52. Thanks for your brilliant code!
An idea;
Is it possible to ‘get’ both distance and duration in a single call / single function?

This might halve the requests to the api and then it would be possible to just extract each value from the cell?

The json that comes back on googles api page example appears to have both so i think it might be possible ?

1. I did a bit more reading of the pricing page @ google for the api and calls are per ‘element’, so my suggestion won’t change the cost as duration and distance are each an element
thanks

53. Thanks for sharing the code. It is really helpful.

I tried to bypass google restriction by the use of HTTP proxies (code as follow), but keep getting  #VALUE!

objHTTP.Open “GET”, URL, False
objHTTP.setRequestHeader “User-Agent”, “Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)”
objHTTP.setProxy 2, “##.##.##.##:80”
objHTTP.send (“”)

I also tried Google API key, but still getting -1

lastVal = “&mode=car&language=en-US&sensor=false&key=###########”

Any help greatly appreciated.

1. Hi

Thanks for the great post. I had trouble with adding the API key too, was getting VALUE=-1 but then when I looked at the HTTP response it was giving an error because it was not using HTTPS. I changed firstVal from http://… to https://… and it started working – in case anyone else is having that problem.

54. For anyone needing miles instead of meters….

55. Thanks a lot AnalystCave for posting this VBA! I had just been looking for a way to automatically figure out mileage between locations to include in my employees time sheets.

J majerus – thanks for posting that update for miles. For me though it’s dropping anything after the decimal. So far dividing meters by 1609.344 is giving the most accurate results for a miles conversion.

What I’m not understanding is when you do an HTML direct input with the language set to “en” and units set to “imperial” it provides the correct information, except in the “text” line.

So if I enter this directly:
`https://maps.googleapis.com/maps/api/distancematrix/json?origins="756horatiostreet,utica,ny13502"&destinations=8555senecaturnpike,newhartfordny13413"&mode=car&language=en&units=imperial&sensor=false%22`

I get text 8.7 mi and value 13930

In the Excel VBA no matter what parameters I set for units and language the results are always 13930.

Any ideas on how to fix that and get the direct mileage results from google?

BTW, the google maps API doc says this:
* Note: this unit system setting only affects the text displayed within distance fields. The distance fields also contain values which are always expressed in meters.

56. Hi,

Love this bit of code it has proved really useful so thank you for putting it together.

I am however having an unusual issue which i couldn’t see anyone else having so hoping someone can point out maybe what might be causing it.

The problem is that when I use the code to calculate the direct distance between two coordinates when based in the UK I get one sensible number but when I go abroad and use it in some countries I get a total different unrelated number.

e.g. Distance between London, UK and Brussels, Belgium

When in the UK distance = 313km

When in Belgium = 16269km

Any suggestions would be appreciated

Here is the code as i have it in Excel.