INTELLIGENT WORK FORUMS FOR ENGINEERING PROFESSIONALS
Come Join Us!
Are you an Engineering professional? Join EngTips now!
 Talk With Other Members
 Be Notified Of Responses
To Your Posts
 Keyword Search
 OneClick Access To Your
Favorite Forums
 Automated Signatures
On Your Posts
 Best Of All, It's Free!
*EngTips's functionality depends on members receiving email. By joining you are opting in to receive email.
Donate Today!
Do you enjoy these technical forums?
Posting Guidelines
Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Excel VBA Circle from 3 Points

I have points (x,y,z) around an airfoil from a measuring device. It uses a ball to touch the surface. My xyz points are from the ball center. I need a macro to calculate the actual surface. How do I do it?
I was thinking of taking 3 points to get a circle, then get a line from the middle pt to the center, and then use my ball radius to move down that line and get a new xyz point. Then you move over one point and do it again.
Any other methods would be appreciated along with the macro that makes it go. I've got 1300 points per cut, 4 cuts per airfoil, and 6 airfoils per wing and at least 2 wings so speed may be important.
Thanks,
Roger 

IRstuff (Aerospace) 
22 Jul 08 19:04 
I think what you need to do is something like Huygen's wavefront propagation analysis, which would require finding the normal vector at each point and propagating in from that point the diameter of the measuring ball. The curve bounded by this second set of points is the airfoil curve. A question to be answered is how close are the points compared to the diameter of the ball. If the separation is large, then you'd probably need to spline fit a curve at each point. TTFN
FAQ731376: EngTips.com Forum Policies 

