Macro (SVB) Programs Example - Writing a Custom Statistica Application with User-Defined Dialog Boxes

This example illustrates how a custom statistical procedure can be developed by combining the power and flexibility of Statistica's statistical analysis routines with custom-defined dialog boxes and dialog box procedures.

' This program will use the Basic Statistics Breakdown options to
' find the dependent variables in a list of dependent variables
' that are significantly related to a set of categorical predictors;
' the program also computes the overall R-Square values for those
' variables.
' The following statement makes all arrays by default 1-referenced;
' thus, the declaration "Dim x(1 to 10) as Double" is the same
' by default as "Dim x(10) as Double"

Option
Base 1

Public
Msg As String ' Total number of variables
Public
Nvars As Long ' Number of dependent variables
Public
NSignificantDVs As Integer ' Number of related DV's
Dim
RSquares () As Double ' Dv, Rsquare, and p-value
Dim
Vars () As Integer
Dim
VarNames () As String
Public
AlphaValue As Double ' p-value (significance) criterion
Public
ndep As Integer ' Number of independent variables
Public
nindep As Long ' Number of significantly related DV's
Public
DependentVariableList() As Long
Public
IndependentVariableList () As Long
Dim
ResWB As Workbook
Dim
folder As WorkbookItem

Sub
Main
' Error handling: Detect when there is no input Spreadsheet

On Error GoTo NoInputSpreadsheet

' Number of variables in active (input) Spreadsheet

Nvars = ActiveDataSet.NumberOfVariables

' Install default erorr handling

On Error GoTo Finish

' Dimension variable lists, to be used for variable
' selection dialog

ReDim DependentVariableList(1 To Nvars)
ReDim
IndependentVariableList (1 To Nvars) As Long
Dim
i As Integer 

' Display dialog for variable selection, etc.

If Not InputDialog Then GoTo Finish

' Set up RSquares array, as a 2-dimensional array

ReDim RSquares (1 To ndep, 1 To 2)
ReDim
Vars (1 To ndep) As Integer
ReDim
VarNames (1 To ndep) As String

' Find significantly predicted DV's

If Not FindSignificantlyPredictedDVs Then

MsgBox "None of the DV's are related to predictors."
GoTo
Finish

End If 

' Make new workbook, for display of results

Set ResWB = Workbooks.New
ResWB.Visible = True

' Display main results

If Not MakeSummarySpreadsheet Then GoTo Finish

If MsgBox ( _
"Do you want to perform detailed analyses for these DV's?", vbOkCancel, _
"Detailed Analyses?") = vbCancel Then GoTo Finish

'Compute detailed results, if requested

If Not ComputeDetailedStatistics Then GoTo Finish 

Finish:

Exit Sub 

NoInputSpreadsheet:

MsgBox "Open a data file (Spreadsheet) for this analysis", _
vbCritical

Exit Sub

End Sub

' This function displays and services the specifications dialog

Function
InputDialog As Boolean
On
Error GoTo Finish
InputDialog=False

Begin Dialog UserDialog 390,147, _
"Search for significant DV's", .UI ' %GRID:10,7,1,1

PushButton 20,14,120,21,"Variables",.VariableSelection
Text
40,42,70,14,"Grouping:",.Text1
Text
110,42,180,14,"none",.Text2
Text
30,56,80,14,"Dependent:",.Text3
Text
110,56,180,14,"none",.Text4
Text
30,84,50,14,"Alpha:",.Text6
TextBox
80,77,90,21,.AlphaValue
Text
20,112,360,28, _
"Find all DV's related to the predictors with p<alpha.", .Text5

OKButton 310,14,70,21,.OkButton
CancelButton
310,42,70,21,.CancelButton
End
Dialog

Dim dlg As UserDialog
' Initialize the AlphaValue field

dlg.AlphaValue=".05"

TryAgain:

On Error GoTo Finish
Dialog
dlg
On
Error GoTo BadAlphaValue
AlphaValue = CDbl(dlg.AlphaValue)

InputDialog=True

Finish:

Exit Function

BadAlphaValue:

