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 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
Copyright © 2021. Cloud Software Group, Inc. All Rights Reserved.