# Calculate the distance between 2 postcodes in miles



## Jaffabfc (Aug 26, 2015)

Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible? 

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks


----------



## easy2understandexcel (Aug 26, 2015)

Hi,
I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
I googled it and found a few places you can get it for free.

The trigonometry is a bit beyond me for the formula, but it looks like this will be a good starting point: link i found

Hope this helps!


Please check out my excel youtube channel  Excel 101


----------



## Jaffabfc (Aug 26, 2015)

easy2understandexcel said:


> Hi,
> I think you will want to get the Latitude and Longitude coordinates for each postal code, then it can be calculated by formula.
> I googled it and found a few places you can get it for free.
> 
> ...



if i do the longitude and latitude though wont that do the distance as a pigeon distance and not as in proper distance for people driving etc.

thanks


----------



## Comfy (Aug 26, 2015)

What you are asking is quite substantial.

To start you off you are going to want to look at either the BING maps API or Google Maps API.

Make sure you read the licensing details so that you are sure you are using them within the terms of service.

You then might need an understanding of javascript/JSON and/or XML.


----------



## MARK858 (Aug 26, 2015)

There was some code from Kyle123 which seems to work which was posted in a different forum but I am sure Kyle123 won't object to me re-posting it here and also below is an excerpt from his post giving brief instructions.



> I had originally posted a thread as to how to get this via the google API which is extremely simple, grabbing this and putting it into excel directly however breaches the terms of use.
> 
> Since then, I have discovered that Microsoft also offers a similar API, you do however need to sign up and get a key (here) and accept their terms of use.
> 
> ...



Distance is in Kilometers  and comment out Open Explicit if you use it.


```
Function GetDistance(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP


    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"
    Set re = New XMLHTTP

    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDistance>")
    End With

    GetDistance = Val(s(1))

End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String
    Dim re As XMLHTTP

    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=[COLOR="#FF0000"]YOUR_MS_KEY[/COLOR]"

    Set re = New XMLHTTP
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re
        s = Split(.responseText, "<TravelDuration>")
    End With

    GetTimeinMins = Val(s(1)) / 60

End Function
```

use as



> =GetDistance("PO144HZ","PO155TT")


----------



## bdeeve8 (Jan 24, 2019)

Hi, thanks for sharing this!

So I used this code ad the distance computed was zero (0). Any idea what could be wrong?


----------



## Scott Huish (Jan 24, 2019)

1. Did you get your API key and modify the code as indicated? https://www.microsoft.com/en-us/maps/create-a-bing-maps-key
2. What postal codes did you use?


----------



## bdeeve8 (Jan 24, 2019)

I did so. Modified the code as follows:
1. Deleted the "
2. Referenced MSML

and I got m API key. I'm using Canadian postal codes (which I assume is equally applicable: "T2P 4R5", "M9N 1K9"


----------



## bdeeve8 (Jan 24, 2019)

MSXML (I meant) **typo


----------



## John_w (Jan 25, 2019)

I have updated the two functions to parse the XML response and return the distance and time.


```
Const APIkey = "YourAPIkey"

Function GetDistance(sPCode As String, ePcode As String) As Double

    Dim t As String, s As Variant
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
    
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
    
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetDistance = .SelectSingleNode("//ns:TravelDistance").Text
        Debug.Print .SelectSingleNode("//ns:DistanceUnit").Text
    End With
    
End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String, s As Variant
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
        Dim re As XMLHTTP60
        Set re = New XMLHTTP60
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
        Dim re As XMLHTTP
        Set re = New XMLHTTP
    [URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If
    
    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey
    
    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4
    
    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetTimeinMins = .SelectSingleNode("//ns:TravelDuration").Text / 60
        Debug.Print .SelectSingleNode("//ns:DurationUnit").Text
    End With
    
End Function
```
=GetDistance("T2P 4R5", "M9N 1K9") returns 2240.836018 (miles) for me.


----------



## Jaffabfc (Aug 26, 2015)

Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible? 

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks


----------



## bdeeve8 (Jan 28, 2019)

Awesome! Thanks, man! It works now  Any idea how I could modify it to return distance in Km instead of miles?




John_w said:


