Macro (SVB) Programs Example - Basic Spreadsheet Manipulation

This example application shows how to do some basic spreadsheet manipulation such as adding and deleting variables. Open the adstudy.sta data file:

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


'add another variable (column) to the spreadsheet


'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


MsgBox "Mean = " & ave & vbCrLf & "Std. Dev. = " & sd

End Sub