Macs and PCs

Peltier Tech Waterfall Chart Utility for the Macintosh

Peltier Technical Services is pleased to announce a new Macintosh version of the popular Waterfall Chart Utility. Functionality of the Mac utility is nearly identical to that of the Windows version, though differences between the Mac and Windows platforms require it to be released as a separate utility.

The Waterfall Chart Utility can be licensed from the Peltier Tech Waterfall Chart Utility web page. Make sure the Mac version is listed in the shopping cart before you checkout.

Documentation

The rest of this post serves as temporary documentation about installation and use of the Peltier Tech Waterfall Chart Utility add-in for Microsoft Excel for the Apple Macintosh.

NOTE: Opening, installing, or using the add-in file PeltierTechWaterfallMac.xla means accepting the terms of the end user license agreement PeltierTechWaterfallMacEULA.txt.

The following files for the Peltier Tech Waterfall Chart Utility are delivered in a zip file named PeltierTechWaterfallMac.zip:

• PeltierTechWaterfallMac.xla (the add-in for Microsoft Excel for the Mac)
• PeltierTechWaterfallMacEULA.txt (the end user license agreement)
• PeltierTechWaterfallMacREADME.txt (the Read-Me file)

Unzip these files into a new folder on your computer.

INSTALLATION

Open Excel. Go to the Tools menu > Add-Ins.

In the Add-Ins dialog, click Select…, browse to the folder that contains the files you unzipped above, and select PeltierTechWaterfallMac.xla.

Click OK, and note that the add-in is listed and checked in the Add-Ins dialog.

Click OK once more to return to Excel.

If you don’t want to install the add-in, you can still use it in the current Excel session. Go to the File menu > Open, browse to the folder containing the utility’s files, select PeltierTechWaterfallMac.xla, and click OK.

Installing or opening the add-in file adds the Peltier Tech menu to Excel. This menu has two buttons, Waterfall Chart and About….

DATA

The required data has two columns. The first column is a set of labels used as X axis category labels. The second column is a set of values. The first label and value are the starting point, indicated by a solid blue bar. The rest of the values are assumed to be changes in value, which will be indicated by floating green (up) and red (down) bars. There is no need to calculate any cumulative or final sums, as the program manages that for you.

You may use a label in the second column above the values. Excel will guess whether you have included a label, but you can override this setting in the dialog. If you do not use a range with such a label, a row for this and other labels will be inserted above the first label/value pair in your data.

If the last label in the first column is accompanied by a value, a new row is inserted for the final point.

If the last label in the first column has no corresponding value in the second column, it will be used to indicate the ending point, and the cumulative value will be used for a solid blue bar.

To indicate an intermediate subtotal within the chart, insert a row in the appropriate place within your data, enter a label for the subtotal in the first column, and leave the value blank in the second column.

RUNNING THE UTILITY

Select your data (or select a single cell in your data and Excel will guess at your data range), go to the Peltier Tech menu > Waterfall Chart. Enter or check the settings in the dialog, and press OK.

You should check that the correct data range is selected, and that the setting for whether there is a leading value row is correct. You can select whether to add labels to the bars on the chart, and you can select whether to place the calculations and chart on the active worksheet or on a new worksheet.

If you select the active sheet option, the program places calculations in several columns to the right of the source data. If these cells are not blank, the program will stop without overwriting these cells.

RUNNING THE UTILITY FROM VBA

You can create a waterfall chart from your own VBA code, bypassing the dialog. The syntax for the PeltierTechWaterfallChart function is shown in the object browser.

You can use early binding or late binding.

Early binding allows you to use intellisense to help streamline your programming.

Late binding does not require a reference and does not cause a compile error at runtime if the utility is not present.

EARLY BINDING

First, in the Visual Basic Editor open the project which will call the utility. Go to the Tools menu > References, find Peltier Tech Waterfall Chart Utility (or similar label) in the list, check its checkbox, and click OK.

The utility returns a reference to the chart, so you can assign this to a chart variable in your code:

Dim chtWaterfall As Chart
Set chtWaterfall = PeltierTechWaterfallChart(DataRange, LabelInFirstRow, ShowLabelsOnBars, ChartOnNewSheet)

Alternatively you can run the program without defining a chart variable:

Call PeltierTechWaterfallChart(DataRange, LabelInFirstRow, ShowLabelsOnBars, ChartOnNewSheet)

or

PeltierTechWaterfallChart DataRange, LabelInFirstRow, ShowLabelsOnBars, ChartOnNewSheet

Variables:
DataRange As Range – The range containing the chart data
LabelInFirstRow As Boolean – True if first row of DataRange contains a label
ShowLabelsOnBars As Boolean – True to apply data labels to bars in chart
ChartOnNewSheet As Boolean – True to create chart on new worksheet, False to create chart on active sheet (Optional, Default = True)

LATE BINDING

You can assign the chart to a chart variable in your code:

Dim chtWaterfall As Chart
Set chtWaterfall = Application.Run("PeltierTechWaterfallMac.xla!PeltierTechWaterfallChart", DataRange, LabelInFirstRow, ShowLabelsOnBars, ChartOnNewSheet)

Alternatively you can bypass defining a chart variable:

Application.Run "PeltierTechWaterfallMac.xla!PeltierTechWaterfallChart", DataRange, LabelInFirstRow, ShowLabelsOnBars, ChartOnNewSheet

Variables are defined in the Early Binding instructions above.

CONTACT PELTIER TECH

If there are problems with installation or operation of the utility, contact:

Peltier Technical Services, Inc.
http://972.86c.myftpupload.com
jon@peltiertech.com