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