Newton Excel Bach, not (just) an Excel Blog

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

This function and a variety of other coordinate geometry related functions are included on the spreadsheet IP2.xls, with full open source code.  More details of IP2 here.

MaxDist in action