Freeforms in VBA

[profray]

I have tried the freeformbuilder example in Excel VBA 2007. It works fine, however the help file cannot be correct since it is redundant. Go to the FreeformBuilder.AddNodes Method and look at the Description on the right. According to MS help, when using the msoEditingCorner, X1 and Y1 are the same thing (I'm pretty sure that its just a typo here), but what about X2,Y2,X3,Y3 ? They must be something different but have identical descriptions! I have tried to write a simple routine where I read values for all these arguments and perform the addnodes method and it is not obvious. Anyone have a correct version of this help item, or better yet a workable example where you can specify the two ends of a single freeform segment, then specify the slopes at the ends. I want to make a little diagram of a beam bending (picture a flattend out S-curve lying on its side, or a flat parabola, depending on the action of the beam)
Thanks in advance.

 

[Norie]

Eh, what is the FreeFormBuilder?

An add-in? Custom control?

 

[profray]

FreeformBuilder is the how MS references the BuildFreeform method in VBA help. It is under the Excel Developer Reference. It is how one creates a freeform in VBA, then you convert it to a shape.
MSExcel's help code is:


<CODE>Set myDocument = Worksheets(1)</CODE>
<CODE>With myDocument.Shapes.BuildFreeform(msoEditingCorner, 360, 200)</CODE>
<CODE> .AddNodes msoSegmentCurve, msoEditingCorner, _ 380, 230, 400, 250, 450, 300</CODE>
<CODE> .AddNodes msoSegmentCurve, msoEditingAuto, 480, 200</CODE>
<CODE> .AddNodes msoSegmentLine, msoEditingAuto, 480, 400</CODE>
<CODE> .AddNodes msoSegmentLine, msoEditingAuto, 360, 200</CODE>
<CODE> .ConvertToShape</CODE>
<CODE>End With</CODE>
</PRE>

 

[Norie]

Isn't that from the PowerPoint object model?

Oops, my bad - looking in the wrong place.

 

[profray]

Re: Freeforms in VBA-Mystery solved !!

Building Freeform Shapes in Excel (Crazy Stuff!)
I have been programming in VBA and Excel since the time it first existed (yeah, I’m over 50) and I have never run across such a strange set of commands as the freeform building process. It defies all logic. Follow the link to my Software Page to see the rest of the story. Its a pdf file of modest size.
http://www.ce.sc.edu/DeptInfo/membe...V 301/excel_and_visual_basic_applications.htm

 

[shg]

I've drawn shapes using polylines and a Safe Array of Points. The code fragment below will be mostly nonsense to you, but it demonstrates building an array and drawing the shape:

    [COLOR=red]Dim SAoP()  As Single           ' SafeArrayOfPoints for polyline[/COLOR]
 
    ReDim fBuf(1 To 1000, 1 To 2)
 
    Worksheets("Hammer Plot").Select
    For i = 1 To nF    ' loop through all the faces
        nPts = 0
        With F(i)
            For j = 1 To .nV    ' and all their edges
                p1 = v(.vr(j))
                p2 = v(.vr(j Mod .nV + 1))
                pn = Perp(p1, p2)
                dir = Sv3(p2, p1)
                Q1 = Sgn(Dv3(Cross(pn, p1), Cross(p1, p2))) * Angle(pn, p1)
                Q2 = Sgn(Dv3(Cross(pn, p2), Cross(p1, p2))) * Angle(pn, p2)
                a = Angle(p1, p2)
                m = Application.Ceiling(RAD2DEG * a / MAXANGLE, 1)
                If m < 3 Then m = 3
 
                ' get the sample points p along line segment p1p2
                For k = 0 To m
                    Q = Q1 + (Q2 - Q1) * k / m
                    p = Av3(pn, Msv3(Tan(Q) * Nv3(pn), U3(dir)))
                    lon = RAD2DEG * Atan2xx(p.y, p.x)
                    lat = RAD2DEG * Application.Asin(p.z / Nv3(p))
                    Ham = HamLL(lon, lat)
                    nPts = nPts + 1
                    fBuf(nPts, 1) = Ham.x
                    fBuf(nPts, 2) = Ham.y
                Next k
            Next j
 
            ' build the the safeArryOfPoints buffer by copying and scaling the face buffer
            ' Also compute the face center for the face lable
            [COLOR=red]ReDim SAoP(1 To nPts, 1 To 2)[/COLOR]
            For j = 1 To nPts
               [COLOR=red]SAoP(j, 1) = fBuf(j, 1) * ScaleX + OfstX[/COLOR]
[COLOR=red]               SAoP(j, 2) = fBuf(j, 2) * ScaleY + OfstY[/COLOR]
                .ctr.x = (.ctr.x * (j - 1) + fBuf(j, 1)) / j
                .ctr.y = (.ctr.y * (j - 1) + fBuf(j, 2)) / j
            Next j
 
            ' draw the face twice - once filled and in back of the graticule
            [COLOR=red]Set .shpF = ActiveSheet.Shapes.AddPolyline(SAoP)[/COLOR]
'...

 

[profray]

This is pretty cool! Thanks. I can decipher most of what you are doing.
You have variables such as
nf = number of faces
Faces(i) = array of faces which is a structured data that has as one
of its sub-entities number of vertices (.nv)
vertices ( .vr(j) )
etc...
What I see is the magic to match your numerical structure to the VBA
structure of the Polyline object in VBA.
Sweeeet!!

 

[shg]

It's less than obvious in the code, but the last point replicates the first to close the shape.

 

[profray]

Your boss should double your salary!!

 

[Norie]

Ray

I'm a little confused about what you are actually asking here?

Are you just surprised at the quirkiness (it's early - I'm being kind) of MS Help?

PS I tried the matrices thing but sort of cheated using Excel functions and VBA methods - is that allowed.

Seemed to work anyway.