Hi All,

This was a past posting that has been enhanced with this post. The original UDF took a range of coordinates and returned the minimum distance between any two sets of coordinates.

This one returns the nearest neighbor from from a specified row of coordinates.

For example, if each coordinate point is in B:E and the data table is from B2:E20, then

=nearest_neighbor2(B2:E2,$B$2:$E$20)

will return the minimun distance between B2:E2 and any other B:E set within the range.

Not that there is any specific usefulness for this for anybody, but you may be able to gather something from the code to use in a future project.

Code:

Function NEAREST_NEIGHBOR2(TestRange As Range, ValRange As Range)
Dim RangeArray As Variant, TestArray As Variant
Dim i As Long, Counter As Long
Dim j As Integer, distance As Double, min_dist As Double
Dim StartValRangeRow As Long, StartTestRangeRow As Long
If ValRange.Cells.Count = 0 Then
NEAREST_NEIGHBOR2 = CVErr(xlErrNum)
Exit Function
End If
'''Read the values into an array
TestArray = TestRange.Value
RangeArray = ValRange.Value
'''Identify the starting row for each argument
'''to ignore comparing a row against itself.
StartValRangeRow = ValRange.Row
StartTestRangeRow = TestRange.Row
'''Loop through the data and determine the minimum distance.
For i = 1 To ValRange.Rows.Count
If StartValRangeRow + i - 1 <> StartTestRangeRow Then
distance = 0 ' reset distance in each pass
Counter = Counter + 1
For j = 1 To ValRange.Columns.Count
distance = distance + (TestArray(1, j) - RangeArray(i, j)) ^ 2
Next j
If Counter = 1 Then
min_dist = distance '''first pass must be the minimum so far
Else
min_dist = WorksheetFunction.Min(min_dist, distance)
End If
End If
Next i
NEAREST_NEIGHBOR2 = Sqr(min_dist)
End Function

Any comments/enhancements/suggestions welcome.

Thanks,

Jay

## Like this thread? Share it with others