MsgBox "Bad alpha value; please specify a valid alpha value."
GoTo
TryAgain

End Function

' Dialog function; services the dialog

Private
Function UI(DlgItem$, Action%, SuppValue&) As Boolean

Dim ok As Boolean

Select Case Action%
Case
1 ' Dialog box initialization
Case
2 ' Value changing or button pressed

UI = True
Select Case
DlgItem

Case "CancelButton"

UI=False

 

Case "OkButton"

ok=False
If
ndep<2 Or nindep<1 Then

ok=True
GoTo
DoVariables

End If
UI=False 

' Variable selection dialog

Case "VariableSelection"

ok=False

DoVariables:

ret = SelectVariables2 (ActiveDataSet, _
"Variables for Analysis", _
2, Nvars, DependentVariableList, ndep, _
"Dependent variables:", _
1, 6, IndependentVariableList, nindep, _
"Independent (grouping) variables: ")

If ndep>0 Then

DlgText "Text4", "Selected"

Else

DlgText "Text4", "none"

End If

 

If nindep>0 Then

DlgText "Text2", "Selected"

Else

DlgText "Text2", "none"

End If

 

End Select

 

End Select

 

End Function

' This function sets up the Spreadsheet with a user-defined
' number of rows and columns

Function
SetSpreadsheetNumberOfRowsAndColumns( _
ii As Integer, _
jj As Integer, _

Final As Spreadsheet) As Boolean

On Error GoTo Finish
Dim
nr As Integer
Dim
nc As Integer
SetSpreadsheetNumberOfRowsAndColumns=True
If
(Final.NumberOfCases<ii) Then

Final.AddCases(Final.NumberOfCases, _
ii-Final.NumberOfCases)

Else

Final.DeleteCases(ii+1, Final.NumberOfCases)

End If

If (Final.NumberOfVariables<jj) Then

Final.AddVariables("Var",Final.NumberOfVariables, _
jj-Final.NumberOfVariables)

Else

Final.DeleteVariables(jj+1, Final.NumberOfVariables)

End If

Finish:
End Function


' This function finds the dependent variables that
' are significantly related to the set of categorical
' predictor variables

Function
FindSignificantlyPredictedDVs As Boolean

On Error GoTo Finish

Dim
i As Integer
Dim
j As Integer
Dim
k As Integer
Dim
ncolumns As Integer
Dim
x8,SSTotal,SSresidual As Double

NSignificantDVs=0
FindSignificantlyPredictedDVs=False
For
i=1 To ndep
Vars (i)=DependentVariableList(i)

For j=1 To 2

RSquares(i,j)=0

Next j

Next i

ReDim IDVars(1 To nindep) As Integer

For
i=1 To nindep

IDVars(i) = IndependentVariableList(i)

Next i

Set newanalysis = Analysis (scBasicStatistics)

With
newanalysis.Dialog

.Statistics = scBasBreakdowns

End With

newanalysis.Run

'Format the variable list into a string

Dim
VarString As String

'get the DVs
For
i = 1 To UBound(Vars)

VarString = VarString + Str(Vars(i))

Next i

'get the IVs
VarString = VarString + "|"

For i = 1 To UBound(IDVars)

VarString = VarString + Str(IDVars(i))

Next i

With newanalysis.Dialog

.Variables = VarString
.Codes = ""

End With

newanalysis.Run

Set AnovaSpreadsheet=newanalysis.Dialog.AnalysisOfVariance
ncolumns=AnovaSpreadsheet.Item(1).NumberOfVariables

k=0
For
i=1 To ndep

x8=AnovaSpreadsheet.Item(1).value(i,ncolumns)
If
x8<=AlphaValue Then

k=k+1
VarNames(k)=ActiveDataSet.VariableName(Vars(i))
Vars(k)=DependentVariableList(i)
RSquares(k,2)=x8

' Compute R-Square

SSTotal=AnovaSpreadsheet.Item(1).value(i,1)+ _
AnovaSpreadsheet.Item(1).value(i,4)

SSresidual=AnovaSpreadsheet.Item(1).value(i,4)
If
(SSTotal>0) Then

RSquares(k,1)=1-SSresidual/SSTotal

