Option Explicit
Function GCDist(strStartAddr As String, strStartCity As String, _
strStartState As String, strEndAddr As String, strEndCity As String, _
strEndState As String) As Double
'
' by Jimmy Pena, 4-2-2008
' http://www.codeforexcelandoutlook.com
'
' uses formula from http://www.cpearson.com/excel/latlong.htm

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

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>") + 10
LastPos = InStr(xmlSite.responseText, "</Latitude>") - 1

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

Set xmlSite = Nothing

End Function

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>") + 11
LastPos = InStr(xmlSite.responseText, "</Longitude>") - 1

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

Set xmlSite = Nothing

End Function