I have just written a short User Defined Function (UDF) to find the maximum distance between any two points from a list of coordinates, and since it may be useful to others, I will post it here. Here is the code:
Function MaxDist(XRange As Variant, YRange As Variant) As Variant Dim i As Long, j As Long, MDRes(1 To 1, 1 To 4) As Double, Dsq As Double, MaxD As Double Dim Maxi As Long, Maxj As Long, NumRows As Long, STime As Single If TypeName(XRange) = "Range" Then XRange = XRange.Value2 If TypeName(YRange) = "Range" Then YRange = YRange.Value2 STime = Timer NumRows = UBound(XRange) If UBound(YRange) <> NumRows Then MaxDist = "X and Y ranges must be the same length" End If If NumRows < 2 Then MaxDist = "Must be at least 2 pairs of coordinates!" End If For i = 1 To NumRows - 1 For j = i + 1 To NumRows Dsq = (XRange(i, 1) - XRange(j, 1)) ^ 2 + (YRange(i, 1) - YRange(j, 1)) ^ 2 If Dsq > MaxD Then MaxD = Dsq Maxi = i Maxj = j End If Next j Next i MDRes(1, 1) = MaxD ^ 0.5 MDRes(1, 2) = Maxi MDRes(1, 3) = Maxj MDRes(1, 4) = Timer - STime MaxDist = MDRes End Function
The function returns the maximum distance between any two points and the row numbers of the two points. To return all three values enter as an array function:
- Enter the function as a normal function.
- Select the cell with the function and the adjacent two cells
- Press F2 (Edit)
- Press Ctrl-Shift-Enter