End If

End If

Next i

NSignificantDVs=k
FindSignificantlyPredictedDVs =(NSignificantDVs>0)

Finish:
End Function


' This function generates the summary results Spreadsheet

Function
MakeSummarySpreadsheet As Boolean

Dim Final As Spreadsheet
MakeSummarySpreadsheet=False
Set
Final = Application.Spreadsheets.New
Final.InputSpreadsheet=False
If (Not
SetSpreadsheetNumberOfRowsAndColumns( _
NSignificantDVs,2,Final)) _

Then
GoTo Finish

Final.VariableName(1) = "R-square"
Final.VariableFormatString(1)="#.#####"
Final.VariableName(2) = "p-value"
Final.VariableFormatString(2)="#.#####"
Final.CaseNameLength=20
Final.CaseNameWidth=1.5
For
i=1 To NSignificantDVs

Final.value(i,1)=RSquares(i,1)
Final.value(i,2)=RSquares(i,2)
Final.CaseName(i)=VarNames(i)

Next i

Set folder = ResWB.InsertFolder(ResWB.Root,)

folder.Name = "Summary R-square"
Dim
si As WorkbookItem
Set
si = ResWB.InsertObject(Final, folder, scWorkbookLastChild)
MakeSummarySpreadsheet = True 

Finish:
End Function


' This function computes detailed statistics for a selected
' set of variables; it is based on a macro recording from
' Basic Statistics

Function
ComputeDetailedStatistics As Boolean

Dim Indeps() As Integer
Dim
Deps() As Integer
Dim
Tempo As String

Dim
i As Integer
Dim
j As Integer

On
Error GoTo Finish
ComputeDetailedStatistics=False
Set
newanalysis = Analysis (scBasicStatistics)
With
newanalysis.Dialog

.Statistics = scBasBreakdowns

End With

newanalysis.Run

ReDim
Indeps (1 To nindep) As Integer
For
i=1 To nindep

j=IndependentVariableList(i)
Indeps(i)=j

Next i

ReDim
Deps (1 To ndep) As Integer
Tempo=""
For
i=1 To ndep

j=DependentVariableList(i)
Tempo=Tempo+Str(j)+" "
Deps(i)=j

Next i

'Format the variable list into a string

Dim
VarString As String

'get the DVs
For
i = 1 To UBound(Deps)

VarString = VarString + Str(Deps(i))

Next i

'get the IVs
VarString = VarString + "|"

For i = 1 To UBound(Indeps)

VarString = VarString + Str(Indeps(i))

Next i

With newanalysis.Dialog

.Variables = VarString
.
Codes = ""

End With

newanalysis.Run

newanalysis.Dialog.ResultsVariables = Tempo

If
(nindep>=2) Then

newanalysis.Dialog.ResultsSelection = "1 | 2 "

End If

Set
sg=newanalysis.Dialog.CategorizedMeansInteractionPlots

Set
folder = ResWB.InsertFolder(ResWB.Root,)

folder.Name = "Plot of interactions"
Dim
si As WorkbookItem
For
i=1 To sg.Count

Set si = ResWB.InsertObject(sg.Item(i), _
folder, scWorkbookLastChild)

Next i

With
newanalysis.Dialog

.DependentVariablesForPostHocTests = Deps

End With

Set
s=newanalysis.Dialog.ScheffeTest
Set
folder = ResWB.InsertFolder(ResWB.Root,)

folder.Name = "Scheffe post-hoc tests"
For
i=1 To ndep

Set si = ResWB.InsertObject(s.Item(i), folder, _
scWorkbookLastChild
)

Next i
ComputeDetailedStatistics=True

Finish:
End Function

If you run this program using the example data file Exp.sta and request detailed analyses, then various plots and summary spreadsheets will be computed and displayed in a workbook.

 

Note that further customization of programs, in fact of the entire Statistica system, can be achieved by controlling events related to the results spreadsheets and graphs (see Controlling Statistica Events with SVB Programs). For example, you could further "beautify" the program by launching additional analyses when the user clicks on particular cells in the initial results spreadsheet, etc.