# Find Distance between two places by passing Latitude and Longitude

#### ashish koul

##### Member
If you want to find the distance and time taken between tow places by passing the Latitude and Longitude of Origin and Destination Via VBA Excel and Google Map.

Here is the UDF

Code:
``````Public Function get_dis_and_time(lat_1 As String, lon_1 As String, lat_2 As String, lon_2 As String)

' we will be using Google API Distance Matrix to get Time and distance between two cities.

Dim surl As String
Dim oXH As Object
Dim bodytxt As String
Dim tim_e As String
Dim distanc_e As String

surl = "http://maps.googleapis.com/maps/api/distancematrix/xml?origins=" & lat_1 & "," & lon_1 & _
"&destinations=" & lat_2 & "," & lon_2 & "&sensor=false"

Set oXH = CreateObject("msxml2.xmlhttp")

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

bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)

tim_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)

bodytxt = Right(bodytxt, Len(bodytxt) - InStr(1, bodytxt, "<text>") - 5)
distanc_e = Left(bodytxt, InStr(1, bodytxt, "</text>") - 1)

get_dis_and_time = tim_e & " | " & distanc_e

Set oXH = Nothing
End Function``````
In cell A3 = Origin Latitude
In cell B3 = Origin Longitude
In cell C3 = Destination Latitude
In cell D3 = Origin Longitude
In cell E3 Type =get_dis_and_time(A3,B3,C3,D3)

Last edited by a moderator:

#### Kathy Siskoy

##### New Member
When I use Google Maps and enter the information, it returns different values than what the code above does. Sometimes by several miles.

#### Chihiro

##### Excel Ninja
It's travel distance over road (or other mode of transportation). So each time you query in browser, it can be different, depending traffic condition etc.

When you query using above code, many of optional values are not set and info isn't real time.

If you want to calculate approximate distance over surface in straight line (i.e. great-circle distances) using lat & long, then use Haversine formula to calculate the distance.