## Excel VBA Circle from 3 Points

## 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

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

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

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

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

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

=====================================

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

## RE: Excel VBA Circle from 3 Points

=====================================

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

## RE: Excel VBA Circle from 3 Points

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

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

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

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

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

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

## RE: Excel VBA Circle from 3 Points

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

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

I have the xyz of the ball center and the normal vector ijk - what next?

## RE: Excel VBA Circle from 3 Points

(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

## RE: Excel VBA Circle from 3 Points

Presumably, you'd then try to fit some standard NACA? airfoil curve to the calculated surface.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

## RE: Excel VBA Circle from 3 Points

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

## RE: Excel VBA Circle from 3 Points

## RE: Excel VBA Circle from 3 Points

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.

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

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

TTFN

FAQ731-376: Eng-Tips.com Forum Policies

## RE: Excel VBA Circle from 3 Points

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

Serial Number xxxxxxxx-A 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.