A Financial Statement Template for April Fools Day

Here’s an easy way to annoy your accounting department. For this April Fools Day, I am introducing a new template: Garbage Financial Statement.

This post is the first of (what will be) many case study discussions, where I provide an overview of the techniques and approaches to completing VBA projects. The format of case study posts will be somewhat different from the tutorial posts made up to this point — I will be writing more casually (hence, the use of personal pronouns). This way, I can more effectively convey why I took certain approaches and my reasoning behind that.

Through case studies, I will introduce some useful VBA procedures, while also reviewing techniques which make Excel behave in unusual ways. For the most part, these will be done with the goal of teaching a new concept or focusing on specific features. This post, for example, focuses on using Excel shapes and general VBA concepts. While the final product in this case study is not useful in a practical sense, it introduces some useful features of working with Shape objects.

I welcome any comments on these posts from people with general questions, or maybe you’re already a VBA expert and have a critique or a more efficient solution.

Overview of the Garbage Financial Statement

The Garbage Financial Statement is a standard profit & loss (P&L/Income) statement template with one twist. Any data entered into the form will be flipped upside-down. When the user attempts to click on the cell containing the flipped data (to modify or delete), the data will move to a randomly-selected adjacent cell.

There is absolutely no practical application for this template. However, this case study allows me to provide an overview for the following topics and approaches:

  • Working with Excel shapes and shape properties through VBA.
  • Creating and using random numbers in VBA.
  • Brief overview of the Worksheet_Change event.
  • Many topics discussed in the Getting Started with VBA series.

Case Study File

The Garbage Financial Statement can be downloaded here. Macros must be enabled upon opening.

General Premise and Mechanics

Maybe there’s a clueless accountant somewhere out there who will unknowingly spend 30 seconds trying to figure out what is going on. Maybe they’ll restart Excel, or even restart their computer. Either way, they’re still one step ahead of this lowly Finance Manager, who spent a good 3 hours putting this together (I ran into some simple problems).

New data entered into the financial statement is rotated 180 degrees.
New data entered into the financial statement is rotated 180 degrees.

The mechanics are as follows:

  • User enters data into cell, and cell data is flipped.
  • When user attempts to select data, data moves to an adjacent cell.

The following video shows the procedures in action:


You would think that this is an easy task given that Excel provides a range orientation setting within the format menu. This property can be accessed and modified through the Range object in VBA using the Orientation property. However, the rotation attributes are limited between +90 degrees and -90 degrees. A full upside-down (180 degree) flip is not possible with these constraints.

As a solution to this constraint, values entered into cells on this template are then transferred to a Shape object. Excel’s Shape object has the ability to rotate a full 360 degrees, allowing us to circumvent the range’s orientation property’s +90/-90 constraint.

Entering Data:
  1. A hidden default shape is duplicated and re-sized to fit exactly within the impacted cell borders. Foreground and border are already transparent in the default shape and are inherited in the duplication.
  2. Text entered into the cell is removed from the cell and added to the shape’s TextFrame.
  3. The shape is rotated 180 degrees.
Clicking on a Shape (Attempting to Delete):
  1. VBA detects the name of the clicked shape.
  2. The clicked shape is deleted, and a new shape (loaded with the same text value) is placed into an adjacent cell.
    • A random number generator used in conjunction with a Case Select statement will determine the destination cell randomly — must be adjacent, either up, down, left, or right.
    • Another random number generator will actually let the user delete or alter the value, about once every 6 clicks.
  3. Conditionals within the Case Select statement prevent any value/shape movements from colliding with existing value, existing shape, or beyond spreadsheet boundaries.
    • If a cell is completely surrounded by values or shapes, the user will be able to edit the value 100% of the time.

Script Analysis

As previously outlined, the VBA code in this workbook encompasses two primary actions: (1) Flip cell contents as entered and (2) move cell contents on a click. The workbook contains 4 subroutines and 1 function to accommodate these actions.

The VBA Editor Project Window.
The VBA Editor Project Window.

