Peltier Tech Cascade Chart Utility Documentation.

Sample Cascade Charts

The data in the following table represents revenue, costs, profit, and unit sales figures for a series of products. This data will be used to illustrate the capabilities of the Peltier Tech Cascade Chart Utility.

Revenue, Cost, and Profit Data


 

Revenue data, as depicted below, is shown as a single series in a cascade chart. The single series revenue chart is available in two styles: filled or outline only.

Revenue Data

Revenue Cascade Charts
 

Multiple costs from this table are shown in the filled and outline-only styles of the multiple series cascade chart.

Cost Data

Cost Cascade Charts
 

Most products are profitable, but a couple show losses. Positive and negative values can be shown in filled and outline only cascade chart styles.

Profit (Loss) Data

Profit Cascade Charts
 

Installation

Start installation by closing Excel, then double click on the PTS_Cascade_Setup.exe file from the zip file you've purchased. The Cascade Chart utility is installed as an Excel add-in, and will be available the next time you start Excel.

The Excel User Interface

Excel 2000 through 2003 Interface
When installed, both versions of the add-in add a new menu, PTS Charts, to Excel's Worksheet and Chart Menu Bars, and this PTS Charts menu has a new item, Cascade Chart, added to it. The add-in also adds a new toolbar, PTS Charts, and a Cascade Chart icon is added to the new toolbar. This menu and toolbar are shown below:

Excel 2007 Interface
When installed, the utility adds a new custom ribbon tab, PTS Charts, to Excel's ribbon. The new tab has a group called Custom Charts, with a new item called Cascade Chart. If you use other PTS charting utilities, they also appear on this custom tab. This new ribbon tab is shown below:

The Cascade Chart Dialog

The Cascade Chart dialog allows you to select the style of cascade chart you want, and select the data for the chart. There is a brief reminder of the appropriate data layout right on the dialog.

Cascade Chart Utility Dialog
 

Data Requirements for a Cascade Chart

The Peltier Tech Cascade Chart Utility expects a data range with at least three columns and a header row. The first column contains labels for each block along the horizontal axis; the labels will be placed above the blocks. The contents of the header cell for the first column will be used as the chart's vertical axis title. The second column contains numerical values which indicate the widths of each block. The contents of the header cell for the second column will be used as the chart's vertical axis title. The heights of the blocks are listed in the third and subsequent columns, one column per series of blocks. The header cells for the third and subsequent columns are the names of the series of blocks, and will appear in the legend.

To use the utility, select a range of data to chart, then select the Cascade Chart menu item. This is how the dialog looks when a single series data range is selected.

Suitable Data for a Single-Series Cascade Chart
 

This is how the dialog looks when a multiple series data range is selected.

Suitable Data for a Multiple-Series Cascade Chart
 

Program Output

The utility inserts a new worksheet for its output. The selected data is copied to this worksheet in an appropriately staggered arrangement, and a chart is created that plots the data. The chart is a standard Excel chart; it can be copied and pasted, moved, resized, and otherwise formatted as any other Excel chart. Some changes, of course, will break the carefully laid out appearance of the chart, so the user should be ready to undo an operation or completely rebuild the chart.

The single series data above produces this cascade chart. The range of cells below the chart is used to control the horizontal axis scale of the chart. The complexity of the chart precludes use of the regular axis formatting for its scaling.

Entering a value in any of the shaded cells in this axis scaling range overrides the defaults based on the data.

The multiple series data above produces this cascade chart.

Aside from the layout of the source data and the custom horizontal axis scaling mechanism, this is a "normal" Excel chart. You can add relevant chart and axis titles, reduce the oversize margins around the plot area, resize the text and reposition the labels, and change the fill or border colors to suit.

Accessing the Peltier Tech Cascade Chart Utility via VBA

The ability to call the Peltier Tech Cascade Chart Utility from other VBA procedures has been incorporated in this version of the utility. Documentation is incomplete but will be expanded here at a later time. The following brief explanation should help experienced programmers get started.

The core of the Peltier Tech Cascade Chart Utility is a procedure called PTS_CascadeChart, which can be called from other VBA procedures. The syntax of the PTS_CascadeChart function is:

        Function PTS_CascadeChart(ChartData As Range, _
                                  CascadeStyle As ptsCascadeStyle) _
                                  As Chart

ChartData is the range containing the data to chart, laid out in a grid with column labels in the first row of the range and row labels in the first column.

CascadeStyle describes the style of the cascade chart. CascadeStyle equals 0 (zero) for the Stacked Area Cascade Chart, 1 (one) for the Stacked Line Cascade Chart, and 2 (two) for the Unstacked Line Cascade Chart.

To create a Stacked Area Cascade Chart programmatically, a calling procedure could select the data range, then run this line of code:

        PTS_CascadeChart Selection, 0

To assign the chart to an object variable in order to manipulate it further, the code should be amended:

        Set chtCascade = PTS_CascadeChart(Selection, 0)

where chtCascade has been declared as a variable of type Excel.Chart.

Contact Peltier Technical Services