Numbering Charts in a Worksheet Grid

Many of the models I work with require a series of embedded charts to appear on a single worksheet. Each chart is usually numbered for reference. The numbers usually appear in a worksheet cell directly above the right-side of each embedded chart.

Below is a macro that will quickly add a number for each embedded chart to a cell on the upper right-side of the chart:

Sub NumberCharts()

    Dim i As Integer
    Dim Rng As Range
    Dim ColumnsAcross As Integer
    Dim RowsDown As Integer

    '*************************************************

    'The range where you want the first "Chart 1" to appear

    Set Rng = ActiveSheet.Range("F3")

    '# of columns to the right that you want "Chart 2" to appear

    ColumnsAcross = 5

    '# of rows down that you want "Chart 3" to appear

    RowsDown = 13

    '*************************************************

    i = 0

    Do Until i = ActiveSheet.ChartObjects.Count

        i = i + 1

        Rng.Value = "Chart " & i
        Rng.HorizontalAlignment = xlRight

        i = i + 1

        Rng.Offset(0, ColumnsAcross).Value = "Chart " & i
        Rng.Offset(0, ColumnsAcross).HorizontalAlignment = xlRight

        Set Rng = Rng.Offset(RowsDown, 0)

    Loop

End Sub

The numbers appear on the upper right-side of the chart similar to below:

Get a Maximum and Minimum Value from Certain Charts

A utility that I use at work automatically sets the Y Axis of an embedded chart to a calculated minimum and maximum value. To do so, the utility must extract the maximum and minimum point values from that chart. As I'm in the process of updating this utility to work with the Excel 2007 ribbon, I thought it would be good to document the VBA procedure that I used to extract the minimum and maximum values:

Sub Max_Min_Chart_Point_Values()

    Dim Cht As Chart
    Dim Srs As Series

    Set Cht = ActiveChart
    Set Srs = Cht.SeriesCollection(1)

    MaxVal = Srs.Values(1)
    MinVal = Srs.Values(1)

    For m = 1 To ActiveChart.SeriesCollection.Count
        A = ActiveChart.SeriesCollection(m).Values
        For l = 1 To ActiveChart.SeriesCollection(m).Points.Count
            If A(l) > MaxVal Then MaxVal = A(l)
            If A(l) < MinVal Then MinVal = A(l)
        Next l
    Next m
    
    Debug.Print MaxVal
    Debug.Print MinVal

End Sub

Please note that I've not tested this procedure with every chart type. I do know that it works with line and column charts, which happen to be most of the charts that I work with.

Simple Rollover Calculation

In healthcare, the term "rollover" can refer to the amount of volume it would take to offset a change in revenue. A typical question might be "if we sign a contract with a certain insurance company and they agree to a 30% discount, how much more additional volume will we need to see to offset that discount?" The term "volume" refers to billed charges for office visits, surgery procedures, pharmacy prescriptions, etc.

For example, if we currently have $600,000 in patient charges, a bad debt rate of 5%, and 7,500 units of volume, how much more volume do we need to see if the discount rate goes from 5% to 30%? In the screenshot below, the $600,000 of gross revenue appears in line 1, the 5% discount rate in line 2, and 7,500 units of volume in line 5. The net revenue is calculated by multiplying the gross revenue by the discount rate.

The first step to estimating the rollover impact is to calculate the gross revenue per unit for the data you're given. That calculation appears in line 6 below. Next, carry the gross revenue per unit ($80), the net revenue ($570,000), and the new discount rate (30%) into the second column of calculations.

Calculate the gross revenue by dividing the net revenue by 1 - the discount rate. The formula in cell F4 is:

=F8/(1-F10)

Finally, calculate the volume required at a 30% discount by dividing the gross revenue by the gross revenue per unit. In the screenshot below, the formula in cell F12 is:

=F4/F14

In summary, the analysis shows that volume needs to increase 36% to recover the net revenue lost by increasing the discount rate from 5% to 30%.

Add an ActiveX SpinButton Control to a Spreadsheet

