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