Latitude Longitude Functions


Working with distance, latitude and longitude in Excel

Driving Distance MapQuest UDF

    Here is a UDF for getting driving distance from MapQuest, based on cells in your worksheet.

    Let's say you have a starting address in cells A1 (street), B1 (city), C1 (state), D1 (zip), and the destination address is E1 (street), F1 (city), G1 (state), H1 (zip). This function pulls the address from the cells in Excel, creates an instance of Internet Explorer, passes the addresses to MapQuest and returns the driving distance to the target cell. If there is an error with MapQuest, it shows a friendly error message.

Update 10/31/08: MapQuest reorged their page and broke the UDF. I've updated the URL string, and also added a call to the Refresh Method. This is required to make the driving distance appear programmatically.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
'
' must set references to Microsoft VBScript Regular Expressions, Internet Controls
' & HTML Object Library before running this script
' based on http://www.vbaexpress.com/kb/getarticle.php?kb_id=386
Public Function GetDistance(startAddr As String, startCity As String, _
  startState As String, startZip As String, endAddr As String, _
  endCity As String, endState As String, endZip As String) As String

Dim sURL As String
Dim appIE As InternetExplorer
Dim regex As RegExp, Regmatch As MatchCollection
Dim BodyTxt As String
Dim GetFirstPos As Long

sURL = "http://www.mapquest.com/maps?1c=" & Replace(startCity, " ", "+")
sURL = sURL & "&1s=" & startState & "&1a=" & Replace(startAddr, " ", "+")
sURL = sURL & "&1z=" & startZip & "&2c=" & endCity & "&2s=" & endState
sURL = sURL & "&2a=" & Replace(endAddr, " ", "+") & "&2z=" & endZip

Set appIE = New InternetExplorer
'Set appIE = CreateObject("Internetexplorer.application")

appIE.navigate sURL
appIE.Visible = True

    Do
        DoEvents
    Loop Until appIE.readyState = READYSTATE_COMPLETE

appIE.Refresh

Set regex = New RegExp
With regex
    .Pattern = "Total Estimated Distance"
    .MultiLine = False
End With

BodyTxt = appIE.document.Body.innerText
Set Regmatch = regex.Execute(BodyTxt)

If Regmatch.Count > 0 Then
    GetFirstPos = WorksheetFunction.Find("Total Estimated Distance", BodyTxt, 1)
   
    GetDistance = Mid$(BodyTxt, GetFirstPos, 30)
 
    Else
    GetDistance = "Address Error, fix and try again"
End If

appIE.Quit
Set appIE = Nothing
Set regex = Nothing
Set Regmatch = Nothing

End Function

    This function should be used in a worksheet as follows:

    =GetDistance(A1,B1,C1,D1,E1,F1,G1,H1)

    Now note that it will return an error if you include suite or apartment numbers in the house number. Also if you include hash marks "#" it will fail. Also keep in mind you are opening Internet Explorer in the background and waiting for MapQuest to load, so there will be an approx 6 second delay between you entering the function and it actually returning a result. Also I recommend that after entering the function and getting the result, you either paste in the value or delete it and re-enter it if needed — the function is volatile and you will end up re-running the code inadvertently, which will slow down your computer to a crawl.

Great Circle Distance UDF using Yahoo API and XML

    Yahoo provides a nifty geocoding API which we can use to return latitude and longitude information, based on a given street address. Here I will show a simple UDF that uses XML to return this data to two string variables, which can be used in other VBA procedures or directly in the worksheet.

    I have previously posted some sample code showing how to get driving distance into Excel, namely

    The first thing I did was go to the following website and create a Yahoo API: Yahoo Developer Network and the website that explains how it works is here: Yahoo Geocoding API

    To construct the URL, I simply used the sample URL from their site (see below) and replaced the appid with the one I created using the link above. When you create your own appid, it should be placed in the URL below, between "appid=" and "&street". Then we can use function arguments to pass the additional parameters (city, state and zip).

http://local.yahooapis.com/MapsService/V1/geocode?appid=sGmf70bV34H6QgDaDk.tLer1VOJZEf.P7nZPi2V8MMmFDEQ_f30ByzwxgTI-&street=701+First+Ave&city=Sunnyvale&state=CA

    Then it is simply a matter of using the stock code from Website Parsing/Retrieval using XML and using text functions like Instr, Left$, Mid$ and Len to extract the latitude and longitude from the returned XML response.

    Remember to set a reference to Microsoft XML, v6.0 (c:\WINDOWS\system32\msxml6.dll on my system) in the VB Editor. See the Binding page for help setting up references to object libraries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
