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.

**Table of Contents**hide

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

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

- Later, type the following codes in the window-

```
Public Function Calculate_Distance(start As String, dest As String, Alink As String) As Double
Dim first_Value As String, second_Value As String, last_Value As String
Dim mitHTTP As Object
first_Value = "http://maps.googleapis.com/maps/api/distancematrix/json?origins="
second_Value = "&destinations="
last_Value = "&mode=car&language=pl&sensor=false&key=" & Alink
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.

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

**How to Calculate Distance between Two GPS Coordinates in Excel****Calculate Coordinates from Bearing and Distance in Excel****How to Calculate Mahalanobis Distance in Excel (Step by Step)****Calculate Miles between Two Addresses in Excel (2 Methods)****How to Calculate Manhattan Distance in Excel (2 Suitable Ways)****Calculate Levenshtein Distance in Excel (4 Easy Methods)**

sorry but this canâ€™t work. Your VBA code accepts only 2 arguments, and you use 3 arguments.

full of errors.

Hello JAN T,

Hope you are doing well. I think by following the below-stated procedures you can make your code work.

â€¢ After going to your

VBEwindow, go to theToolstab >>Referencesoption.Then, the

References – VBAProjectwindow will appear.â€¢ Check the following options.

o Microsoft Scripting Runtimeo Microsoft WinHTTP Services, version 5.1â€¢ Press

OK.â€¢ Now, type the following code.

Make sure to add a

third argumentin your code and use it in the indicated place.â€¢ Finally, go to your sheet and use the following function.

One thing to mention is that make sure to use a valid API address, otherwise, you will get an error.

Regards

Tanjima Hossain

I need code for three locations. For example the distance from your starting point to the 1st location and then the distance from the 1st location to the 2nd location. So that would be 3 different distance fields to be included in the total distance calculation. Can you show an example of that please? Thank you so much.

Hi D KELLY,

Thanks for reaching out! I’d be happy to help you with your code and provide a solution. To solve this issue, we built a procedure called GetLatLong and a user-defined function named GetTotalDistance3Locations using VBA. You can input your desired locations in the GetLatLong procedure. The model calculates the total distance for three locations. Please click the link underneath this section to get a copy of the illustration workbook.

https://www.exceldemy.com/wp-content/uploads/2023/01/Total-Distance-Calculation-for-Three-Locations.xlsm

Best regards,

Lutfor Rahman Shimanto

(ExcelDemy Team)

Does it still work in 2023? I made an API but it doesnt work.

Hello

SAMIRThanks for reaching out and posting your query. Regarding your question, I can assist you with a

User-definedfunction programmed inExcel VBAthat will take three arguments. Among these arguments, the first and second will be theLatitudeandLongitudeof the start and end location. And the third argument must be anAPI Key. I am giving you anAPIfor demonstration which should work. However, you may use yourAPIas well as the third argument. I am attaching theWorkbookused to investigate the described issue.Excel VBA Code:OUTPUT:WORKBOOK:SOLUTION WORKBOOK

The

User-definedfunction andAPIwill meet your requirement. Don’t hesitate to contact us again with any other inquiries or concerns.Regards

Lutfor Rahman Shimanto