Minimum Distance

The lists are about 400 locations in total and i'm trying to find the minimum distance to the next coordinate for each. I am using the great-circle distance formula for each of these but there is 399 calculations for each 400 locations, so that's why I feel some vba programming is necessary. Here is an attached worksheet with a sample of 5 locations, using a rudimentary method to find each of the minimum, but with 400 locations, excel runs out of room and this would take quite a while to accomplish.

test min dist.xls

Update: I ended up making 400 macros (yes, it took me 5 hours, but I got it done) and got my data, but I still want to know how to write a function in vba to do this for me because I will definitely be using this kind of method in future research of my own.

Update 2: I started looking further into VBA and I have come up with some code that seems like it should work. Here it is:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
Dim Index As Integer, Min As Double, Dist As Double
Index = 3
Min = 500
While Index < 404
Index = Index + 1
Dist = WorksheetFunction.Acos(Cos(WorksheetFunction.Radians(90 - Lat1)) * Cos(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) + Sin(WorksheetFunction.Radians(90 - Lat1)) * Sin(WorksheetFunction.Radians(90 - Worksheet.Cells(Index, 3))) * Cos(WorksheetFunction.Radians(Lon1 - Worksheet.Cells(Index, 3)))) * 3958.756
If Dist < Min And Dist <> 0 Then
Min = Dist
End If
Wend
FindMinDist = Min
End Function

When I try using the function, it gives me back #VALUE! in the cell. I have my latitude in C4:C403 & longitude in D4:D403. Any ideas on how to make this work properly?

Figured it out! All good to go! Wrote it myself. Here it is if anyone is interested:

Function FindMinDist(Lat1 As Double, Lon1 As Double)
    Dim Index As Integer, Min As Double, Dist As Double, Lat2 As Double, Lon2 As Double
    Index = 3
    Min = 500
    While Index < 392
        Index = Index + 1
        Lat2 = Cells(Index, 3).Value
        Lon2 = Cells(Index, 4).Value
        If Cos((90 - Lat1) / 57.2957795130823) * Cos((90 - Lat2) / 57.2957795130823) + Sin((90 - Lat1) / 57.2957795130823) * Sin((90 - Lat2) / 57.2957795130823) * Cos((Lon1 - Lon2) / 57.2957795130823) > 1 Then
        Dist = 0
        Else
        Dist = WorksheetFunction.Acos(Cos((90 - Lat1) / 57.2957795130823) * Cos((90 - Lat2) / 57.2957795130823) + Sin((90 - Lat1) / 57.2957795130823) * Sin((90 - Lat2) / 57.2957795130823) * Cos((Lon1 - Lon2) / 57.2957795130823)) * 3958.756
        End If
        If Dist < Min And Dist <> 0 Then
            Min = Dist
        End If
            Wend
    FindMinDist = Min
End Function

Another way:

Function FindMinDist(lat1 As Double, lon1 As Double, r As Range) As Double
    Const pi As Double = 3.14159265358979
    Const D2R = pi / 180#
    
    Dim iRow As Long
    Dim dMin As Double
    Dim dAng As Double
    
    dMin = 1.79E+308
    
    For iRow = 1 To r.Rows.Count
        dAng = CentralAngle(lat1, lon1, r(iRow, 1).Value2, r(iRow, 2).Value2)
        If dAng < dMin Then dMin = dAng
    Next iRow
    
    FindMinDist = dMin
End Function

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 points 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
    lat2 = D2R * lat2
    dLon = D2R * (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

By passing the range containing the other lat-longs, Excel sees a dependency and will automatically recompute when the data changes. Example usage:

=FindMinDist(D2, E2, D4:E392)

The value returned is in radians, which means you can multiply by the earth radius in your preferred units (meters, miles, nautical miles, ...) to get linear (great circle) distance.