## 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

This entry was posted in Coordinate Geometry, Excel, Maths, Newton, UDFs, VBA and tagged , , , , . Bookmark the permalink.

### 1 Response to Maximum distance between two points

This site uses Akismet to reduce spam. Learn how your comment data is processed.