Wouldn't it be useful if...

[whiteghost]

google earth? er no....sorry routemaster... UK only best solution buy a map

 

[mnordeen]

I have been trying to do this and there is a couple ways to do it but they are way too complicated, does anyone have a simple solution?

 

[Atroxell]

If you have 2 lat/lon points, you can use the formula below. Everything I have found shows that it is reliably accurate. Of course there is probably some variable that needs to be accounted for that makes it perfect, but this works well enough for my purposes:

=ACOS(COS(RADIANS(90-Lat1))    *COS(RADIANS(90-Lat2))   +SIN(RADIANS(90-Lat1))    *SIN(RADIANS(90-Lat2))   *COS(RADIANS(Long1-Long2)))*3958.756

 

[05newtothis]

Thats funny...not the kidney-bladder thing the Dunkin' Donuts!! Got me vote!

 

[TinaP]

What I would really like to see is the ability to enter the two zip codes, plus a third cell with a brief description of my urologists diagnosis of my kidney-bladder functions, and have Excel calculate how many pit stops will be needed on the journey. Then, upload it to Mapquest and have it show little urinal icons at all the available Dunkin' Donuts along the route.

Possibly the most useful function ever suggested. Although urinals fail me due to anatomical differences.

 

[RobMatthews]

Possibly the most useful function ever suggested. Although urinals fail me due to anatomical differences.

At the risk of being the one to "go there"; maybe you've just gotta ... Nah, on second thoughts, I'll just let that go.

 

[diddi]

i wrote a function that does what you want using the googlemaps API. its fast and accurate for actual road distance rather than lat/long triangulation

 

[mnordeen]

Well I found this, I didn't write it but it works. its in KM but that is an easy conversion if you want Miles.

You need to enable the Reference; In VBA Tools/Reference Select "Microsoft XML,V6.0"

in Cell say C1 use

=G_Distance(A1,B1)

< type the address or zip to/from in A1 and B1.

Function G_DISTANCE(Origin As String, Destination As String) As DoubleDim myRequest As XMLHTTP60
Dim myDomDoc As DOMDocument60
Dim distanceNode As IXMLDOMNode
    G_DISTANCE = 0
    On Error GoTo exitRoute
    Origin = Replace(Origin, " ", "%20")
    Destination = Replace(Destination, " ", "%20")
    Set myRequest = New XMLHTTP60
    myRequest.Open "GET", "http://maps.googleapis.com/maps/api/directions/xml?origin=" _
        & Origin & "&destination=" & Destination & "&sensor=false", False
    myRequest.send
    Set myDomDoc = New DOMDocument60
    myDomDoc.LoadXML myRequest.responseText
    Set distanceNode = myDomDoc.SelectSingleNode("//leg/distance/value")
    If Not distanceNode Is Nothing Then G_DISTANCE = distanceNode.Text / 1000
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

I believe this can be modified to find public bathrooms.

 

[Gerald Higgins]

I for one would find such a function useful.

I'm happy to use Google maps to find the road distance between two places.

But from time to time I've needed to get data for many different journeys, and some function like this would save alot of time over looking them up individually on Google.

Diddi - would you care to share your method ?

mnordeen - I tried your solution but couldn't get it to work.
I'm using Excel 2007.
I'm afraid I'm not hot on VBA or UDFs, but I copied your code exactly as is, into a standard module, and the very first line is highlighted in red, I think it's having a problem with the "...my request..." section.
Any ideas ?

If I can get mnordeen's proposal to work, I am confident that I could get it to work repetitively for a long list of journeys.

 

[mnordeen]

Gerald,
Did you enable the reference?
Open excel, open VBA then from the tool bar at the top (Tools)/(Reference Select) "Microsoft XML,V6.0"
I have it working on 2003 and 2007, it works like a champ.. B>)