SigmaPlot Macro Sample Code

 

Some very useful programming procedures have been developed for the macros distributed with SigmaPlot.

Some of these code samples and an example macro where each has been used, are described here.

 

Code Sample Macro Function
Populate Array Batch Process Excel Files Populates array with specific notebook items
Empty Column Area Below Curves Determines if a worksheet column is empty
Create Worksheet Frequency Plot Creates worksheet object and opens it
Find Plots Color Transition Values Creates current page and graph objects; generates list of plots in graph
First Empty Column Area Below Curves Determine range of worksheet data; find first empty column
List of Data Columns F-test Comparison of Curves Creates list of columns containing data
First Empty Power Spectral Density Creates “first empty” column option in dialog box
Transform F-test Comparison of Curves Creates and runs a transform
Reports F-test Comparison of Curves Creates and numbers reports
Column Length F-test Comparison of Curves Returns column length
Fit Library Batch Process Excel Files Opens and defines a fit library
Curve Fit Batch Process Excel Files Runs a curve fit
Select Plot Batch Process Excel Files Selects the current plot
Call Automated Application Paste to PowerPoint Slide Calls another automated application like PowerPoint or Word
Column title Rank and Percentile Adds a column title

Populate Array
Creates an array populated by a specific set of notebook items, in this case, equation names. The item type is specified using the ItemType property where ItemType = 6 corresponds to a fit item (equation).

DIM ITEMLIST$()
DIM I, INDEX AS INTEGER
I=0
INDEX = 0
FOR I = 0 TO FITFILE.NOTEBOOKITEMS.COUNT - 1
       IF FITFILE.NOTEBOOKITEMS(I).ITEMTYPE = 6 THEN
              REDIM PRESERVE ITEMLIST$(INDEX)
              ITEMLIST(INDEX) = FITFILE.NOTEBOOKITEMS(I).NAME
              INDEX = INDEX + 1
       END IF
NEXT I

Empty Column
This subroutine determines if a column is an empty column.

PUBLIC FUNCTION EMPTY_COL(COLUMN AS VARIANT, COLUMN_END AS VARIANT)
'DETERMINES IF A COLUMN IS EMPTY
       DIM WORKSHEETTABLE AS OBJECT
       SET WORKSHEETTABLE = ACTIVEDOCUMENT.CURRENTDATAITEM.DATATABLE
       DIM I AS LONG
       DIM EMPTY_CELL AS BOOLEAN
       FOR I = 0 TO COLUMN_END STEP 3 'CHANGE THE STEP VALUE TO CHANGE 
       THE SAMPLING INTERVAL. SMALL SAMPLE SIZE = SLOW OPERATION
              IF WORKSHEETTABLE.CELL(COLUMN,I) = "-1.#QNAN" THEN EMPTY_CELL = TRUE
              IF WORKSHEETTABLE.CELL(COLUMN,I) <> "-1.#QNAN" THEN GOTO NOTEMPTY
       NEXT I
       EMPTY_COL = EMPTY_CELL
       GOTO EMPTYCOL:
       NOTEMPTY:     
       EMPTY_COL = FALSE
       EMPTYCOL:
END FUNCTION

Create Worksheet
Creates an object consisting of the current Worksheet.

DIM CURRENTWORKSHEET
SET CURRENTWORKSHEET = ACTIVEDOCUMENT.CURRENTDATAITEM
CURRENTWORKSHEET.OPEN 'OPENS/SELECT DEFAULT WORKSHEET AND SETS FOCUS

Find Plots
Creates objects consisting of the current Page and Graph, and generates a list of Plots for the Graph.

