Excel — Taming of the Shapes with PyXLL May 2022

Link to the 'P Movie'

Click to see ‘P Movie’
(hint: run at 2.0 speed)

Click to see ‘P Movie’
(hint: run at 2.0 speed)

Click to see variations in the ‘Picasso-Path’

First Image Modified -- (90 deg turns are now 95 deg turns) -- Picassoesque
First Image Modified
(90 deg turns are now 95 deg turns)
Picassoesque

Click to see variations in the ‘Picasso-Path’

Les Femmes d'Alger (Version O)
“Les Femmes d’Alger (Version O)”
Pablo Picasso, 1955

The Back Story

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.

In the video, I provide a dropdown to allow easy access to the function/coordinates for each of the five letters in the banner. After drawing the shapes, I use Excel’s Format Painter to change the style of my letter shapes. Note that the shapes are drawn in a specific location to make a banner.

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

The two examples are somewhat frivolous; my more 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 not require a macro spreadsheet (xlsm).
  • 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
    tcmd function signature
  • Therefore, 4 possible combinations:
    Title Page
    Note closeup of the arrow

Side Note

Using PyXLL, I also programmed a shape building tool based on Turtle commands.  I built a function called tcmd (turtle command) that allows the following data commands:

  • x 7 → set xnew to 7
  • y 8.5 → set ynew to 8.5
  • xy 4 6 → set xynew to [4, 6]
  • lt 15 → turn left 15 degrees
  • rt 45 → turn right 45 degrees
  • hd 45 → set heading to 45 degrees (0 = +x; 90 = +y)
  • fd 3 → use current heading; move forward 3

For many applications, I find it easier to specify coordinates using ‘path’ commands as used in Turtle software. For instance to draw a pentagon with side of length 5:

  • fd 5
  • rt 72
  • 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/


Examples of use in my structural engineering

Leave a Reply

Your email address will not be published. Required fields are marked *