• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Find Distance between two places by passing Latitude and Longitude

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)

' Read more about Google API’s here
'https://developers.google.com/maps/documentation/distancematrix/

' we will be using Google API Distance Matrix to get Time and distance between two cities.
'https://developers.google.com/maps/documentation/distancematrix/

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)


Download Working File Here https://www.box.com/s/x2ofy9r8cc7bro0p83xq
 
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.

https://chandoo.org/forum/threads/power-map.31577/#post-187726
 
Top