DIM INDEX, SPPAGE, SPGRAPH, NUMBERPLOTS, PLOTLIST$()
SET SPPAGE = ACTIVEDOCUMENT.CURRENTPAGEITEM
SPPAGE.OPEN
SET SPGRAPH = SPPAGE.GRAPHPAGES(0).CURRENTPAGEOBJECT(GPT_GRAPH)
NUMBERPLOTS = SPGRAPH.PLOTS.COUNT
REDIM PLOTLIST$(NUMBERPLOTS - 1)
FOR INDEX = 0 TO NUMBERPLOTS - 1
       PLOTLIST(INDEX) = SPGRAPH.PLOTS(INDEX).NAME
NEXT INDEX

First Empty Column
Determines the data range and defines the first empty column (=LastColumn+1).

DIM WORKSHEETTABLE AS OBJECT
SET WORKSHEETTABLE = ACTIVEDOCUMENT.CURRENTDATAITEM.DATATABLE
DIM LASTCOLUMN AS LONG
DIM LASTROW AS LONG
LASTCOLUMN = 0
LASTROW = 0 
WORKSHEETTABLE.GETMAXUSEDSIZE(LASTCOLUMN,LASTROW)

List of Data Columns
Sorts through columns and creates a list of data columns. The list consists of column numbers or column titles if they exist. Uses object definitions from code examples above, as well as the empty_col function in the Empty Column description above.

DIM USEDCOLUMNS$(), LISTEDCOLUMNS(), LISTINDEX, COLCONTENTS, COLTITLE
REDIM USEDCOLUMNS$(LASTCOLUMN -1)
REDIM LISTEDCOLUMNS(LASTCOLUMN -1)
LISTINDEX = 0
FOR INDEX = 0 TO LASTCOLUMN - 1
       COLCONTENTS = EMPTY_COL(INDEX, LASTROW) 
       COLTITLE = WORKSHEETTABLE.CELL(INDEX,-1) 'RETRIEVE COLUMN TITLE
       IF COLCONTENTS = TRUE THEN GOTO NEXTINDEX
       IF COLCONTENTS = FALSE THEN   'IF THE FIRST CELL IS NOT EMPTY
              SELECT CASE COLTITLE
              CASE "-1.#QNAN"
                     USEDCOLUMNS$(INDEX) = "COLUMN " + CSTR(INDEX + 1)
                     LISTEDCOLUMNS(LISTINDEX) = CSTR(INDEX + 1)
                     LISTINDEX = LISTINDEX + 1
              CASE ELSE
                     USEDCOLUMNS$(INDEX) = COLTITLE 'IF TITLE IS PRESENT USE TITLE
                     LISTEDCOLUMNS(LISTINDEX) = CSTR(INDEX + 1)
                     LISTINDEX = LISTINDEX + 1
              END SELECT
       END IF
       NEXTINDEX:
NEXT INDEX

First Empty
Creates a “First Empty” column option in a dialog box.

BEGIN DIALOG USERDIALOG 391,104,"EMPTY COLUMN" ' %GRID:10,7,1,0
       OKBUTTON 283,11,96,19
       TEXT 14,10,120,14,"&RESULT COLUMN",.TEXT1
       TEXTBOX 155,9,90,18,.RESULTSCOL
END DIALOG
DIM DLG AS USERDIALOG
IF DLG.RESULTSCOL = "" THEN DLG.RESULTSCOL = "FIRST EMPTY"
WORKSHEETTABLE.GETMAXUSEDSIZE(LASTCOLUMN,LASTROW) 'RE-INITIALIZE VARIABLES
IF     DLG.RESULTSCOL = "FIRST EMPTY" THEN
       DLG.RESULTSCOL = CSTR(LASTCOLUMN + 1)
ELSE
       DLG.RESULTSCOL = DLG.RESULTSCOL
END IF

Transform
Creates and runs a transform. 9 is the item type for a transform.

DIM SPTRANSFORM AS OBJECT
SET SPTRANSFORM = ACTIVEDOCUMENT.NOTEBOOKITEMS.ADD(9)
SPTRANSFORM.OPEN
SPTRANSFORM.TEXT = "<TRANSFORM TEXT>" 
'USE + VBCRLF + _ TO CREATE A LINE BREAK
'*****************************************************
'* DEBUG TRANSFORM CODE; THIS OPENS THE TRANSFORM IN *