> I have updated the two functions to parse the XML response and return the distance and time.
> 
> 
> ```
> ...


----------



## John_w (Jan 28, 2019)

Just use a miles to km conversion in the cell formula.


----------



## bdeeve8 (Jan 28, 2019)

How could I possibly implement this function over an large array of cells (I have to calculate distance for about 6,000 entries). Doing this by manually entering each postal code might take a very long time. IS there a way to implement this function over large number of columns, as it only takes in the postal codes as strings and cell referencing doesn't seem to work..




bdeeve8 said:


> Awesome! Thanks, man! It works now  Any idea how I could modify it to return distance in Km instead of miles?


----------



## John_w (Jan 28, 2019)

Cell referencing works for me:

=GetDistance(A2,B2)*1.60934 gives 3606 Km for the 2 post codes.

As you have many post codes it would be far better to run a macro which calculates all the distances, instead of calling a UDF for each distance.  With the post codes in columns A and B starting at row 2, this puts the calculated distance in column C:


```
Public Sub Calculate_Distances()

    Dim cell As Range
    
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            cell.Offset(, 2).Value = GetDistance(cell.Value, cell.Offset(, 1).Value) * 1.60934
        Next
    End With
    
End Sub
```


----------



## kweaver (Jun 17, 2019)

I found this app that I did years ago for a client. Maybe this function and info will help.

A column (starting in A3) has zips "from" and row 2 has zips "to" (starting in B3).

B3, for example, has this code:


```
=IFERROR(3960*CentralAngle(VLOOKUP($A3,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP($A3,zipinfo!$C$2:$E$33248,3,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,2,FALSE),VLOOKUP(B$2,zipinfo!$C$2:$E$33248,3,FALSE)),"")
```

which is filled across and down.

This function does the trigonometry:


```
Function CentralAngle(ByVal lat1 As Double, ByVal lon1 As Double, _
                      ByVal lat2 As Double, ByVal lon2 As Double) As Double
    ' shg 2008-1111
    
    ' Returns central angle between two point in RADIANS
    ' using Vincenty formula

    Const pi    As Double = 3.14159265358979
    Const D2R   As Double = pi / 180#

    Dim dLon    As Double
    Dim x       As Double
    Dim y       As Double

    ' convert angles from degrees to radians
    lat1 = D2R * lat1
    lon1 = D2R * lon1
    lat2 = D2R * lat2
    lon2 = D2R * lon2

    dLon = lon2 - lon1  ' delta lon

    x = Sin(lat1) * Sin(lat2) + Cos(lat1) * Cos(lat2) * Cos(dLon)
    y = Sqr((Cos(lat2) * Sin(dLon)) ^ 2 + (Cos(lat1) * Sin(lat2) - Sin(lat1) * Cos(lat2) * Cos(dLon)) ^ 2)
    CentralAngle = WorksheetFunction.Atan2(x, y)
End Function
```

It needs a sheet called "zipinfo" that contains the zip codes in column C, has the state abbreviation in col A,  the latitude in D and longitude in E.

I put a similar calculation in the zipinfo sheet.


----------



## kweaver (Jun 17, 2019)

This works for the United States...probably something could be adopted for the UK or elsewhere with Post Codes unlike the US.

Just need the long. and lat. to make the calculations work.


----------



## salimi (Feb 6, 2022)

John_w said:


> I have updated the two functions to parse the XML response and return the distance and time.
> 
> 
> ```
> ...


Is this code still working? I am trying to use with  UK post codes (Similar to Canadians) and  constantly get error message #NAME? in the destination cell. I have registered and received a key from MS Bing maps so am not sure what is going on at all. Thanks for the reply in advance


----------



## John_w (Feb 6, 2022)

Yes, the code still works.  The #NAME? error indicates the function isn't named GetDistance, or you haven't put the code in a standard module.

The original code uses early binding of the XMLHTTP object and therefore requires a reference to Microsoft XML v6.0.  Here is the code reposted to use late binding so that the reference isn't needed.


```
Option Explicit

Const APIkey = "Your BING API key"

Function GetDistance(sPCode As String, ePcode As String) As Double

    Dim t As String, s As Variant
    Static re As Object
    If re Is Nothing Then Set re = CreateObject("MSXML2.XMLHTTP")

    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey

    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetDistance = .SelectSingleNode("//ns:TravelDistance").Text
        Debug.Print .SelectSingleNode("//ns:DistanceUnit").Text
    End With

End Function


Function GetTimeinMins(sPCode As String, ePcode As String) As Double
    Dim t As String, s As Variant
    Static re As Object
    If re Is Nothing Then Set re = CreateObject("MSXML2.XMLHTTP")

    t = "http://dev.virtualearth.net/REST/V1/Routes/Driving?o=xml&wp.0=" & sPCode & "&wp.1=" & ePcode & "&avoid=minimizeTolls&du=mi&key=" & APIkey

    re.Open "get", t, False
    re.send
    Do
        DoEvents
    Loop Until re.readyState = 4

    With re.responseXML
        .SetProperty "SelectionNamespaces", "xmlns:ns='http://schemas.microsoft.com/search/local/ws/rest/v1'"
        GetTimeinMins = .SelectSingleNode("//ns:TravelDuration").Text / 60
        Debug.Print .SelectSingleNode("//ns:DurationUnit").Text
    End With

End Function
```
=GetDistance("T2P 4R5", "M9N 1K9") now returns 2376.182469 (miles).


----------



## salimi (Feb 7, 2022)

John_w said:


> Yes, the code still works.  The #NAME? error indicates the function isn't named GetDistance, or you haven't put the code in a standard module.
> 
> The original code uses early binding of the XMLHTTP object and therefore requires a reference to Microsoft XML v6.0.  Here is the code reposted to use late binding so that the reference isn't needed.
> 
> ...


Beautiful! It works perfectly now. Thanks so much for your reply. Much appreciated


----------



## carm (Aug 5, 2022)

I've added the Microsoft XML v6.0 reference in VBA but i'm getting #NAME? when I try to use =GetDistance("T2P 4R5", "M9N 1K9")


----------



## Jaffabfc (Aug 26, 2015)

Hi,

i am wanting to be able to calculate the distance between 2 postcodes in excel.

i have a list of around 3000 places in the UK and i am wanting to know which is the closest to were i currently am.

To do this i am wanting to be able to put a column on the end (e.g. column f) to work out how much it is from A to B, is this even possible? 

I am hoping there is something simple that i can do even if it means linking google maps into it.

Thanks


----------



## John_w (Aug 5, 2022)

carm said:


> I've added the Microsoft XML v6.0 reference in VBA but i'm getting #NAME? when I try to use =GetDistance("T2P 4R5", "M9N 1K9")


Look at my post two posts above yours for possible causes of the error and you don't need the reference.


----------



## carm (Aug 5, 2022)

John_w said:


> Look at my post two posts above yours for possible causes of the error and you don't need the reference.


Thank you for your help

I've got it working now. One last question, is their anyway to manual run the lookup? i.e if I make changes to the postcodes to reference it doesn't perform a lookup


----------



## John_w (Aug 5, 2022)

carm said:


> anyway to manual run the lookup? i.e if I make changes to the postcodes to reference it doesn't perform a lookup


What do you mean by manually run the lookup?

If you reference cells containing the start and end postcodes in the formula, instead of hardcoding them, then it will recalculate the formula when you change any of the postcodes, for example:

```
=GetDistance(A1,B1)
```


----------



## carm (Aug 8, 2022)

@John_w  Thanks for your help with this script. Its working great.


Is their any way I change things so once it has looked up the distance it doesn't do it again? My logistics spreadsheet at any time has around 200 postcode locations so when I open the sheet up it resolves every postcode which takes ages

Thanks


----------



## John_w (Aug 9, 2022)

carm said:


> Is their any way I change things so once it has looked up the distance it doesn't do it again? My logistics spreadsheet at any time has around 200 postcode locations so when I open the sheet up it resolves every postcode which takes ages


Delete the `=GetDistance` cell formulas and instead run this macro, which loops through the rows in the active sheet starting at A2 and reads the start postcode from column A and the end postcode from column B and puts the distance in miles in column C.


```
Public Sub Calculate_Distances()

    Dim cell As Range
    
    With ActiveSheet
        For Each cell In .Range("A2", .Cells(.Rows.Count, "A").End(xlUp))
            cell.Offset(, 2).Value = GetDistance(cell.Value, cell.Offset(, 1).Value)
        Next
    End With
    
End Sub
```


----------



## carm (Aug 9, 2022)

John_w said:


> Delete the `=GetDistance` cell formulas and instead run this macro, which loops through the rows in the active sheet starting at A2 and reads the start postcode from column A and the end postcode from column B and puts the distance in miles in column C.
> 
> 
> ```
> ...



That's great, can you explain which part of this code I would need to alter so I can hard code the end postcode (Currently column B)?


----------



## John_w (Aug 9, 2022)

carm said:


> That's great, can you explain which part of this code I would need to alter so I can hard code the end postcode (Currently column B)?


The 2nd argument to GetDistance is the end postcode, therefore:


```
cell.Offset(, 2).Value = GetDistance(cell.Value, "end postcode")
```


----------