One of my favorite tools for controlling the input to a single cell within an interactive spreadsheet model is an ActiveX SpinButton. When added to a spreadsheet, the SpinButton can allow the user to move quickly through multiple scenarios within a predefined range of values.

In Excel 2007, you can add an ActiveX SpinButton to a spreadsheet by first selecting the Developer tab. Then select Insert -> ActiveX Controls -> Spin Button.

In Design mode, you can link a cell as well as adjust the minimum and maximum values for the SpinButton. To do so, click on the SpinButton when in design mode. Then right-click on your mouse to access the properies for that SpinButton.

Rather than manually entering the minimum, maximum, and linked cell properties, my personal preference is to use a few simple VBA statements. For example, assuming the SpinButton is named "SpinButton1", to add a maximum value = 30, a minimum value = 20, and a linked cell = C4, and an incremental value = 1, add the following code to the sheet module for the sheet containing the ActiveX SpinButton.

Private Sub SpinButton1_SpinDown()
    With Range("C4")
        .Value = WorksheetFunction.Max(20, .Value - 1)
    End With
End Sub

Private Sub SpinButton1_SpinUp()
    With Range("C4")
        .Value = WorksheetFunction.Min(30, .Value + 1)
    End With
End Sub

The cell entry for the SpinButton combined with the SpinButton Control might be set up on the spreadsheet to look like this:

To allow the SpinButton to accept changes in percent, slightly modify the code above. For example, to add a maximum value = .50, a minimum value = .30, and a linked cell = C4, and an incremental value = .01, add the following code to the sheet module for the sheet containing the ActiveX SpinButton.

Private Sub SpinButton1_SpinDown()
    With Range("C4")
        .Value = WorksheetFunction.Max(0.3, .Value - 0.01)
    End With
End Sub

Private Sub SpinButton1_SpinUp()
    With Range("C4")
        .Value = WorksheetFunction.Min(0.5, .Value + 0.01)
    End With
End Sub

After changing the cell entry number format to percent, for the SpinButton combined with the SpinButton Control might be set up on the spreadsheet to look like this:

Although you must be in design mode to modify the size and position of the SpinButton and /or modify its properties, you must exit the design mode in order for the SpinButton to be operable.

Note: the examples above are based on code and techniques that can be found at the following:

• From java2s.com: SpinUp Events
• From java2s.com: SpinDown Events
• From Microsoft: Add a Scroll Bar or Spin Button to a Worksheet
• Excel 2007 Power Programming with VBA by John Walkenbach - Chapter 13 - Pages 440 - 442.
• Excel 2000 VBA by John Green - Chapter 10 - page 175.

Excel 2010 Chart Improvements - Migrating Excel 4 Macros to VBA

From the Microsoft Excel Product Team Blog . . .

There are a number of properties for chart elements that were previously only accessible through the Excel 4 Macro Language. We have added additional properties to VBA for these items.

Formula Properties - New properties replace the GET.FORMULA() XLM command, providing formula support for missing chart elements. New Formula properties (Formula, FormulaR1C1, FormulaLocal, FormulaR1C1Local) have been added for AxisTitle, ChartTitle, DisplayUnitLabel, and DataLabel objects.

Series/Point Name - The chart object model currently doesn’t provide a way to determine the series name that is given to a series when it is created. This information is available when hovering over a data point with the mouse, or using the SELECTION() function in XLM. The XLM command returns a point name in the format “SmPn” where m is the series number assigned when the chart is created and n is the point number. For example, when selecting a point on a chart, SELECTION() returns "S1P3". A new Point.Name property has been added that Returns a point name in the format “SmPn” where m is the series number assigned when the chart is created and n is the point number.

Position Properties - The XLM Function GET.CHART.ITEM returns the X,Y coordinates of the corners or mid-points of any chart item. This function had been necessary for some chart elements that did not have positional (.Left, .Top, .Width, and .Height) properties. In Excel 2010 we added .Left, .Top, .Width, and .Height properties for any chart elements that did not have them. This included AxisTitle, ChartTitle, DisplayUnitLabel, Point, and DataLabel objects. Additionally we added a Point.PieSliceLocation Method that returns coordinates of multiple points on Pie Slices.