Macro (SVB) Program Examples - Creating a Random Sized Spreadsheet
This example application shows how to create a new STATISTICA
Spreadsheet
of a specific size. The program will create a dialog box where the user
can enter the respective spreadsheet dimensions, then display another
dialog box prompting the user to select a variable to highlight in red.
Sub Main
On Error
GoTo Leave ' go here if dialog box cancels
' Create a new, empty, spreadsheet
Dim S As Spreadsheet
Set S = Application.Spreadsheets.New
Begin Dialog UserDialog
290,119,"Select Spreadsheet
Size" ' %GRID:10,7,1,1
TextBox 170,14,80,21,.Rows
TextBox 170,49,80,21,.Columns
OKButton 40,91,90,21
Text 40,14,110,14,"Number of Rows:",.Text1
Text 20,49,130,14,"Number of Columns:",.Text2
CancelButton 150,91,100,21
End Dialog
Dim dlg As UserDialog
' Prefill with current size of the spreadsheet
dlg.Rows = CStr(S.NumberOfCases)
dlg.Columns = CStr(S.NumberOfVariables)
' display the dialog
Dialog dlg
Dim R As Integer
Dim C As Integer
' read the values that were set
R = CInt(dlg.Rows)
C = CInt(dlg.Columns)
'increase or decrease the size of the Spreadsheet as necessary
If (R > S.NumberOfCases)
Then ' need to increase number of cases
S.AddCases(S.NumberOfCases, R-S.NumberOfCases)
ElseIf (R < S.NumberOfCases)
Then ' need to decrease number of cases
S.DeleteCases(R+1, S.NumberOfCases)
End If
If (C > S.NumberOfVariables)
Then ' need to increase number of variables
S.AddVariables("NewV",
S.NumberOfVariables,
C-S.NumberOfVariables)
ElseIf (C < S.NumberOfVariables)
Then ' need to decrease number of cases
S.DeleteVariables(C+1,
S.NumberOfVariables)
End If
' fill the Spreadsheet with random values
S.EntireRange.FillRandomValues
S.Visible = True
' Make column 1 be sequential
For i = 1 To
S.NumberOfCases
S.value(1, i) = i ' Set Column 1 to sequential numbers
Next
S.Variable(1).Font.Bold = True
' Ask which variable to sort by
nv = 0 ' initial number of
selected variables
Dim MyVars(1 To 1) As
Long ' returned array
of selected vars (index from 1)
ret = SelectVariables1(S,
"Select Variable to sort by", 1, 1, MyVars, _
nv, "Variables:")
If ret And nv
> 0 Then
' if a good return, and a variable was selected
'copy the selected variable number
vNo = MyVars(1)
'sort by the selected variable
S.SortData(vNo)
'Set the entire column Red
S.Variable(vNo).Font.Color = RGB(255,0,0)
End If
Leave: 'Exit program
End
Sub