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)
| 