You have a curve of points Pk = (X,Y,Z)k for k = 1..N The tangent vector tk to point Pk is unique in direction: tk = (Xk+1  Xk1, Yk+1Yk1,Zk+1Zk1) (I wanted to use tilde instead of equal but don't have it on my keyboard) The normal vector nk is normal to the tangent vector tk. It satisfies: nk x tk = 0 This is not unique in direction. It can be perpendicular to the original curve over a span of 360 degrees. The solution of points a distance Rball from the original curve will be a curved cylinder with the original curve at its center. I don't think we can begin to estimate which points are on the surface unless we compare it to other cuts. Right ===================================== Engtips forums: The best place on the web for engineering discussions. 

Can you produce a curve consisting of the line segments of the centre of the ball? If so then you need to do an offset (in CAD terms) towards the centre of the airfoil, by the radius of the ball. So the easy way to do it would be to import it into CAD. Alternatively fatten each line segment up by one radius and then establish the intersections points of the lines forming the innermost edges of the fattened line segments. Fast but crude. Cheers
Greg Locock
SIG:Please see FAQ731376: EngTips.com Forum Policies for tips on how to make the best use of EngTips. 

Who says the point of contact between the ball and the airfoil lies along the line connecting center of the ball to center of the airfoil? ===================================== Engtips forums: The best place on the web for engineering discussions. 

I guess it works if your airfoil is a sphere... ===================================== Engtips forums: The best place on the web for engineering discussions. 

I guess if you are determined to create the output of a single cut curve as a curve in space (without reference to other curves), then you need some assumptions / approximations. Greg's assumption would be one possible assumptio to create a solution of that form. Else in the absence of assumptions, the output from one cut curve is a curved cylinder. Only by examining multiple cylinders can we get a more exact idea by process similar to whittling wood. Each cylinder cuts away a volume of space where the airfoil does not exist. What's left in the middle is mostly where the airfoil does exist (plus a little extra rough edge between curved cylinder tracks). fwiw, I am just talking throught the problem from my perspective. I haven't worked with cad or 3d modeling packages so I might be missing some easier approaches. ===================================== Engtips forums: The best place on the web for engineering discussions. 

Sorry, I was assuming that the radius of the probe is small compared with that of the airfoil, and that the cross section through the airfoil is square on to the span of the wing. Cheers
Greg Locock
SIG:Please see FAQ731376: EngTips.com Forum Policies for tips on how to make the best use of EngTips. 

We're using a Zeiss measurement machine. The ball is indeed tiny compared to what it's measuring. It is probably small enough that you can't even see that it's round. The arm is programmed with an initial shape so it has a chance of reaching in without hitting anything and what direction to look. After that it starts moving and measuring.
It would not have to be normal to the surface. There is a reference point in space and the CMM knows the angle of it's arm and where the center of the ball is.
We are looking at CAD to visualize what we have but I think those resources are tied up with other work. We're actually looking for the flow area between adjacent airfoils. 

jghrist (Electrical) 
23 Jul 08 9:06 
I've found the following website very useful in working with other rolling sphere problems (power substation lightning protection): http://mathworld.wolfram.com/topics/Geometry.htmlThe problem is that you don't know what direction the airfoil is in relation to the sphere location at any one point. I think you'll have to come up with some way to determine the approximate direction from two or three adjacent sphere positions. 

IRstuff (Aerospace) 
23 Jul 08 10:27 
We must assume that the surface is locally "flat," i.e., that the surface can contact the sphere at only one point on the surface and on the sphere, otherwise, you cannot accurately replicate the surface. The contact point on the sphere must be on the tangent of the circle, and the normal of the tangent goes through the center of the sphere. Since the tangent of the circle is parallel to the surface, the surface normal must be parallel and coincident with the radius of the sphere. TTFN
FAQ731376: EngTips.com Forum Policies 

jghrist (Electrical) 
23 Jul 08 11:13 
Quote:We must assume that the surface is locally "flat," i.e., that the surface can contact the sphere at only one point on the surface and on the sphere, otherwise, you cannot accurately replicate the surface.
I think you need to assume the surface locally flat for three adjacent, but not colinear sphere locations. Then determine the plane defined by the sphere centers. The airfoil point coordinates will be translated from the sphere center coordinates by a distance equal to the sphere radius, and in a direction normal to the plane defined by the sphere centers. 

IRstuff (Aerospace) 
23 Jul 08 11:56 
That would depend on how close together the samples are; if the samples are widely spaced, then there may be sufficient curvature to cause an error in using linear regression. In any case, a more robust approach should be to spline the 3 points, and find the normal to the spline at the center point. Repeat the process at each data point. Extend the normal one radius (I mistakenly said diameter in an earlier posting). TTFN
FAQ731376: EngTips.com Forum Policies 

For reference the ball is 2.5mm in radius and it's taking points quite close together. Accuracy is to millionths of an inch (<100 I believe). In the prime area of interest, the leading edge, the surface always curves away from the ball so it can only contact the surface at one point.
I have the xyz of the ball center and the normal vector ijk  what next? 

XYZcontact = XYZcenter  Rball*XYZunitnormalvector. (assuming unitnormalvector has magnitude 1 pointing directly outward from the surface). But... how did you get the normal vector? ===================================== Engtips forums: The best place on the web for engineering discussions. 

jghrist (Electrical) 
23 Jul 08 14:06 
Quote:In any case, a more robust approach should be to spline the 3 points, and find the normal to the spline at the center point. Repeat the process at each data point. Extend the normal one radius (I mistakenly said diameter in an earlier posting).
I agree. Either way would be a bit hairy using Excel with or without Visual Basic. I'd tend to use Mathcad or Matlab. 

IRstuff (Aerospace) 
23 Jul 08 14:12 
From each normal vector, you displace by the radius of the ball. The curve that encompasses all the displaced points is the actual surface. Presumably, you'd then try to fit some standard NACA? airfoil curve to the calculated surface. TTFN
FAQ731376: EngTips.com Forum Policies 

jghrist (Electrical) 
23 Jul 08 14:26 

No, I don't have to fit a NACA curve. If the surface is smooth so is the output. It's supposed to be accurate to <78 millionths of an inch if I heard right. They have to really watch how they handle parts because of dust or even fingerprints. How hard you set a part down on the measurement table affects the results.
The arm with the ball knows its angle to the part and where on the ball it touched the part. I'm thinking it has enough info to calculate the vectors from there. 

jghrist (Electrical) 
25 Jul 08 12:27 
Quote:The arm with the ball knows its angle to the part and where on the ball it touched the part. I'm thinking it has enough info to calculate the vectors from there.
I would think that if it knows all that, it would be smart enough to output the surface X,Y,Z coordinates instead of the ball center X,Y.Z coordinates. 

There are a variety of ways to program the output and the way chosen by the expert ended up with some problems. The ball center data is perfectly fine but requires a lot more math processing to turn it into something usable hence the reason he chose the original method. With our expert out of the country for 10 days I was trying to see if progress could be made without him. 

IRstuff (Aerospace) 
25 Jul 08 13:10 
Well, I think you have the gist of the basic approach. An alternative approach might be to feed the coordinates into a 3D CAD program, which could probably then come up with a bounding curve and then "shrink" the object by 5 mm total. TTFN
FAQ731376: EngTips.com Forum Policies 

I have another question on this. The files come to me in groups e.g. Key2  pressure side, suction side, leading edge. How do I write a macro to sort the groups, not the individual points? Example:
Key1=A Key2=1 Key3=2.0 Npts 3 1 2 3
Key1=A Key2=2 Key3=1.5 Npts 5 1 2 3 4 5
Key1=A Key2=1 Key3=1.5 Npts 4 1 2 3 4
The files are in csv format but it's easy enough to either change that or pull the necessary values out of the 3 header strings.
How do I sort by Key1, Key2, then Key3 without changing the actual data?
The final result would look like this:
Key1=A Key2=1 Key3=1.5 Npts 4 1 2 3 4
Key1=A Key2=2 Key3=1.5 Npts 5 1 2 3 4 5
Key1=A Key2=1 Key3=2.0 Npts 3 1 2 3 

IRstuff (Aerospace) 
28 Jul 08 18:41 

I would use a vba macro to pull it into excel as a table. Each record will have Key1value, Key2value, Key3value, followed by the data. Then use excel's sort function. What does the data look like for example where 1 is? A set of 3 floating point numbers for X, Y, Z coordinates? Any delmiters? Can you post a small excerpt of the actual file? If I get time, I might try to write a macro to do it. The logic is pretty simple. You might also try posting the question along with the sample data at the vba forum on www.tektips.com (sister site to www.engtips.com ). They have people there that could do this in their sleep. ===================================== Engtips forums: The best place on the web for engineering discussions. 

Data File D:\Work\ Serial Number xxxxxxxxA Blade 1 LeadingEdge Section Y1.50 NPTS 72 0.0117912193095768 1.50014072437008 0.0361111128431496 0.0140347175575217 1.50013940003197 0.0340299219190519 0.0162581880711581 1.50013792304094 0.0319672791692395
Good idea. If I write out additional columns for the keys then the sort would be a piece of cake. 



