Macro (SVB) Programs Example - Basic Spreadsheet Manipulation

Ribbon bar. Select the Home tab. In the File group, from the Open menu, select Open Examples to display the Open a Statistica Data File dialog. Double-click the Datasets folder, and then open the data set.

Classic menus. Open the data file by selecting Open Examples from the File menu to display the Open a Statistica Data File dialog. The data file is located in the Datasets folder.

Sub Main
 
'create a new spreadsheet variable to be used to refer  'to the Adstudy.sta spreadsheet
 
Dim ss As Spreadsheet
 
'ActiveDataSet is the spreadsheet that is currently open  'we use "Set" because a spreadsheet is an object
 
Set ss = ActiveDataSet
 
'Note: in Statistica the rows of a spreadsheet are referred to  'as cases, the columns are referred to as variables  'loop through all cases in the GENDER variable,  'change MALE to WHALE
 
Dim i As Long
 
For i = 1 To ss.NumberOfCases
 
'for text fields, use ss.Text() when retrieving the contents of a  'cell, and use ss.Value() when setting the contents of a cell
 
If ss.Text(i,1) = "MALE" Then
 
ss.Value(i,1) = "WHALE"
 
End If
 
Next i
 
'loop through all cases in the MEASURE01 variable (column)  'and double them
 
Dim currVal As Double
 
For i = 1 To ss.NumberOfCases
 
for numerical fields, use ss.Value() when retrieving or  'setting the contents of a cell
 
currVal = ss.Value(i,3)  currVal = currVal * 2  ss.Value(i,3) = currVal
 
Next i
 
'delete all variables (columns) in the spreadsheet to the right  'of the MEASURE01 variable
 
ss.DeleteVariables(4,ss.NumberOfVariables)
 
'add another variable (column) to the spreadsheet
 
ss.AddVariables("Stuff",3,1,scDouble)
 
'fill in the new variable (column) with random numbers  'between 0 and 10,  'leave every 10th value blank
 
For i = 1 To ss.NumberOfCases
 
If(i Mod 10) <> 0 Then
 
ss.Value(i,4) = 10*Rnd()
 
End If
 
Next i
 
'look for missing values in the new variable (column)
 
For i = 1 To ss.NumberOfCases
 
If ss.MissingData(i,4) Then
 
MsgBox "Missing case " & CStr(i)
 
End If
 
Next i
 
'find the mean and standard deviation of the data in the new variable (column)
 
Dim ave As Variant  Dim sd As Variant
 
ss.Variable(4).ColumnStats(scMeans,ave)  ss.Variable(4).ColumnStats(scStdDev,sd)
 
MsgBox "Mean = " & ave & vbCrLf & "Std. Dev. = " & sd
 
End Sub