Formula Trace Tool for Excel 2007

Last week I upgraded my formula auditing add-in to work with Excel 2007. To recap, the Formula Trace Tool allows the auditor to click on precedent or dependent cell references in a listbox as opposed to clicking on audit arrows or performing a manual search. The goal of this functionality is to allow the auditor to move through formulas much more quickly and efficiently.

When the add-in is installed, a button containing an orange diamond appears on the right side of the Formulas ribbon tab. If interested, more information on how to use the tool and a link to download it can be found here.

Stacking Embedded Charts

A technique that I've found useful for presentations or working in small worksheet spaces involves stacking embedded charts on top of each other. Stacking the charts is done in such a fashion that only one chart is visible at a time. When a chart is needed, it is made visible by calling it to the top of the stack.

For example, start with four embedded charts on a worksheet named "charts":

First apply a name to each chart. This is not required but in my experience I've found it's easier to keep track of chart names as opposed to numbers. This is especially true if you're constantly adding and deleting charts. An easy way to name a chart is by activating it and then running the macro below.

Sub NameChart()
    ActiveChart.Parent.Name = "Cht01"
End Sub

I've named the charts in the screenshot "Cht01", "Cht02", "Cht03", and "Cht04".

Next, stack the charts one on top of another. Again, a macro makes this easy. The macro below sizes each chart to the size of the first chart (Cht01). It then positions each chart into cell C2.

Sub StackCharts()

    Dim ChtObj As ChartObject
    Dim myCht As ChartObject
    Dim Rng As Range

    Set myCht = Worksheets("Charts").ChartObjects("Cht01")
    Set Rng = Worksheets("Charts").Range("C2")
    
    For Each ChtObj In ActiveSheet.ChartObjects

        ChtObj.Top = Rng.Top
        ChtObj.Left = Rng.Left
        ChtObj.Height = myCht.Height
        ChtObj.Width = myCht.Width

    Next ChtObj

End Sub

At this point only one chart is visible. Finally, you can use controls to determine which chart is visible. In the screenshot below I've added an option button for each chart. Each option button contains code that makes the chart associated with it visible by bringing it to the top of the stack. All four option buttons are grouped together.

The worksheet showing a single stacked group of charts with option button controls now looks like this:

Code now has to be added to each option button to control the position in the stack for the chart that it is associated with. The code examples below need to be copied into the "charts" sheet module.

The code for the option button titled "Show Chart 1" is below:

Private Sub OptionButton1_Click()
    Dim ChtObj As ChartObject
    Set ChtObj = Worksheets("Charts").ChartObjects("Cht01")
    ChtObj.ShapeRange.ZOrder msoBringToFront
End Sub

The code for the option button titled "Show Chart 2" is below:

Private Sub OptionButton1_Click()
    Dim ChtObj As ChartObject
    Set ChtObj = Worksheets("Charts").ChartObjects("Cht02")
    ChtObj.ShapeRange.ZOrder msoBringToFront
End Sub

And so on for buttons three and four.

At this point the model is finished and the visibility of each chart is controlled by the option buttons.

Comment Shapes

Several sites have recently posted VBA techniques to add shapes to comments . . .

• All About Comment Shapes from the Code for Excel and Outlook Blog.
• Pimp Your Comment Boxes from Chandoo.org.
• Remove Your Name From an Excel Comment from the Journal of Accountancy.
• Comment Programming from Contextures.

Change Font Size for Multiple Charts

A recent question to the Microsoft Excel Charting Discussion Group asks "I have about 60 charts in a workbook and would like to change all of the font sizes - title, axis, and labels to a different font size. Is there a way to do this with a macro?

After studying the object model, it looks like this should do the trick:

Sub Resize_Fonts()
    Dim Sht As Worksheet
    Dim Cht As ChartObject
    For Each Sht In ActiveWorkbook.Sheets
        For Each cht In Sht.ChartObjects
            Cht.Chart.ChartArea.Font.Size = 10
        Next cht
    Next Sht
End Sub

Tie Y Axis to Cells, Constants, or Variables

In Link Chart Axis Scale Parameters to Values in Cells, Jon Peltier describes how to use VBA to link the X and Y axis parameters of a chart to worksheet cells, constants, or variables. The article further describes how you can use a Worksheet_Change event to automate the process. It's hard to understand why Microsoft has yet to build this very useful and simple feature into Excel.

Assuming an embedded column chart named "Cht01" that resides on a sheet named "Charts", the VBA code to control the primary axis is:

Sub SetYAxis()

    Dim Cht As Chart
    Set Cht = Sheets("Charts").ChartObjects("Cht01").Chart

    With Cht.Axes(xlValue)
        .MaximumScale = Range("F2")
        .MinimumScale = Range("F3")
        .MajorUnit = Range("F4")
    End With

End Sub

The msdn Excel 2007 Object Model Reference shows that the Chart.Axes Method "returns an object that represents either a single axis or a collection of the axes on the chart". The syntax is:

expression.Axes(Type, AxisGroup)

The optional parameters for the Type variant are xlValue, xlCategory, or xlSeriesAxis. The AxisGroup can refer to a primary (xlPrimary) or secondary (xlSecondary) value.

John Walkenbach's Excel Charts - chapter 16 also has a very good explanation of how to use VBA to set axis values.