In the previous post, I presented a macro that aligned embedded charts to a worksheet cell grid given certain criteria. The macro works OK if you're not concerned about the order in which any chart is placed on the worksheet. Andy Pope offered a more efficient version via comment. However, after more review I found that the macro would rearrange the original placement of one or more of the charts if one or more of the original charts were deleted and replaced. The root of the problem is that embedded charts are really shapes that are stored with an index number in the shapes collection. If a shape is deleted and a new shape created, the new shape is given the last index number in the collection. To place individual charts where you want, you need to somehow specify what index number of the shape you want to where.

The only way I could think of to get around this problem was to name each chart individually and the order the charts by name. Depending on how I wanted the charts to fall on the worksheet, I named each chart "Cht1", "Cht2", "Cht3", etc. I then modified Andy's macro to order the charts by the chart names:

Sub AlignCharts()

    Dim Cht As ChartObject
    Dim ChartsAcross As Integer
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("B2")
    ChartsAcross = 3
    ColumnsAcross = 4
    RowsDown = 10

    Set Rng2 = Rng1
    
    TotalCharts = 0
    
    For Each Cht In ActiveSheet.ChartObjects
        TotalCharts = TotalCharts + 1
    Next Cht
    
    For Cnt = 1 To TotalCharts
    
    Set Cht = ActiveSheet.ChartObjects("Cht" & Cnt)
        With Cht
            .Top = Rng1.Top
            .Left = Rng1.Left
            .Height = 94.5
            .Width = 144
        End With
        If Cnt Mod ChartsAcross = 0 Then
            Set Rng1 = Rng2.Offset(RowsDown, 0)
            Set Rng2 = Rng1
        Else
            Set Rng1 = Rng1.Offset(0, ColumnsAcross)
        End If
    
    Next

End Sub

When a series of embedded charts are printed on a page and assuming all of the charts are the same height and width, for a more professional presentation consider aligning the charts horizontally and vertically with an even amount of spacing between. Aligning charts via worksheet cell ranges can help to do so. The code below automates that process and is useful for aligning many embedded charts at one time. There are six inputs:

• Rng1 - the starting point for the chart at the top left side of the worksheet.
• ChartsAcross - the number of charts that you want across the worksheet.
• ColumnsAcross - increments the number of columns between Rng1.
• RowsDown - increments the number of rows between Rng1.
• Cht.Height - the height for each chart on the worksheet.
• Cht.Width - the width for each chart on the worksheet.

Sub AlignCharts()

    Dim Cht As ChartObject
    Dim ChartsAcross As Integer
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer
    Dim Rng1 As Range
    Dim Rng2 As Range

    Set Rng1 = Range("B2")
    ChartsAcross = 2
    ColumnsAcross = 4
    RowsDown = 10
       
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Height = 94.5
        Cht.Width = 144
    Next Cht

    Set Rng2 = Rng1

    For Each Cht In ActiveSheet.ChartObjects
        Cnt = Cnt + 1
    Next Cht

    For A = 1 To Cnt

        Set Shp = ActiveSheet.Shapes(A)
        Shp.Top = Rng1.Top
        Shp.Left = Rng1.Left

        If Application.WorksheetFunction.Round(A / ChartsAcross, 0) _
        - (A / ChartsAcross) = 0 Then
            Set Rng1 = Rng2.Offset(RowsDown, 0)
            Set Rng2 = Rng1
        Else
            Set Rng1 = Rng1.Offset(0, ColumnsAcross)
        End If

    Next A

End Sub

Given the assumptions above, the code works by first counting the number of charts on the worksheet. The first chart is placed at the top / left of cell B2. An If-Then-Else statement is used to place the following charts to the right of the chart before or to increment to the row range. The statement works by comparing the rounded (integer) value of the chart count divided by the total number of charts against the real number of the chart count divided by the total number of charts. If the divisor has a remainder, the chart is placed to the right of the chart before it. If the divisor does not have a remainder, the range increments to the next row.

The end result given the assumptions above returns the following:

