can excel draw with preset values

[man57]

[Chip Pearson]

Could you explain further what you are attempting to accomplish?


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com


"man57" <[email protected]> wrote in message
news:[email protected]...
>

[CLR]

If you are talking about creating drawing objects from a "table" of preset
values, then the answer is yes, it can be done using VBA, but it is a BIG
deal to try to make drawings that way........to test, start up the macro
recorder and do a couple simple drawing objects and then check the
code....you can vary the numbers therein and the location/size of the
drawing objects will change, and you can even replace one of them with
"Range("D1").value" and the macro will follow suite......once the code is
all set up tho, you can easily change the numbers in the table and thereby,
the picture will change.

hth
Vaya con Dios,
Chuck, CABGx3

"man57" <[email protected]> wrote in message
news:[email protected]...
>

[Ken Johnson]

Here's an example of using sheet data to control a shape:

Copy/Paste the following X1 values into A2:A9

240
273
318
377
459
419
378
303

Now these X2 values into B2:B9

239
311
311
382
429
358
358
286

Now these Y1 values into D2:D9 (Column C is blank)

285
237
179
178
179
225
285
286

Now these Y2 values into E2:E9

254
234
190
170
210
236
277
301

Now anywhere on that sheet use the freeform builder to draw a closed
shape with exactly 8 nodal points (Click/release/drag 8 times then
finish off with a double click while holding the pointer over the start
of the curve)

Now name the shape by selecting it then typing "mycurve" (w/o the
quotes) in the name box on the left of the formula bar, then press
Enter.

Now paste the following code into a standard module.

When the code is running you should see the shape you drew (mycurve)
quickly move to a new position then morph between being a
parallelogram and a step shaped polygon five times.

The parallelogram shape is produced by the set of X1 and Y1 values, one
pair of values for each of the 8 nodal points.
The step shaped polygon is produced by the X2 and Y2 values.

Each nodal point moves linearly from (X1,Y1) to (X2,Y2) as the value of
k changes from 0 to 1 in steps of 0.02. Making this step size smaller
slows down the movement between the two shapes.

Public Sub FiveCycles()
Dim mycurve As Shape
Set mycurve = ActiveSheet.Shapes("mycurve")
Dim Xo() As Single, Xf() As Single
Dim Yo() As Single, Yf() As Single
Dim I As Integer, IntNodes As Integer
Dim j As Integer, k As Single
IntNodes = mycurve.Nodes.Count
ReDim Xo(IntNodes)
ReDim Xf(IntNodes)
ReDim Yo(IntNodes)
ReDim Yf(IntNodes)

For I = 1 To IntNodes
If Cells(I + 1, 1) = "" Or _
Cells(I + 1, 2) = "" Or _
Cells(I + 1, 4) = "" Or _
Cells(I + 1, 5) = "" Then
MsgBox "Not Enough Data for Nodal Points on Curve!"
Exit Sub
End If
Xo(I) = Cells(I + 1, 1)
Xf(I) = Cells(I + 1, 2)
Yo(I) = Cells(I + 1, 4)
Yf(I) = Cells(I + 1, 5)
Next I
Do While j < 5
j = j + 1
Do While k < 1
k = k + 0.02 'value affects speed
For I = 1 To IntNodes
mycurve.Nodes.SetPosition I, _
k * (Xf(I) - Xo(I)) + Xo(I), _
k * (Yf(I) - Yo(I)) + Yo(I)
Next I
Calculate
Loop
Do While k > 0
k = k - 0.02
For I = 1 To IntNodes
mycurve.Nodes.SetPosition I, _
k * (Xf(I) - Xo(I)) + Xo(I), _
k * (Yf(I) - Yo(I)) + Yo(I)
Next I
Calculate
Loop
Loop
End Sub

This code works on PC and Mac OS earlier than OS X. Microsoft changed
the way VBA works for Mac OS X. For it to work on the latest versions
of Office for Mac the Calculate lines have to be changed to DoEvents
and the mouse has to be continually moving while the code runs
otherwise no motion is observed, just initial and final positions
(which amounts to nothing since the shape finishes up where it
started.)

Ken Johnson

[man57]

I am calculating the length of rafters on a building and I want to draw it
and print it.

"Chip Pearson" wrote:

> Could you explain further what you are attempting to accomplish?
>
>
> --
> Cordially,
> Chip Pearson
> Microsoft MVP - Excel
> Pearson Software Consulting, LLC
> www.cpearson.com
>
>
> "man57" <[email protected]> wrote in message
> news:[email protected]...
> >
>
>
>

[Ken Johnson]

Don't quote me on this one, but Excel doesn't print drawings very well
at all.
I just drew a circle which the size tab of the format autoshape dialog
showed Width and Height both to be 6.61 cm. However, the printed
"circle" is 6.4 cm high and 7.0 cm wide.

I would do the drawing in Word. Word also has a drawing grid that can
be turned on and off, making it fairly easy to get the dimensions right
without having to use the Format Autoshape dialog too often.
If you use the freeform builder you can edit the nodal points and the
line segments between them fairly easily.
Before you print your Word drawing make sure "Allow A4/Letter paper
resizing" on the Print tab of the Options dialog is NOT ticked,
otherwise the printed version will be distorted.

Ken Johnson

[paul]

Why not have a generic drawing(s) and merely have excel "fill" in the
required dimensions.
--
paul
[email protected]
remove nospam for email addy!



"man57" wrote:

> I am calculating the length of rafters on a building and I want to draw it
> and print it.
>
> "Chip Pearson" wrote:
>
> > Could you explain further what you are attempting to accomplish?
> >
> >
> > --
> > Cordially,
> > Chip Pearson
> > Microsoft MVP - Excel
> > Pearson Software Consulting, LLC
> > www.cpearson.com
> >
> >
> > "man57" <[email protected]> wrote in message
> > news:[email protected]...
> > >
> >
> >
> >