Excel — Drawing Automated Shapes with PyXLL Sep 2023

**The Excel Drawing Function**

Using PyXLL, I have programmed automatic shapes in Excel. That is, I built a function that draws an arbitrary shape in a user spreadsheet: the shape is defined by a range of coordinates; as the coordinates are edited, a new shape can be drawn.

The shapes are just standard Excel shapes, they can be easily copied, translated, rotated, flipped, scaled, modified in any way.

**Shapes/Paths**

Shapes are simply closed paths. An open path traverses the user vertices. A closed path traverses the user vertices and returns to the first vertex.

**Use Case**

My most serious use case is drawing diagrams for engineering work.

**Features**

- The shape is drawn accurately to scale.
- Because the function is in PyXLL, it can be used in any standard (xlsx) spreadsheet
- That is, it does
require a macro spreadsheet (xlsm)*not*

or an add-in (xlam). - The shape coordinates are in a standard Excel range; the range can be on any worksheet
- The shape/path is drawn by a function; the function is called from an Excel cell formula
- There are two boolean parameters:

first, closed (default) or open shape

second, arrow on the first line segment (default) or not

- Therefore, 4 possible combinations:

**Side Note**

Using PyXLL, I also programmed a shape building tool based on *turtle* commands. I built a function called ** tcmd **(

**t**urtle

**c**o

**m**man

**d**) that allows the following data commands:

7 → set x**x**_{new}to 78.5 → set y**y**_{new}to 8.5→ set xy**xy**4 6_{new}to [4, 6]15 → turn left 15 degrees**lt**45 → turn right 45 degrees**rt**45 → set heading to 45 degrees (0 = +x; 90 = +y)**hd**3 → use current heading; move forward 3**fd**

For many applications, I find it (an order of magnitude) easier to specify coordinates using ‘path’ commands as used in *turtle* software.

I import the * TNavigator* class from standard Python

*. I take advantage of two functions within,*

**turtle***and*

**xcor***to build a coordinate list from a series of*

**ycor***commands entered in Excel.*

**turtle**Note that the (Part 1) processing of turtle commands is separated from the (Part 2) drawing of the shape. I use standard Excel and PyXLL script to draw the shape. This two-part approach eliminates the need for an appurtenant turtle canvas. Further, the standard Excel shapes can be mixed, matched, formatted, copied, edited, deleted like and with any manually drawn Excel shape.

For instance to draw a pentagon with side of length 5 (no sines or cosines!):

5**fd**72**rt**- repeat 5 times

Even if no drawing is needed, the *turtle* ** tcmd **approach easily calculates coordinates for other Excel uses.

Here is a screenshot of * tcmd* calculating the pentagon coordinates

**Shear and Moment Diagram**

The above shows the shear and moment diagram for a propped cantilever. The ** leancrew** link has a full explanation of the mechanics involved.

https://leancrew.com/all-this/2015/08/super/

**A couple examples of Python use in my structural engineering**