The procedures for this project can be found in two locations from the Project Window. The Worksheet_Change event–which represents actions triggered by interaction with a specific sheet–can be found in the Sheet1 “Microsoft Excel Objects”, labeled as 1 in the figure to the left. The remaining procedures can be found in the standard module, titled as “Module1”, labeled as 2 in the figure to the left.

Flip Cell Contents

Step 1: User enters data.

The primary trigger for imitating the data flip action is data input from user into a cell. This triggers a Worksheet_Change event subroutine (located in the Sheet1 object). This encompass the shortest of the 4 subroutines:

Immediately after variable declarations are two assignment statements for Boolean variables playGame and shapeFlip. As a good practice, I give spreadsheet users the option to turn off any possible “undesired” feature of a procedure. When variable playGame is set to False, the spreadsheet works normally without any flip for data movement actions — the procedures are effectively turned off. When variable shapeFlip is set to False, the initial flip action is turned off, but the value will still move if the user attempts to select.

This subroutine contains an additional conditional to prevent errors when multiple cells are changed, deleted, or inserted. Changing multiple cells simultaneously presents an address with a colon, which is not present when a single cell is changed. For example, selecting only range A1 will register a Target of “$A$1” (no colon), whereas selecting the multi-cell range of A1 through B4 will register a Target of “$A$1:$B$4” (contains colon). The colon in the multi-cell range is the key to detecting if more than 1 cell is selected. The statement in line 14 will assign a value of 1 to absoluteCount if a colon exists in the Target range. A combination of the intrinsic Len and Replace functions will count the number of colons present in the string.

If all conditions pass, the input value is extracted and the Flip_Entry subroutine is called.

Step 2: User input is transferred to shape and flipped.

The Flip_Entry subroutine will manage the entire data rotating process. While this procedure contains many lines, a closer analysis reveals that the bulk of the script relates to extracting and inputting properties.

The first part of this procedure works to extract cell coordinates and size, then applies said coordinates to a duplicated version of the original Shape object named “Default_Shape”. This effectively makes the duplicated shape fit snug within the impacted cell. A unique name is also applied to the shape object, which comprises of the string “Shape_” concatenated with the impacted cell address. A value change in cell B2 would create a shape named “Shape_B2”. This will be useful later for detecting which cells already contain a shape.

The next part of the procedure determines proper formatting for the value (numeric values are provided with “standard” comma separated and two decimal place values), then inputs the value in the shape’s TextFrame. The value is then properly aligned within the shape, and the shape is then flipped.

The procedure wraps up by clearing the cells contents (the value now exists the shape’s TextFrame only), then realigns the “Default_Shape” to its original cell. One minor issue with duplicating shapes is that it moves the original shape a few pixels from its origin — it should be reset after each duplication, or the original shape may end up in a cell like ZBA3738 after a few thousand runs.

Finally, it’s important to freeze events during the processing of this procedure. Otherwise, we could end up with a recursive call back to the Worksheet_Change event when the procedure deletes the cell value (it will register as a 2nd change). With that, the EnableEvents command is set to False (Line 10) at the beginning of this subroutine, then set to True (Line 52) at the end.

The action to flip cells contents is now complete.

Move Shape (Value) on Attempted Click

The procedure for moving a shape (value) is a bit more complicated than the physical flipping of values discussed in the previous section. Through the procedures, the clicked shape must be deleted, then a new flip shape initiated which re-casts the shape, but also places the shape within new size constraints and coordinates.

Before being hidden, I assigned the original shape “Default_Shape” to the macro Attempt_Delete on-click. Any duplicates made of the original shape will also inherit this on-click setting.

Step 1: User clicks on shape.

This activates the Attempt_Delete subroutine.

This subroutine serves two purposes. First, it extracts the name from the clicked shape using the Application.Caller method. Second, it generates a random number, in this example, between 0 (zero) and 5.

To incorporate some unpredictability into the action, the procedure will let the user modify a cell value about 1 out of every 6 clicks. If the variable deleteChance is randomly assigned the value of 0 (zero), the If statement will return False, causing the shape to be deleted and its corresponding range to be activated (letting the user make modifications to the value). If deleteChance is assigned a value greater than 0 (zero), it registers as True and initiates a call to the Move_Shape subroutine, passing along the clickedShape Shape object and clickedShapeValue String value as arguments.