Function GCDist(strStartAddr As String, strStartCity As String, _
strStartState As String, strEndAddr As String, strEndCity As String, _
strEndState As String) As Double
'
' Jimmy Pena 4-2-2008
' http://www.codeforexcelandoutlook.com
'
' uses formula from http://www.cpearson.com/excel/latlong.htm with slight mod

Dim MyStartLat As String
Dim MyStartLong As String
Dim MyEndLat As String
Dim MyEndLong As String

MyStartLat = GetLatitude(strStartAddr, strStartCity, strStartState)
MyStartLat = CDec(MyStartLat)
MyStartLong = GetLongitude(strStartAddr, strStartCity, strStartState)
MyStartLong = CDec(MyStartLong)

MyEndLat = GetLatitude(strEndAddr, strEndCity, strEndState)
MyEndLat = CDec(MyEndLat)
MyEndLong = GetLongitude(strEndAddr, strEndCity, strEndState)
MyEndLong = CDec(MyEndLong)

With WorksheetFunction
    GCDist = Format(3958.756 * .Acos(Cos(.Radians(90 - (MyStartLat))) * _
Cos(.Radians(90 - (MyEndLat))) + Sin(.Radians(90 - (MyStartLat))) * _
Sin(.Radians(90 - (MyEndLat))) * _
Cos(.Radians((MyStartLong - MyEndLong)))), "####.##")
End With

End Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Private Function GetLatitude(strStreet As String, strCity As String, _
strState As String) As String

Dim sURL As String
Dim FirstPos As Long
Dim LastPos As Long

Dim xmlSite As XMLHTTP60
Set xmlSite = New XMLHTTP60

sURL = "http://local.yahooapis.com/MapsService/V1/geocode?" & _
"appid=sGmf70bV34H6QgDaDk.tLer1VOJZEf.P7nZPi2V8MMmFDEQ_f30ByzwxgTI-" & _
"&street=" & Replace(strStreet, " ", "+") & "&city=" & strCity & _
"&state=" & strState

xmlSite.Open "GET", sURL, False
xmlSite.Send

Do Until xmlSite.readyState = 4
Loop

FirstPos = InStr(xmlSite.responseText, "Latitude") + 9
LastPos = InStr(FirstPos + 1, xmlSite.responseText, "/Latitude") - 1

GetLatitude = Mid$(xmlSite.responseText, FirstPos, LastPos - FirstPos)

Set xmlSite = Nothing

End Function
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Private Function GetLongitude(strStreet As String, strCity As String, _
strState As String) As String

Dim sURL As String
Dim FirstPos As Long
Dim LastPos As Long

Dim xmlSite As XMLHTTP60
Set xmlSite = New XMLHTTP60

sURL = "http://local.yahooapis.com/MapsService/V1/geocode?" & _
"appid=sGmf70bV34H6QgDaDk.tLer1VOJZEf.P7nZPi2V8MMmFDEQ_f30ByzwxgTI-" & _
"&street=" & Replace(strStreet, " ", "+") & "&city=" & strCity & _
"&state=" & strState

xmlSite.Open "GET", sURL, False
xmlSite.Send

Do Until xmlSite.readyState = 4
Loop

FirstPos = InStr(xmlSite.responseText, "Longitude") + 10
LastPos = InStr(FirstPos + 1, xmlSite.responseText, "/Longitude") - 1

GetLongitude = Mid$(xmlSite.responseText, FirstPos, LastPos - FirstPos)

Set xmlSite = Nothing

End Function

    To use, you should have a starting address, city and state in separate cells (ex: A1:A3), and destination address, city and state in separate cells (ex: B1:B3) type =GCDist(A1,A2,A3,B1,B2,B3) in a cell and press Enter. You could also type the arguments directly into the parenthesis, for example:

=GCDist("123 Main St","Queens","NY","100 Main Blvd","Ames","IA")

    The first thing you may notice is just how much faster this is than the GetDistance() function above. Keep in mind, though, that even though it is much faster, less volatile and less prone to error, this function returns great circle ("as the crow flies") distances, where the GetDistance() function returns actual door to door distance (much more useful in my opinion).

    The first thing the function does is pass the arguments to the "real" functions, a series of functions declared Private so they are only accessible from the GCDist() function. i.e. they can't be found in the GUI. These functions, GetLatitude() and GetLongitude(), use the XMLHTTP60 object to open a request to the Yahoo website, pass the URL and grab the return text, then parse it for the latitute and longitude (respectively).

    These values are returned to the GCDist function, converted to decimal values (CDec), then passed to the formula (courtesy of Chip Pearson's website) which calculates the great circle distance.

    I use the Replace() function because if you notice in the sample URL there are plus signs where there are usually spaces in the street address. Since we can't have spaces in the URL, we replace them with plus signs wherever found.



Buy Chandoo's Excel Formula E-book Create Excel dashboards quickly with Plug-N-Play reports.