Example - Accessing Variables (Columns) in Spreadsheets via the .VData(i) Property
Other useful properties of spreadsheet objects to access specific variables (columns) or cases (rows) of data are the VData(i) and .CData(i) properties. For example, the .VData(i) property allows you to copy individual columns from a spreadsheet to a 1-referenced array (vector) of Double values, and back. Here is an example:
' This program illustrates the Spreadsheet .VData(i) property, ' and how to access efficiently entire columns in a Spreadsheet ' for further processing with STATISTICA matrix functions. ' Note that property CData(i) can be used analogously, to access ' the contents of an entire case (row) in a Spreadsheet. ' The program will first open example data file Exp.sta, compute ' the means for variables Correct1 through Correct3 within each ' case or observation, subtract the means from the respective ' values in those variables, and then perform an analysis with ' the Basic Statistics Breakdowns option.
Sub Main
Dim InputFile As Spreadsheet Dim i As Long, j As Long Dim nr As Long, nc As Long
' Open the example data file Exp.sta; you may have to ' edit the file location, according to the installation of ' STATISTICA on your computer.
Set InputFile=Spreadsheets.Open( _ Path + "\Examples\DataSets\Exp.sta")
' Vector v will be used as a "work array", to hold the ' columns of the input data Spreadsheet as they are copied.
Dim v() As Double InputFile.Visible = True nr = InputFile.NumberOfCases
' The following arrays are used in the computations via ' the matrix functions.
ReDim x(1 To nr,1 To 3) As Double ReDim xt(1 To 3, 1 To nr) As Double ReDim Means(1 To nr) As Double
' Note that variables (columns) 6 through 8 in the input ' Spreadsheet hold the data for variables Correct1 through ' Correct3.
For i=6 To 8
' Here the .Vdata(i) property is used to retrieve the i'th ' column from the data file, and transfer the contents into ' vector v.
v=InputFile.VData(i)
' Copy vector v to the i-5'th column in matrix x.
MatrixObject.MatrixSetColumn (x, i-5, v)
Next i
' Transpose x.
MatrixObject.MatrixTranspose(x,xt)
' Compute means within-subject, across Correct1 through ' Correct3.
MatrixObject.MatrixMeans(xt,Means) ReDim c(1 To 3) As Double
' Subtract means for each subject from Correct1 through ' Correct3.
For i=1 To nr
MatrixObject.MatrixGetColumn (xt, i, c) MatrixObject.MatrixElemSubtract (c, Means(i), c) MatrixObject.MatrixSetColumn (xt, i, c)
Next i
' Transpose (back) to x(n of cases, n. of variables)
MatrixObject.MatrixTranspose(xt,x)
For i=6 To 8
' Copy i-5'th column from x to vector v.
MatrixObject.MatrixGetColumn (x, i-5, v)
' Set contents of vector v back into the input ' data Spreadsheet.
InputFile.VData(i)=v
Next i
' Make the (modified) data file visible.
InputFile.Visible=True
' Here we save the modified example data file Exp.sta to ' a new data file, so that the original data file will ' not be changed.
InputFile.SaveAs( _ Path + "\Examples\DataSets\ExpMOD.sta", _ True)
' Next perform the Breakdown analysis via the respective ' STATISTICA Basic Statistics options.
Set newanalysis = Analysis(scBasicStatistics,InputFile)
newanalysis.Dialog.Statistics = scBasBreakdowns newanalysis.Run newanalysis.Dialog.Variables = "6 7 8 | 3" newanalysis.Dialog.Codes = "" newanalysis.Run newanalysis.Dialog.Summary.Visible = True
End Sub
The .VData(i) property will copy the Double values in all rows and in the ith column from the data spreadsheet to a vector of Double values. When a vector of Double values is assigned to .VData(i), the values in the vector will be transferred to the respective column in the spreadsheet.
Like the .Data property, the VData(i) property assumes that the vector and the spreadsheet have compatible dimensions, i.e., in this case that the number of cases or rows in the data spreadsheet is equal to the number of elements in the (1-referenced) vector argument. Also, like the .Data property, cannot use the .VData(i) property directly as an argument in matrix function calls if those functions write values back to the respective input matrix.