I spend a good deal of time building and auditing spreadsheets in my position as an analyst in a Finance department. One of the features that has always frustrated me about Excel is the program's ability to audit the spreadsheet. Specifically, the need to double-click on the auditing arrows to move to precedent or dependent cells is, in my opinion, very difficult and inefficient.

Aaron Blood's Explode Add-In converts all of the precedent or dependent cells into cell addresses that appear in a listbox. You can then click on the listbox addresses to move to each cell address in the spreadsheet. The Explode Add-In is a great idea. Unfortunately, the code is protected so I couldn't customize it to my needs.

In this and future posts, I'm going to attempt to create my own formula auditing tool. Like the Explode Add-In, I want to be able to click on formula references to move between precedent and dependent cells. My other qualifications are that the add-in needs to be simple, efficient, and easy to use.

The first challenge to building the Formula Trace Tool add-in is to come up with the code that converts the precedent and dependent cell references to cell addresses. That code is below.

Sub TracePrecedents()

    On Error Resume Next

    Dim Rng As Range
    Dim Adrs As String
    Dim UniqueValues As New Collection
    Dim iArrow As Integer

    Set Origin = ActiveCell

    Origin.ShowPrecedents

    For iArrow = 1 To 1000
        
        Err = 0
        
        Set oPrec = Origin.NavigateArrow(True, iArrow)
        Adrs = oPrec.Parent.Name & " - " & oPrec.Address
        UniqueValues.Add Adrs, CStr(Adrs)
        
        If Err <> 0 Then Exit For

    Next

    Set Rng = ActiveSheet.Range("E3")

    For Each Item In UniqueValues
        Rng.Value = Item
        Set Rng = Rng.Offset(1, 0)
    Next Item

End Sub

To view the output of the procedure, set the active cell to a cell with precedents and run the macro. In the screenshot below, the active cell is C22 and the precedent cells are indicated by the blue auditing references. Each precedent cell is listed in the range E3:E9.

Alternatively, get rid of the range references and work in the immediate window. The resulting code is below.

Sub TracePrecedents()

    On Error Resume Next

    Dim Rng As Range
    Dim Adrs As String
    Dim UniqueValues As New Collection
    Dim iArrow As Integer

    Set Origin = ActiveCell

    Origin.ShowPrecedents

    For iArrow = 1 To 1000
        
        Err = 0
        
        Set oPrec = Origin.NavigateArrow(True, iArrow)
        Adrs = oPrec.Parent.Name & " - " & oPrec.Address
        UniqueValues.Add Adrs, CStr(Adrs)
        
        If Err <> 0 Then Exit For

    Next

    For Each Item In UniqueValues
        Debug.Print Item
    Next Item

End Sub

As a sidenote, Microsoft provides a list of error codes as well as selected error handling procedures.

The next step is to try to replace the "Debug.Print Item" statement with some type of listbox load. I'll attempt to address that in the next post of this series.

I've found that although arrays are not intuitive to most Excel users they can come in very handy at times. For example, a VLOOKUP formula similar to the one below might be a good alternative to beating the limitations of a nested IF statement.

=VLOOKUP(A1,{1,"Jan";2,"Feb";3,"Mar";4,"Apr";5,"May";6,"Jun";7,"Jul";8,"Aug";
9,"Sep";10,"Oct";11,"Nov";12,"Dec"},2,FALSE)

At times I need to present financial models to Committees in a format that allows the group to interactively view the results of changes made to the model. The ability to name, hide, and show multiple charts via VBA is great for these types of presentations. Ideally, I like to stack all of the charts to be presented one on top of another. Stacking the charts prevents me from having to skip around the worksheet to find the chart as will as insures that any chart that I choose is positioned exactly where I want it to be on the overhead viewer. I then show the selected chart and hide the others via command buttons, option buttons, or combo boxes.

As a example, I first name all of the charts in the model using the following code (you must select the chart first and then run the macro to name it):

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

I then size one or more of the charts to the same outside dimensions and position on the worksheet. To size and position a single chart, I use this code:

