How to Calculate Distance in Excel with Google Maps

Excel has a wide range of applications. And while using VBA then it seems that we can do whatever we want in Excel. So of course, we can find the distance between places using a map in Excel. In this article, I’ll show a quick guide to calculate the distance in excel with Google Maps with sharp steps and clear illustrations.


Download Practice Workbook

You can download the free Excel workbook from here and practice on your own.


Using a User-Defined Function to Calculate Distance in Excel with Google Maps

Here, we’ll find the distance between MacArthur Park and Jersey City using Google Maps.

First, we need to know an important thing. To calculate the distance in Excel using Google Maps, we will need an API key. API stands for Application Programming Interface. Excel connects with Google Maps using the API key for collecting required data. Some maps provide free API keys like Bing Maps. But Google Maps doesn’t provide free API. Although you manage a free API somehow that will not work perfectly. So, you will have to buy the API key from this link.

Here, I have managed a free API key. It doesn’t work properly, just used to show as an example. We’ll use VBA to create a user-defined function named Calculate_Distance to find the distance. It will have three arguments- Starting Place, Destination, and API key. Now let’s start the procedures.

Steps:

  • Press ALT + F11 to open the VBA window.

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

  • Next, click as follows: Insert > Module to create a new module.

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

  • Later, type the following codes in the window-
Public Function Calculate_Distance(start As String, dest As String)
Dim first_Value As String, second_Value As String, last_Value As String
first_Value = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
second_Value = "&destinations="
last_Value = "&mode=car&language=pl&sensor=false&key=YOUR_KEY"
Set mitHTTP = CreateObject("MSXML2.ServerXMLHTTP")
Url = first_Value & Replace(start, " ", "+") & second_Value & Replace(dest, " ", "+") & last_Value
mitHTTP.Open "GET", Url, False
mitHTTP.SetRequestHeader "User-Agent", "Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.0)"
mitHTTP.Send ("")
If InStr(mitHTTP.ResponseText, """distance"" : {") = 0 Then GoTo ErrorHandl
Set mit_reg = CreateObject("VBScript.RegExp"): mit_reg.Pattern = """value"".*?([0-9]+)": mit_reg.Global = False
Set mit_matches = mit_reg.Execute(mitHTTP.ResponseText)
tmp_Value = Replace(mit_matches(0).Submit_matches(0), ".", Application.International(xlListSeparator))
Calculate_Distance = CDbl(tmp_Value)
Exit Function
ErrorHandl:
Calculate_Distance = -1
End Function
  • Then nothing, just go back to your sheet.

Using a User-Defined Function to Calculate Distance in Excel with Google Maps

Code Breakdown:

  • First, I used a Public Function procedure Calculate_Distance.
  • Then declared some variables first_Value, second_Value, and last_Value for the arguments of our user-defined function.
  • Set the values for the variables (each value is self-descriptive), and set the mitHTTP object in ServerXMLHTTP to utilize the GET method (used later, this object property will allow using the POST method as well).
  • Url is the combination of all the values set earlier, the open property of the mitHTTP object used it.
  • After assigning the values library function does the rest of the calculation.

Now you see, our function is ready to use.

  • In Cell C8, type the following formula-
=Calculate_Distance(C4,C5,C6)
  • Finally, just press the ENTER button to get the distance. It will show the distance in the Meter unit.

Read More: How to Calculate Driving Distance between Two Addresses in Excel


Pros and Cons While Calculating Distance with Google Maps

  • You must have a valid API key.
  • The above code will give the output in the Meter unit.
  • The user-defined function uses place names directly, no need to use coordinates.
  • Make sure, you have used a valid place.

Advantages and Disadvantages of Calculating Distance with Google Maps

Advantages

  • For a large couple of places, it is quite feasible because we can use the Fill Handle tool to copy the formula. That is not possible in Google Maps
  • It’s a pretty faster way.
  • No need to use coordinates.

Disadvantages

  • It can’t work with coordinates.
  • You won’t get the map or route, just you will get the distance.
  • It won’t work with the approximate match of the place names.

Conclusion

I hope the procedures described above will be good enough to calculate the distance in Excel with Google Maps. Feel free to ask any question in the comment section and please give me feedback. Visit ExcelDemy to explore more.


Related Articles

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. Here I will post excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo