Create a SigmaPlot Graph in Excel


Examples have been given showing how to copy a SigmaPlot graph to PowerPoint and Word. In these cases macros were written in SigmaPlot. You can also write a macro in Excel (or Word or PowerPoint) that works with SigmaPlot graphs. The Excel macro discussed here will:

  • Open SigmaPlot
  • Copy the Excel worksheet data
  • Create a worksheet in SigmaPlot and paste the Excel data into it
  • Create the SigmaPlot graph (in SigmaPlot)
  • Copy the SigmaPlot graph and paste it on the Excel worksheet

These are the same steps as used by the SigmaPlot macros except in this case you have to open SigmaPlot and get the Excel data to it.

A simple example of a macro that creates a SigmaPlot graph in Excel may be downloaded and studied. This macro creates a scatter plot with regression line from two selected columns of Excel data and places the graph on the Excel worksheet.

Sub Create_ScatterPlot()

'* This macro creates a scatter plot with a linear regression in SigmaPlot *
'* from the selected Excel data, using the first two selected columns. *
'* The resulting graph is pasted to the Excel sheet. /07/2001 John Kuo *

'Open the SigmaPlot application
Dim SPApp As Object
Set SPApp = CreateObject("SigmaPlot.Application.1")
SPApp.Visible = True
SPPath = SPApp.Path
'Define the current Excel worksheet
Dim ExcelSheet As Object
Set ExcelSheet = ActiveSheet
'Copy the selected Excel data
'Open a SigmaPlot notebook and paste the data into its worksheet
Dim SPNotebook, SPGraph, SPWorksheet, SPPage As Object
Set SPWorksheet = SPApp.ActiveDocument.CurrentDataItem
'Create a SigmaPlot page
Set SPPage = SPApp.ActiveDocument.NotebookItems.Add(CT_GRAPHICPAGE)
'Define the SigmaPlot worksheet columns to plot
Dim ColumnsPerPlot(), PlotColumnCountArray()
ReDim ColumnsPerPlot(2, 1)
ColumnsPerPlot(0, 0) = 0
ColumnsPerPlot(1, 0) = 0
ColumnsPerPlot(2, 0) = 31999999
ColumnsPerPlot(0, 1) = 1
ColumnsPerPlot(1, 1) = 0
ColumnsPerPlot(2, 1) = 31999999
ReDim PlotColumnCountArray(0)
PlotColumnCountArray(0) = 2
'Create the graph
SPPage.CreateWizardGraph "Scatter Plot", "Simple Regression", "XY Pair", ColumnsPerPlot, PlotColumnCountArray
'Select and copy the SigmaPlot graph
'Paste the graph to the Excel worksheet
'Close SigmaPlot notebook
SPApp.ActiveDocument.Close (False)
'Close the SigmaPlot application if there are "no" open notebooks. Note that the open
'notebook count is 3 when there are no open notebooks. Why? In reality, there are always
'at least three open notebooks even when none are visible: the template notebook,
'the macro library, and the graph gallery.
If SPApp.Notebooks.Count = 3 Then SPApp.Quit
End Sub

After downloading the Create SigmaPlot Graph.xls Excel worksheet, place it into the \Program Files\Microsoft Office\Office\XLStart folder. Then start Excel and enter data in two columns of the Excel worksheet. Be sure to select the two columns to be graphed as the macro will copy from the selected columns.

Then from the Developer tab, click the Macros button in the Code group and run the macro:

to produce the SigmaPlot graph in the Excel worksheet.

When you use a macro frequently it is helpful to create a button in the Quick Access Toolbar to access it.

Click the Quick Access Toolbar dropdown:

and select “More Commands”:

Select “Macros” from “Choose commands from”, then select Create_ScatterPlot:

and click the Add button:

Click the “Modify button” to select an icon:

Click Ok and the macro is now available on the Quick Access Toolbar:

Try SigmaPlot FREE for 30 Days!