Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Nearest coordinates value VBA

Nearest coordinates value VBA

Nearest coordinates value VBA

Hi all ,

Could someone please help how to find using VBA the nearest value between coordinates from array2 .

Example :
3, 11

1, 22
2, 10
4, 55
1, 12

In this case to return -> 2,10

Thank you !

RE: Nearest coordinates value VBA

My IP2 spreadsheet already had a maxdist function, so I modified it to a MinDist function:

(I also plotted XYRange with a red line, but I used transparent ink)

Here is the code:


Function MinDist(XY1 As Variant, XYRange As Variant) As Variant
Dim i As Long, j As Long, MDRes(1 To 1, 1 To 4) As Double, Dsq As Double, MinD As Double
Dim Mini As Long, Numrows As Long, STime As Single

' Find the minimum distance from XY1 to any point in the range XYRange

    If TypeName(XY1) = "Range" Then XY1 = XY1.Value2
    If TypeName(XYRange) = "Range" Then XYRange = XYRange.Value2

    Numrows = UBound(XYRange)

    For i = 1 To Numrows
        Dsq = (XY1(1, 1) - XYRange(i, 1)) ^ 2 + (XY1(1, 2) - XYRange(i, 2)) ^ 2
        If i = 1 Then
            MinD = Dsq
            Mini = 1
        ElseIf Dsq < MinD Then
            MinD = Dsq
            Mini = i
        End If
    Next i
    MDRes(1, 1) = Mini
    MDRes(1, 2) = MinD ^ 0.5
    MDRes(1, 3) = XYRange(Mini, 1)
    MDRes(1, 4) = XYRange(Mini, 2)

    MinDist = MDRes
End Function 

You can download the spreadsheet (with numerous functions for line intersections and related tasks) from:

Doug Jenkins
Interactive Design Services

RE: Nearest coordinates value VBA

Thank you IDS!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close