×
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.

Excel VBA Circle from 3 Points

 Forum Search FAQs Links MVPs

Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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 - Xk-1, Yk+1-Yk-1,Zk+1-Zk-1)
(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

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

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 FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel VBA Circle from 3 Points

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?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

I guess it works if your airfoil is a sphere...

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

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 3-d modeling packages so I might be missing some easier approaches.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

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 FAQ731-376: Eng-Tips.com Forum Policies for tips on how to make the best use of Eng-Tips.

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

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.html

The 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.

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

XYZcontact = XYZcenter -  Rball*XYZunitnormalvector.
(assuming unitnormalvector has magnitude 1 pointing directly outward from the surface).
But... how did you get the normal vector?

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

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.

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

Well, I think you have the gist of the basic approach.

An alternative approach might be to feed the coordinates into a 3-D CAD program, which could probably then come up with a bounding curve and then "shrink" the object by 5 mm total.

RE: Excel VBA Circle from 3 Points

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

RE: Excel VBA Circle from 3 Points

No easy way.  Each group needs to somehow get onto a single line, from whence you can sort however you want.

RE: Excel VBA Circle from 3 Points

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.tek-tips.com  (sister site to www.eng-tips.com ).  They have people there that could do this in their sleep.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

RE: Excel VBA Circle from 3 Points

(OP)
Data File D:\Work\
Serial Number xxxxxxxx-A  Blade 1
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.

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.

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!

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!