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

' 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.