Excel — Taming of the Shapes with PyXLL May 2022

Click to see a marquee ‘PyXLL Movie’
(hint: run at 2.0 speed)

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
  
• 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 (turtle command) that allows the following data commands:

• x 7 → set x_new to 7
• y 8.5 → set y_new to 8.5
• xy 4 6 → set xy_new 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 (an order of magnitude) 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