Step 2: VBA detects adjacent values and prepares shape move.

The next procedure involves the most complex subroutine of the bunch. While the action of setting new shape coordinates is quite easy, there are a few checks that need to be made to prevent overlap with existing data.

This subroutine accomplishes two tasks.

  1. The subroutine will assign a random direction for the value/shape to move in. This is done by generating a random number between 1 and 4. The number picked by the randomizer will dictate the direction the shape moves in. The randomly generated number will be applied to a Select Case statement to determine the direction of movement.
  2. The shape is put through a 5 iteration for loop where it attempts to move the shape 4 times. If an adjacent cell (move candidate) is occupied, either with a value or shape, a new iteration will be initiated to attempt to move. If all sides of a shape are occupied, the 5th iteration will cause the shape to be deleted and transfer the value back to the cell.

Detecting if a shape occupies an adjacent cell requires a script to analyze all the shape on the sheet — this is why we included the shape address in the shape name (e.g. “Shape_B5”). The Shape_Exists function below tests the move candidate range against all the shape names in the sheet, returning True if no equivalent name exists.

While many developers would choose to determine if a shape exists through built-in error handling, I prefer to test by cycling through each shape on a spreadsheet. While this may use up more system resources, extra overhead should generally be unnoticeable to users, and it avoids some of the common drama associated with traditional error handling. The function compares the name of each shape against the address of the candidate cell (passed as an argument to the function). The Split() function is used to extract the shape address from the shape name.

Step 3: VBA moves or deletes clicked shape:

Assuming everything checks out and a shape is able to move, the Flip_Entry subroutine will be called from the Move_Shape subroutine, taking with it the Shape object, value to move, and a True boolean as arguments. From this point, the script follows the first “Flip Cell Contents” action described further up this post. If the shape us unable to move due to constraints or the “random opportunity”, the following actions occur.

Step 3, Option 1: Move shape.

While a value move appears as a physical move to the user, in actuality, it involves the shape in the current sell being deleted, and a new one being created in the new “move to” cell.

I could have avoided delete and re-duplicate actions by simply moving the existing shape, however, that would have actually demanded additional scripting, requiring the renaming, resizing, and re-coordinating the shape to fit the new cell. As an alternative, I simply delete the shape and reuse the Flip_Entry script to give the illusion of the shape moving.

Step 3, Option 2: Delete Shape:

The entire script contains a two possibilities for actually deleting (removing) the shape, allowing users to edit or delete cell contents. These situations are as follows:

  1. Within the Attempt_Delete subroutine–activated once a user clicked on a shape–provides a 1 in 6 chance of being deleted. Variable deleteChance creates a random integer between 0 (zero) and 5. If 0 (zero) is assigned, an If statement triggers false, resulting in the shape being deleted. Note: This value can be modified. Increasing it to 19, for example, would result in a 1 in 20 chance of the user being able to modify the value.
  2. Within the Move_Shape subroutine–called from the Attempt_Delete subroutine–if all cells around the clicked shape are occupied by a value, another shape, or the spreadsheet boundaries, the shape will delete and be converted into a cell value.

The mechanics of the shape delete functions involve (1) assigning the value of a shape’s TextFrame to a variable, (2) physically deleting the shape, then (3) assigning the shape value (through the variable) to a cell through the cells property.


By removing error handling procedures and other organizational attributes of this project, the procedures could easily be reduced to about a quarter of their current size. Doing that, along with removing randomization elements, would cut it down even further, possibly to one eighth of its current size. Regardless, it’s beneficial to include these as working with Worksheet change events opens many opportunities for glitches, especially when a user has (whether inadvertently or purposely) multiple cells selected. Likewise, any work with the range object which could potentially traverse below row 1 or column 1 will result in an error.

Relevant Tutorials and Exercises

The following are some relevant tutorials and posts related to elements used in this case study.

Leave a Reply