Sub SizeAndPositionSingleChart()
    Dim Shp As Shape
    Set Shp = Worksheets("Sheet1").Shapes("Cht1")
    With Shp
        .Height = 168.75
        .Width = 286.5
        .Top = 18
        .Left = 50
    End With
End Sub

To size all of the charts on the worksheet to the same outside dimensions and position, I use the following code:

Sub SizeAndPositionAllCharts()
    Dim Cht As ChartObject
    For Each Cht In ActiveSheet.ChartObjects
        Cht.Height = 168.75
        Cht.Width = 286.5
        Cht.Top = 18
        Cht.Left = 10
    Next Cht
End Sub

In this example, assume the model contains two charts stacked one on top of the other. Using the NameChart macro above, one chart has been named "Cht1" and the second "Cht2". A macro loaded into a regular module similar to the one below will show Cht1 while hiding Cht2.

Sub ShowChart1()
    Worksheets("Sheet1").Shapes("Cht1").Visible = True
    Worksheets("Sheet1").Shapes("Cht2").Visible = False
End Sub

The following macro will show Cht2 and hide Cht1:

Sub ShowChart2()
    Worksheets("Sheet1").Shapes("Cht2").Visible = True
    Worksheets("Sheet1").Shapes("Cht1").Visible = False
End Sub

Assuming the charts are on "Sheet1", showing and hide the charts using command buttons can be accomplished by loading the following code into the sheet module for "Sheet1". To show Cht1 and hide Cht2, use the following:

Private Sub CommandButton1_Click()
    Shapes("Cht1").Visible = True
    Shapes("Cht2").Visible = False
End Sub

To show Cht2 and hide Cht1, use the following:

Private Sub CommandButton2_Click()
    Shapes("Cht1").Visible = False
    Shapes("Cht2").Visible = True
End Sub

A simple model showing Cht1 looks like this:

The same model showing Cht2 is below:

In a previous post titled Pie Chart - Exclude Zero Values, formulas were provided that sort data while excluding zero values. This post explains how to achieve the same functionality without sorting the data.

Begin with the simple two-column data set below.

Add a set of helper columns similar to what is shown below. These helper columns will serve as the chart source.

Next, create two named ranges. The first named range covers the range C4:C12. In this example I've named that range "Data".

The second named range covers the range B4:B12. I've named that range "DataX".

At this point it's time to add two array formulas to the helper columns to extract the data for the chart. Array formula are entered into a range using the Contol - Alt - Enter keystokes.

The first formula is a multi-cell array and covers the range G4:G12. The formula is:

{=INDEX(Data,SMALL(IF(Data<>0,ROW(INDIRECT("1:"&ROWS(Data)))),ROW(INDIRECT("1:"&ROWS(Data)))))}

The second formula is a single-cell array that is entered into cell F4 and copied down the range F4:F12. The formula in cell F4 is:

{=INDEX(DataX,SMALL(IF(Data=G4,ROW(Data)-MIN(ROW(Data))+1),COUNTIF($G$4:G4,G4)))}

As stated above, because this is a single cell array it must be copied through the range F4:F12.

With both formulas properly entered, the helper columns now reflect the original data excluding zeros as shown below.

Conditional formatting can be applied to the helper columns to get rid of the errors (#NUM!). In the conditional formatting dialog box below, I've set the font color to match the background of the spreadsheet (white) if the formula returns an error. The result is to hide the error value.

At this point a simple bar chart can be created using dynamic formulas based on the helper columns. Start with creating two more named ranges. In this example a named range called "ChartData" is created that contains the dynamic formula which serves as the data source for the chart. That formula is:

=OFFSET(Sheet1!$G$4,,,COUNT(Sheet1!$G$4:$G$12),1)

The defined name dialog box now looks like this:

The second named range is called called "ChartDataX". It contains a dynamic formula which serves as the X-Axis source for the chart. That formula is:

=OFFSET(Sheet1!$F$4,,,COUNT(Sheet1!$G$4:$G$12),1)

The defined name dialog box now looks like this:

In this example the workbook is called CNZ.xls. The source for the bar chart references the named ranges "ChartData" and "ChartDataX" as shown in the Chart Source Data dialog box below: