I am trying to write a function that returns the straight line distance between two points in a 3 dimensional space. The worksheet has a table called "Coordinates" that has 4 columns: Column 1 is the point label, 2 through 4 are the X,Y and Z coordinates. The number of rows varies depending on the number of points. The function receives two point labels and the location of the table, does a Vlookup on each point to find its coordinates and calculates the distance.
Here is what I thought should work but it blows out on the first Vlookup. How should I do it?
In the spreadsheet I have:
=Wirelength(A1,A2,Coordinates)
And the UDF is:
Function WireLength(Point1, Point2, ARYLOC1 As Range)
X1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 2)
X2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 2)
Y1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 3)
Y2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 3)
Z1 = WorksheetFunction.VLookup(Point1, Range(ARYLOC1), 4)
Z2 = WorksheetFunction.VLookup(Point2, Range(ARYLOC1), 4)
Xdif = Abs(X1 - X2)
Ydif = Abs(Y1 - Y2)
Zdif = Abs(Z1 - Z2)
WireLength = Sqr(Xdif ^ 2 + Ydif ^ 2 + Zdif ^ 2)
End Function
Bookmarks