The Boss Macro

We’ll make this an April Fools’ Day tradition. Last year, I created The Garbage Financial Statement — a prank financial statement that would flip data whenever entered into a cell, and subsequently move the data to an adjacent cell whenever the user attempted to delete it. I wouldn’t recommend sending it to your supervisor, but it may be a good way to annoy that guy in the Investor Relations department who always likes to “clarify” your numbers.

This year, I’m introducing “The Boss Macro”. What better way to prove your busy workload to your boss (as he/she walks by) than viewing a spreadsheet with flashing data tables and charts updated in real-time! As you sit your desk relaxing, you’ll be able to point to your screen and shrug as your boss asks you what you’re up to. Everyone knows you can’t interrupt Excel while a macro is running. Just like everyone knows that some macros take a very, very loooong time to run.

Overview

Maybe you’re having a tough time getting back to work after your lunch break, or maybe you’re waiting for a co-worker to get you some data. It’s still important to look busy. You stave off boredom by looking for answers to your deepest philosophical questions on Reddit, such as “If I haven’t scrolled over to column XFD, does it actually exist” (the answer is: no). Suddenly, you hear the footsteps of your boss coming down the cubicle isle. Quick! Look busy!

The Boss Macro is here to save you! With the click of a play button, you’ll get all the blinky and flashy, meaningless chart junk you’ve always needed to show that you’re making progress on the day’s work.

The Boss Macro File

Some features will only work in Excel 2013. Macros must be enabled upon opening.

Here’s a quick video showing the simulation in action.

Features
  • Start/stop button to toggle the animation at any time.
  • Two lists which update every 1/2 to 1 second (depending on your computers processing speed) along with changing colors.
  • Two meaningless charts which update every second.
  • Three meaningless indicators.
Operating

This spreadsheet is easy to operate. The “Stop/Start Simulation” button will toggle the animation. You can also click on the “View Trigger Form” button to bring up a small form control with a start button. This is useful if you have a two monitor setup.

Dashboard Elements

No post on this blog is complete without an analysis of approaches and techniques. While this looks like a standard Excel dashboard, there are a few elements worth mentioning.

  • All dashboard calculations are done within VBA — no spreadsheet-level formulas exist. Using Excel formulas for calculation would slow the animation.
  • The main chart showing “Day Change % & Volume” is actually two overlayed charts. The bar chart (showing percent change) sits on top of a line chart (showing the volume trend-line). This is a trick I learned from this blog post at Excel off the Grid.
  • The main chart contains two dynamic features operated from a VBA procedure. First, the data bars will change from green to red if they move from positive to negative % change (and vice-versa). Second, the scale of the line chart (Total Volume, y-axis) will increase as the line resets itself.

Script Analysis

Animation

While Excel isn’t particularly known as a tool for animation, there are some techniques for forcing animation with VBA. This must be done through the Sleep function, which is part of the Windows 32-bit API accessible through the kernel32.dll library.

The following is included at the top of the declarations area in the standard module “Operations_General”

This conditional tests if your computer is operating in the 32-bit or 64-bit Windows environment. If the 64-bit environment is detected, the “PtrSafe” keyword tells VBA that it’s ok to import the Sleep function from the 32-bit library “kernel32”.

Within the procedures that manage the animation, the following code is included.

In our file, this is contained within an infinite loop, permitting the animation to fire at each iteration until forcibly stopped.

  • Line 1: The DoEvents function allows Excel to update any spreadsheet data which precedes this block of code
  • Line 2: The Sleep function (from kernel32.dll) pauses the execution of the script, permitting your computer to catch up with the changes.
  • Lines 3-7: Includes any scripts to refresh charts.
  • Line 8: For reasons I’m unable to explain, some older charts will not refresh unless a 2nd DoEvents function is present. Specifically in this example, the treemap chart (introduced in Excel 2013) will work without the 2nd DoEvents function, while the legacy line and bar charts will not
Sleep vs. Wait

You may be wondering why I simply do not use the Application.Wait procedure instead of the Sleep function. Application.Wait does not require access to kernel32.dll. However, Application.Wait simply pauses the script for a specified amount of time. Sleep actually pauses the scripts execution, which permits the Windows to do other things, such as update the display and any associated charts and objects. I won’t go too much further into the detail on the comparison, but you can find more information at the Excel Trick blog.

Classes

I set up a collection class to manage a collection of stocks and track cumulative volume.

  • The “Portfolio” collection class contains a collection of all stocks in the report. Additionally, it tracks volume (sales, purchases, and total) and includes a member subroutine to print stock data in the Portfolio Summary
  • The “Stocks” class contains data for each individual stock, including ticker, open price, current price, price change, and volume. It contains a member subroutine to track color visuals in the portfolio.

General Procedures

Two standard modules contain general procedures to support the report setup and animation.

Setup

Within the “Operations_Setup” standard module, the subroutine Portfolio_Setup manages the resetting of all data, and the initial setup of the class. The “Portfolio” collection class’ Add procedure is used to explicitly add 18 stocks along with a current price. This procedure also resets all chart data.

Other Activity

While the actions in the animation are seemingly random, there is some logic connecting them. Within the “Operations_General” standard module, the Program_Step subroutine acts as the primary procedure to manage the animation and all other operations. The bulk of this incurs within a Do While loop, which continues until variable continueOperation is set to a value of “False” (which occurs when the user presses the stop the simulation button). The following is an ordered summary of the primary operations that take place within the Do While loop.

  1. A random number between 1 and 4 is generated. If the random number is 1, the Transaction_List subroutine is called once; if the random number is 4, the Transaction_List subroutine is called twice.
  2. The Transaction_List subroutine is called to:
    • Generate a random number between -50,000 and +50,000 to represent the quantity of a trade;
    • Make a an adjustment to the stock price by multiplying the randomly generated quantity by 0.0000012;
    • Update data within the portfolio and stock objects;
    • Print transaction data in the “Transaction Detail” table and format with green/red color.
  3. The Print_Portfolio member subroutine within the Portfolio class is called to:
    • Calculate stock price change and percent change;
    • Print data on the “Portfolio Summary” table and update color formatting;
    • Update bar chart formatting (change between red and green);
    • Update volume indicators on the right-most column of the dashboard;
  4. Manage the animation Sleep and DoEvents procedures.
  5. Refresh all charts.

Relevant Tutorials and Exercises

Hopefully this file will get you out of a tricky situation at work. If not, at least you now have a basic understanding of how to run simple animations in Excel. I’m also including some relevant tutorials explaining some concepts used in this model.

Leave a Reply