## Maximum distance between two points

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