Calculate Distance between Zip Codes

[mgirvin]

Dear Smartest Exclers In The World,

I have a table with row headers as source zip code and column headers as destination zip codes and I would like to calculate the distance between the two zip codes. Does anyone know how to do that, maybe a User Defined function or something?

 

[mgirvin]

Thank you both for your help!

 

[stlpony]

Hello, trying to run the function in this thread ...

Have Zip Codes in Column A, and Column B ... Have = ROUND(g_distance(A2,B2),2) in Column C.

I've added a reference in the VBA tools menu, as described above.

However, when I run the code, nothing happens. Does this still work?

Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim 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) * 0.62137119 'convert from km to miles
exitRoute:
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function

 

[SpillerBD]

The macro works on a street addresses primarily.
For ZIP code use, include the State
ie "TN 38111"
"NV 89121"

Those must be valid combinations.

 

[stlpony]

The macro works on a street addresses primarily.
For ZIP code use, include the State
ie "TN 38111"
"NV 89121"

Those must be valid combinations.

Gotcha. So change column A to include The state with the Zip Code in the format above?

Thanks, @SpillerBD

 

[Taul]

Hi,
Just as I was about to post, SpillerBD got there first and sorted it.
Just to say, I have tested again between your zip codes and got 2547.5 Km, 1582.94 miles, 22.56 hours
still works on UK post codes too.

Just to say, the T&C on the maps require you to also use a map rather than just the function on its own, otherwise Google may pull the plug.

cheers
Paul.

 

[Taul]

Hi,
In case you need it, the source of my data is here, its worth a read.
http://oco-carbon.com/coding/distance-function-google-excel/

In the top right of the page, there are some links, the 5th one down is for the time function.

cheers
Paul

 

[stlpony]

Alright, I'm determined to make this work.

Cell A2: TX 75204 .... Cell B2: MO 63111 ... Cell C2: =ROUND(g_distance(A2,B2),2)


Getting Error: Ambiguous Name detected: G_DISTANCE

I have copied and pasted into a new module:

Function G_DISTANCE(Origin As String, Destination As String) As Double
Dim 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) * 0.62137119 'convert from km to miles
exitRoute:
Set distanceNode = Nothing
Set myDomDoc = Nothing
Set myRequest = Nothing
End Function


When I hit play, it keeps requiring me to make my macro and doesn't just run the function.

Any tips @Taul or @SpillerBD ?

 

[SpillerBD]

"When I hit play..." ??
You've created a function. Use it like any other Excel Function, though you may want to pull it from the function list under User-Defined.
My exact code in a module I have follows.

Public Function G_DISTANCE(Origin As String, Destination As String) As Double
'This FUNCTION must be copied to actually Workbook modules
'Also the Reference (under Tools) "Microsoft XML. v6.0" must be activated
Dim 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) * 0.62137119 'convert from km to miles
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

edit.
The reason you do want to go through the function chooser is the place where the function is called must also be defined. So its not just "G_Distance" but

=ROUND(Personal.xlsb!G_DISTANCE(B1,C1),2)

 

[stlpony]

"When I hit play..." ??
You've created a function. Use it like any other Excel Function, though you may want to pull it from the function list under User-Defined.
My exact code in a module I have follows.

Public Function G_DISTANCE(Origin As String, Destination As String) As Double
'This FUNCTION must be copied to actually Workbook modules
'Also the Reference (under Tools) "Microsoft XML. v6.0" must be activated
Dim 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) * 0.62137119 'convert from km to miles
exitRoute:
    Set distanceNode = Nothing
    Set myDomDoc = Nothing
    Set myRequest = Nothing
End Function

edit.
The reason you do want to go through the function chooser is the place where the function is called must also be defined. So its not just "G_Distance" but

=ROUND(Personal.xlsb!G_DISTANCE(B1,C1),2)

It works!!! Wow this is a crazy awesome. @SpillerBD you're the man.

 

[stlpony]

Now it's not working again. and is showing #Name . Think there is something I'm still not understanding here.