In this example we will use sales data to create a scrolling dynamic VBA dashboard. We’ll build an interactive dashboard applying Excel VBA with exercise file which you can download. We’ll see the essential things that we need to understand to develop dashboards and next we’ll understand how to create amazing and richly visualized charts.
The data table contains information about a company’s sales performance by products. Each of the series formulas in the chart uses names for the category values and the data.
Animation is well used if there is a form to be exposed, but we need a bit more: the ability to interact with the source data.
The dashboard must do the following actions: able to stop, go back, and start again. And we will construct a simple animation effect with a loop.
To create simulations or animations your data source must change dynamically, and that requires a lot of time.
We will see the fundamentals that we need to know to build an Excel VBA dashboard and then we will learn how to create superb and richly visualized reports.
Animated Dynamic Dashboard using VBA
Let’s see what kind of data we’ll use and what will be the conception! We will examine the indicators of three products. And we will do this by the help of a special animated chart analyzing a four year period.
What makes the dashboard really unique is the data-visualization, because we will be able to review a longer period in daily breakdown in such way that the visualization will not disadvantage the understanding.
In the beginning of the article we mentioned source data and in this picture you can take a closer look at it. Column ‘A’ contains the date. In column ‘B’ and ‘D’ there are the daily values relating to each product. Column ‘F’ and ‘G’ contain the most important setting options.
In the ‘Start’ field we can determine what date the analyzation should start from. We set the starting date to the first day, we would like to see the differences between the three product’s values from the first day.
In the next step we have to set the interval in our example this is 360 days, so the animation displays a business year in one window. The third freely fillable field is the jump interval.
With this we set on the chart that one ‘jump’ will correspond to how many days (because of the animation). Based on our experiences the best solution is that this value is set to be 1, so that data remains detailed enough, but keeping the clarity simple.
In the upper right side of the dynamic VBA dashboard there are two ‘Command Button’ can be found, Start / Stop is simply does the starting and stopping of the animation.
Under these the ‘Reset’ switch sets the value of the starting date to 1. The latter one is a little relief; we can call it a comfort service. After we run the animation we don’t have to reset the timer manually, it is enough to use this button.
Dynamic VBA Dashboard – Smart and Simple
Now we know what the subject of data-visualization is and got acquainted with the most important switches and settings. A very simple macro makes the static chart dynamic; this is seen on the next picture.
The interaction with the chart will be much easier using this VBA routine.
Our dashboard procedure uses a public variable (ScrollArea) to keep track of the animation status. The key to chart animation is to use DoEvents statements.
DoEvents permits control to the OS. Control is returned after the operating system has completed processing the events.
The Excel dashboard template introduced today proves that the VBA programming language can be a very useful supplement when we would like to create something really new by using Excel. Download the practice file.