×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Are you an
Engineering professional?
Join Eng-Tips Forums!
• Talk With Other Members
• Be Notified Of Responses
• Keyword Search
Favorite Forums
• Automated Signatures
• 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.

# Nearest coordinates value VBA

## Nearest coordinates value VBA

(OP)
Hi all ,

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

Example :
array1
X Y
3, 11

array2
X Y
1, 22
2, 10
4, 55
1, 12

In this case to return -> 2,10

Thank you !

### RE: Nearest coordinates value VBA

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

Here is the code:

#### CODE --> VBA

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 

Doug Jenkins
Interactive Design Services
http://newtonexcelbach.wordpress.com/

### RE: Nearest coordinates value VBA

(OP)
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.

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:

• Talk To Other Members
• Notification Of Responses To Questions
• Favorite Forums One Click Access
• Keyword Search Of All Posts, And More...

Register now while it's still free!