'* THE TRANSFORMS DIALOG FOR VIEWING AND EDITING     *
'*****************************************************
'SPTRANSFORM.RUNEDITOR 
SPTRANSFORM.EXECUTE
SPTRANSFORM.CLOSE(FALSE)
HERE IS THE TRANSFORM TEXT FROM F-TEST COMPARISON OF CURVES
'APPROXIMATE P VALUE FOR F DISTRIBUTION A&S, EQ. 26.6.15, P. 947
'NORMAL DISTRIBUTION APPROXIMATION FOR P VALUE A&S, EQ. 26.2.17, P 932
SPTRANSFORM.TEXT = "N=SIZE(COL(" +CS1+ "))" + VBCRLF + _
"SS1=TOTAL(COL(" +CS1+ ")^2)"  + VBCRLF + _
"SS2=TOTAL(COL(" +CS2+ ")^2)" + VBCRLF + _
"F = ((SS1-SS2)/SS2)*((N-" +N2+ ")/(" +N2+ "-" +N1+ "))" + VBCRLF + _
"N1=" +N2+ "-" +N1+ VBCRLF + _
"N2=N-" +N2+ VBCRLF + _
"X=(F^(1/3)*(1-2/(9*N2))-(1-2/(9*N1)))/SQRT(2/(9*N1)+F^(2/3)*2/(9*N2))" + VBCRLF + _
"PI=3.1415926" + VBCRLF + _
"Z=EXP(-X^2/2)/SQRT(2*PI)" + VBCRLF + _
"T=1/(1+.2316419*X)" + VBCRLF + _
"P=Z*(.31938153*T-.356563782*T^2+1.781477937*T^3-1.821255978*T^4+1.3374429*T^5)" +
VBCRLF + _
"COL(" +CRES+ ")={" + CHR(34) + "F =" + CHR(34) + SEPARATOR + " " + CHR(34) + "P =" +
CHR(34) + "}" + VBCRLF + _
"COL(" +CRES+ "+1)={F" + SEPARATOR + "P}" + VBCRLF
"P=Z*(.31938153*T-.356563782*T^2+1.781477937*T^3-1.821255978*T^4+1.3374429*T^5)" +
VBCRLF + _
"COL(" +CRES+ ")={" + CHR(34) + "F =" + CHR(34) + SEPARATOR + " " + CHR(34) + "P =" +
CHR(34) + "}" + VBCRLF + _
"COL(" +CRES+ "+1)={F" + SEPARATOR + "P}" + VBCRLF

Reports
Creates and numbers reports.

DIM SPREPORT AS OBJECT
SET SPREPORT = ACTIVEDOCUMENT.NOTEBOOKITEMS.ADD(CT_REPORT)
SPREPORT.NAME = "<NAME OR TYPE> " + SPREPORT.NAME
SPREPORT.TEXT = "<REPORT TEXT>"
HERE IS THE REPORT NAME AND TEXT FROM F-TEST COMPARISON OF CURVES
SPREPORT.NAME = "F-TEST " + SPREPORT.NAME
IF VAR2>0.05 THEN
       GOTO LABEL2 
END IF
IF VAR2<0.05 THEN
       GOTO LABEL1
END IF
LABEL1:
SPREPORT.TEXT = "F= "+VAR1+VBCRLF+"P= "+VAR2+VBCRLF+"THE MORE COMPLEX 
EQUATION PROVIDES A SIGNIFICANTLY BETTER FIT."
GOTO FINISH
LABEL2:
SPREPORT.TEXT = "F= "+VAR1+VBCRLF+"P= "+VAR2+VBCRLF+"THE MORE COMPLEX 
EQUATION DOES NOT PROVIDE A SIGNIFICANTLY BETTER FIT."
GOTO FINISH

Column Length
Returns column lengths.

