How to Calculate Distance Between Two Coordinate Points with Excel VBA

I wanted to calculate the distance between a list of coordinate points in an Excel spreadsheet. This blog post by BlueMM did most of the work for me by coming up with a pretty accurate Excel formula for doing so. This is my attempt to expand on their formula by adapting it for use in VBA.

The Distance Formula

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

This is the formula straight from BlueMM's post. You just need your coordinates to be in Decimal Degrees format with South and West being negative and North and East being positive and each in their own column like this example:

In this example, you would put the formula in Range("C3")  and change the Lat1/Lat2/Long1/Long2 to point to the correct cells and it would look like this:

=ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) *6371

You can then drag the formula down to cover the rest of your points (if you have more than 2) or click on Range("C3") and double click the little square in the corner of the cell. 

From my experience so far, this formula has been pretty accurate except sometimes it results in a number error when the points are exactly the same latitude and longitude. I fixed this error by wrapping the formula with an IF and AND formulas to test for exact matches before calculating the distance to make sure the result is zero when they match like this:

=IF(AND(Lat1=Lat2,Long1=Long2),0,ACOS(COS(RADIANS(90-Lat1)) *COS(RADIANS(90-Lat2)) +SIN(RADIANS(90-Lat1)) *SIN(RADIANS(90-Lat2)) *COS(RADIANS(Long1-Long2))) * 6371)

=IF(AND(A2=A3,B2=B3),0,ACOS(COS(RADIANS(90-A2)) *COS(RADIANS(90-A3)) +SIN(RADIANS(90-A2)) *SIN(RADIANS(90-A3)) *COS(RADIANS(B2-B3))) * 6371)

VBA Distance Function

Enum Measurement
    Miles = 3958.756
    Km = 6371
    NM = 3440.065
    Meters = 6371000
End Enum

Function dDistance(ByRef lat1 As Double, _
                   ByRef lon1 As Double, _
                   ByRef lat2 As Double, _
                   ByRef lon2 As Double, _
                   m As Measurement) As Double
    Dim func As Object
    If lat1 = lat2 And lon1 = lon2 Then
        dDistance = 0
        Exit Function
    End If
    Set func = Application.WorksheetFunction
    dDistance = func.Acos(Cos(func.Radians(90 - lat1)) * _
                    Cos(func.Radians(90 - lat2)) + _
                    Sin(func.Radians(90 - lat1)) * _
                    Sin(func.Radians(90 - lat2)) * _
                    Cos(func.Radians(lon1 - lon2))) * m
End Function

This VBA code calculates the distance with the same functions as the formula. I added some functionality with the measurement parameter that let's you easily calculate different units of distance. Make sure the Enum is at the top of the module for it to work. Now that it's a VBA function, we can automate our distance calculations without our data being in a spreadsheet.

Filter Collection of Coordinates by Distance from Point

Function cFilterByDistance(lat1 As Double, lon1 As Double, _
                           dist As Double, unit As Measurement, _
                           cCoords As Collection) As Collection
    Dim cClose As New Collection
    Dim i As Long
    Dim lat2 As Double
    Dim lon2 As Double

    If cCoords.Count = 0 Then
        Set cFilterByDistance = cClose
    End If
   
    For i = 1 To cCoords.Count
        lat2 = cCoords(i)(LBound(cCoords(i)))
        lon2 = cCoords(i)(LBound(cCoords(i)) + 1)
        If dDistance(lat1, lon1, lat2, lon2, unit) <= dist Then
            cClose.Add Array(lat2, lon2)
        End If
    Next i

    Set cFilterByDistance = cClose
End Function

This function is an example of how you can use the dDistance function to loop through coordinates. It compares the distance between a single point (lat1, lon1) and each point in a collection(cCoords) and returns a collection of the points that are within a given distance (dist) and measurement (unit) from the single point. cCoords is a collection of Array(lat, lon) for each point.