Graphs are an important and essential method of demonstrating information visually for analysis and ease of understanding. Graphs in Excel, however, are static visual representations. You can filter the data or adjust the dataset to change the information that is displayed; however, the graph itself is still a static image. Adding animation visualization to a graph will add an element of professionalism to a presentation.

In this recipe by Andre Odnoha, author of Excel 2010 Financials Cookbook, you will learn how to add animation to Excel graphs.

Excel 2010 Financials Cookbook

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel

(For more resources on this subject, see here.)

Getting ready

We will start this recipe with a single column graph demonstrating profitability. The dataset for this graph is cell A1. Cell A1 has the formula =A2/100. Cell A2 contains the number 1000:

How to do it...

1. Press Alt + F11 on the keyboard to open the Excel Visual Basic Editor (VBE). Once in the VBE, choose Insert | Module from the file menu.
2. Enter the following code:
   

   Sub Animate()
   
   Dim x As Integer
   
   x = 0
   
   Range("A2").Value = x
   
   While x < 1000
   
      x = x + 1
   
      Range("A2").Value = Range("A2").Value + 1
   
      For y = 1 To 500000
   
      Next
   
      DoEvents
   
   Wend
   
   End Sub

   The code should be formatted within the VBE code window as follows:

   

3. Save your work and close the VBE.
4. Once back at the worksheet, from the Ribbon, choose View | Macros | View Macros.
5. Select the Animate macro and choose Run.

The graph for profitability will now drop to 0 and slowly rise to account for the full profitability.

How it works...

The graph was set to use the data within cell A1 as the dataset to graph. While there was a value within the cell, the graph shows the column and corresponding value. If you were to manually change the value of cell A1 and press enter, the graph would also change to the new value. It is this update event that we harness within the VBA macro that was added.

Sub Animate()

Dim x As Integer

x = 0

Here we first declare and set any variables that we will need. The value of x will hold the overall profit:

Range("A2").Value = x

While x < 1000

   x = x + 1

   Range("A2").Value = Range("A2").Value + 1
   For y = 1 To 500000

   Next

   DoEvents

Wend

Next, we choose cell A2 from the worksheet and set its value to x (which begins as 0). Since cell A1 is set to be A2/100, A1 (which is the graph dataset) is now zero.

Using a "while" clause in Excel, we take x and add 1 to its value, then pause a moment using DoEvents, to allow Excel to update the graph, then we repeat adding another value of 1. This is repeated until x is equal to 1000 which when divided by 100 as in the formula for A1, becomes 10. Now, the profitability we end with in our animation graph is 10.

End Sub

There's more...

We can change the height of the profitability graph by simply changing the 1000 in the While x < 1000 line. By setting this number to 900, the graph will only grow to the profit level of nine.

Further resources on this subject:

• Excel 2010 Financials: Using Graphs for Analysis [Article]
• Load Testing Using Visual Studio 2008 [Article]
• How to Manage Content in a List in Microsoft Sharepoint [Article]
• Sage ACT! 2011: Creating a Quick Report [Article]
• Tips and Tricks: Report Page in IBM Cognos 8 Report Studio [Article]

Excel 2010 Financials Cookbook

Powerful techniques for financial organization, analysis, and presentation in Microsoft Excel 2010 with this book and eBook

Published: July 2011
eBook Price: $23.99
Book Price: $39.99
See more

close

Select your format and quantity:

Book 01234567891011121314151617181920

eBook 01234567891011121314151617181920

About the Author :

Books From Packt