2 min read

 

Excel 2010 Financials Cookbook

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

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:


LEAVE A REPLY

Please enter your comment!
Please enter your name here