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

  1. Pingback: Daily Download 16: Intersections, interpolations, and rotations | Newton Excel Bach, not (just) an Excel Blog

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

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