Nearest coordinates value VBA

[TudorM]

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 !

 

[IRstuff]

Are you referring to straight Pythagorean distance?

TTFN (ta ta for now)
I can do absolutely anything. I'm an expert!

https://www.youtube.com/watch?v=BKorP55Aqvg

faq731-376 forum1529 Entire Forum list

http://www.eng-tips.com/forumlist.cfm

 

[TudorM]

Yes ! Thank you !

https://www.youtube.com/watch?v=BKorP55Aqvg

 

[IDS]

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:
more-updates-to-arcspline-and-ip2

Doug Jenkins
Interactive Design Services

http://newtonexcelbach.wordpress.com/

 

[TudorM]

Thank you IDS!