PDA

View Full Version : Find Z value from given XY. Obtain plane from 3 coordinates VBA function



gonurvia
03-31-2017, 05:19 AM
Hi, I have a list of XYZ coordinates on a flat plane such as this one;


419897.8954 4571392.2028 49.3200
422050.4833 4572316.4735 49.3200
422105.3561 4572340.6964 49.3200
422143.0174 4572357.9560 49.3200
.
.
.
The problem is that, until the N row, the Z coordinates stay the same so when I use joerd's function;


Function AltXY(P As Range, x As Double, y As Double) As Double
Dim Z As Double, D(6) As Double
Dim A(3, 3) As Double


A(1, 1) = x - P(1, 1)
A(2, 1) = y - P(1, 2)
A(1, 2) = P(2, 1) - P(1, 1)
A(2, 2) = P(2, 2) - P(1, 2)
A(3, 2) = P(2, 3) - P(1, 3)
A(1, 3) = P(3, 1) - P(1, 1)
A(2, 3) = P(3, 2) - P(1, 2)
A(3, 3) = P(3, 3) - P(1, 3)
'A(3, 1) = Z - P(1, 3)

D(1) = A(1, 1) * A(2, 2) * A(3, 3)
D(2) = A(1, 1) * A(3, 2) * A(2, 3)
D(3) = A(2, 1) * A(1, 2) * A(3, 3)
D(5) = A(2, 1) * A(3, 2) * A(1, 3)

D(0) = D(1) - D(2) - D(3) + D(5)
A(3, 1) = -D(0) / (A(1, 2) * A(2, 3) - A(2, 2) * A(1, 3))
Z = A(3, 1) + P(1, 3)

AltXY = Z
End Function
It works properly as long as the 3 coordinates have different Z values. However, if they have the same Z value, it will treat the plane as horizontal. Is there a way to make the code that detects if they have the same Z coordinates, and if they do, ignore the third one until one wich has different Z value.
Something like
x1=P(1,1) y1=P(1,2) z1=P(1,3)
x2=P(2,1) y1=P(2,2) z1=P(2,3)
x3=P(3,1) y1=P(3,2) z1=P(3,3)
for i 4 to infinity
if P(1,3)=P(2,3)=P(3,3)
x3=P(i,1) y1=P(i,2) z1=P(i,3)
else
end
end

Im not very good with VBA so any help is really appreciated!!

mdmackillop
03-31-2017, 05:58 AM
Can you post a workbook with sample data showing how the function is implemented.

gonurvia
03-31-2017, 07:40 AM
Lets say I have the following coordinates




A
B
C


1
X
Y
Z


2
1
1
1


3
2
2
1


4
3
3
1


5
4
4
1


6
2
0
2



Point coordinates: (2,0)
As you can see, the 3 first coordinates have the same Z value (1), leaving just a straight line. Not until analyzing row 6 can we know the true equation of the plane (x - y - 2z + 2 = 0)

With the function =AltXY(A1:C4;3;3), the VBA can't find out the plane equation (or rather calculates it incorrectly), while if writing =AltXY(A4:C6;3;3), it can find out the correct plane equation and calculate the z value (1) on those coordinates.

gonurvia
03-31-2017, 07:55 AM
Thats why I want the function to see if, in case all 3 z coordinates are the same, the equation should ignore the 3rd set of coordinates until reaching a row with a z with different value than 1, in this case