PUBLIC FUNCTION COLUMN_SIZE(COLUMN AS VARIANT, COLUMN_END AS VARIANT)
'RETURNS COLUMN SIZE OF ENTRIES WITHIN A RANGE
       DIM WORKSHEETTABLE AS OBJECT
       SET WORKSHEETTABLE = ACTIVEDOCUMENT.CURRENTDATAITEM.DATATABLE
       DIM I
       DIM SIZE AS LONG
              I = 0
              SIZE = 0
       DO 
              IF WORKSHEETTABLE.CELL(COLUMN,I) <> "-1.#QNAN" THEN SIZE = SIZE + 1
              I = I + 1
       LOOP UNTIL I = COLUMN_END
       COLUMN_SIZE = SIZE
END FUNCTION

Fit Library
Opens and defines a Curve Fitting Library.

DIM FITLIBRARY$
'DEFINES THE EQUATION SOURCE.  EDIT TO USE A DIFFERENT FIT LIBRARY
FITLIBRARY = "STANDARD.JFL"
'OPEN THE FIT LIBRARY
NOTEBOOKS.OPEN(PATH + "\" + FITLIBRARY, ".JFL")
DIM FITFILE AS OBJECT
SET FITFILE = NOTEBOOKS(PATH + "\" + FITLIBRARY)
FITFILE.VISIBLE=FALSE

Curve Fit
Example of running a Curve Fit; uses fit library defined above. Modify the various properties to suit your application.

DIM FITEQUATION$
FITEQUATION = "<EQUATION NAME>"
DIM FITOBJECT AS OBJECT
SET FITOBJECT = FITFILE.NOTEBOOKITEMS(FITEQUATION)
FITOBJECT.OPEN
FITOBJECT.DATASETTYPE = CF_XYPAIR
FITOBJECT.VARIABLE("X") = "COL(1)"
FITOBJECT.VARIABLE("Y") = "COL(2)"
FITOBJECT.RUN
FITOBJECT.OUTPUTREPORT = TRUE
FITOBJECT.OUTPUTEQUATION = FALSE
FITOBJECT.RESIDUALSCOLUMN = -1
FITOBJECT.PREDICTEDCOLUMN = -1
FITOBJECT.PARAMETERSCOLUMN = -1
FITOBJECT.OUTPUTGRAPH = FALSE
FITOBJECT.OUTPUTADDPLOT = TRUE 'A PLOT MUST BE SELECTED AND THE PAGE OPENED
FITOBJECT.EXTENDFITTOAXES = TRUE
FITOBJECT.ADDPLOTGRAPHINDEX = 0
FITOBJECT.XCOLUMN = -1
FITOBJECT.YCOLUMN = -1
FITOBJECT.ZCOLUMN = -2
FITOBJECT.FINISH

Select Plot
Selects the current plot. The plot must be selected to modify its properties.

DIM SPPAGE
SET SPPAGE = ACTIVEDOCUMENT.CURRENTPAGEITEM
SPPAGE.GRAPHPAGES(0).GRAPHS(0).PLOTS(0).SELECTOBJECT
SPPAGE.OPEN

Call Automated Application
Calls another automated application (in this case, PowerPoint). Note that in order to call objects, properties and methods from the other application, you must also add it as a Reference. Use the Macro References button in the SigmaPlot macro editor.

DIM PPAPP AS OBJECT
SET PPAPP=CREATEOBJECT("POWERPOINT.APPLICATION")
PPAPP.VISIBLE=TRUE

Column Title
Adds a column title to a column in the worksheet.

DIM WORKSHEETTABLE AS OBJECT
SET WORKSHEETTABLE = ACTIVEDOCUMENT.CURRENTDATAITEM.DATATABLE
WORKSHEETTABLE.NAMEDRANGES.ADD("<TITLE>", <COLUMN NUMBER>,0,1,-1, TRUE)

Try SigmaPlot FREE for 30 Days!