PTS Dot Plot Utility Documentation.

Installation

Start installation by closing Excel, then double click on the PTS_Dot_Plot_Pro.exe file from the zip file you've purchased. The Dot Plot 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, Dot Plot, added to it. The add-in also adds a new toolbar, PTS Charts, and a Dot Plot icon is added to the new toolbar. If you have installed other PTS charting utilities, they will appear on the menu and toolbar. This menu and toolbar are shown below:

Excel 2007 Interface
When installed, the professional 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 Dot Plot. If you use other PTS charting utilities, they also appear on this custom tab. This new ribbon tab is shown below:

The Dot Plot Dialog

The top half of the Dot Plot Utility dialog shows the selected range and whether labels were detected in the top row of this range. The bottom half of the dialog allows the user any combination of horizontal or vertical gridlines and lines connecting points in the data series. The utility remembers the combination of lines selected for each chart, and displays those options by default the next time it is used.

Dot Plot Dialog
 

To use the utility, select a range of data to chart, then click the Dot Plot button on the menu, toolbar, or ribbon. If only a single cell is selected, the utility will expand the data range until it reaches empty rows and columns. When the dialog appears, make sure the correct data range is indicated and that the utility has correctly detected whether there are labels in the top row. Select the options for lines to be displayed on the chart, and press OK.

Dot Plot Dialog with Data Range
 

The utility selects the chart data range, inserts a column of calculated values needed by the chart, then inserts the chart.

Dot Plot Chart Data Range and Chart
 

Dot Plot Input and Output Data Range

Input

The data range is a contiguous range (no blank rows or columns, though blank cells are permissible) two or more columns wide. The leftmost column contains the labels which will appear along the left axis of the chart, and subsequent columns contain numerical values to be plotted againts these labels. The may be non-numeric labels in the first row of the data, used for example as series names.

In the first column, the cell above the first label and the cell under the last label must be blank. If they are not, the utility will warn you, and if you proceed, the utility will clear these cells

Output

The utility adds a column of values in the first available column to the right of the selected data. This is generally but not always the column adjacent to the data. The utility will not overwrite any data when this column is added. The values in this column are used in construction of the chart, and should not be altered.

The chart is created to the right of thc column inserted by the utility. The chart starts at the top of the data range (the row with labels if there is one, otherwise the row with the first data value), and extends three rows below the bottom of the data range. If there is one series, the chart is five columns wide, and has no legend. If there are two or more series, the chart is six columns wide and contains a legend.

The dot plot 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.

Illustrations of the data input and chart output range

The figures below illustrate the combinations described above. The red outline indicates the chart source data. Green cells show the first row labels if they exist. Purple cells show the labels in the first column, and blue cells show the numerical values in adjacent columns. The yellow cells must be blank, and the pink cells contain the output calculated by the utility. The chart is next to this column.

Labels in first row, one chart series (no legend)

Labels in first row, multiple chart series described in legend

No labels in first row, one chart series (no legend)

No labels in first row, multiple chart series described in legend

Accessing the PTS Dot Plot Utility via VBA

The ability to call the PTS Dot Plot Utility from other VBA procedures has been incorporated in this version of the utility. The following abbreviated explanation should help experienced programmers get started.

The core of the PTS Dot Plot Utility is a procedure called PTS_DotPlot, which can be called from other VBA procedures. The syntax of the PTS_DotPlot function is:

        Function PTS_DotPlot(ChartData As Range, _
                             FirstLabels As Boolean, _
                             HGridLines As Boolean, _
                             VGridLines As Boolean, _
                             ConnectingLines As Boolean) _
                             As Chart

ChartData is the range containing the data to chart, laid out in a grid with labels in the first column, with numerical data in subsequent columns, and optionally with series labels in the top row.

FirstLabels is true if the data range contains series labels in the top row.

HGridLines, VGridLines, and ConnectingLines are true if the Dot Plot is to have horizontal gridlines, vertical gridlines, and lines connecting data points.

To create a Dot Plot programmatically, a calling procedure could select the data range, then run this line of code:

        PTS_DotPlot Selection, True, True, True, True

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

        Set chtDotPlot = PTS_DotPlot(Selection, True, True, True, True)

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

Contact Me