How can I use the data in a spreadsheet with matrix functions? (How do I copy data efficiently from spreadsheets to matrices, and back?)

Statistica Visual Basic contains several functions to quickly move columns (variables), rows (cases), or the entire data matrix from spreadsheets to dimensioned arrays, and back. The following example shows how you can use the .VData(variable), CData(case), and .Data properties of the spreadsheet object to retrieve particular columns (variables) or rows (cases) of data from the respective spreadsheet, or to copy the entire data matrix.

' This program will fill a dimensioned array ' x(100,10) with random numbers, and then ' transfer the contents of that array to ' a spreadsheet.

Sub Main

Dim i As Integer Dim j As Integer ' Create the new Spreadsheet; by default it will have ' 10 rows and 10 columns. Dim ss As New Spreadsheet ' The next statement will resize the new Spreadsheet to ' 100 cases and 10 variables. ss.SetSize(100,10) ' Dimension array x. ReDim x(1 To ss.NumberOfCases, _ 1 To ss.NumberOfVariables) As Double ' Fill array x with random numbers. For i = 1 To ss.NumberOfCases For j=1 To ss.NumberOfVariables x(i,j)=Rnd(1) Next j Next i ' Copy the contents of array x to the new spreadsheet. ss.Data=x ' Make the resulting spreadsheet visible. ss.Visible=True ' Dimension another array y. ReDim y(1 To ss.NumberOfCases, _ 1 To ss.NumberOfVariables) As Double ' With the next line, the entire contents of spreadsheet ' ss is transferred to the array y. y = ss.Data ' Alternatively, you could also have used the .Vdata(Variable) ' property, to copy the contents column-by-column, or the ' Cdata(Case) property, to copy the contents row by row. ' The following code will add first 10, and then 100 to ' each random number; so the final spreadsheet that will ' be displayed will contain numbers in the range from ' 110 to 111. ReDim yVector(1 To ss.NumberOfCases) As Double For i = 1 To ss.NumberOfVariables ' Copy column i from the spreadsheet to the vector. yVector = ss.VData(i) ' Use matrix function MatrixElemAdd to add 10 to each ' element in yVector. MatrixObject.MatrixElemAdd (yVector, 10, yVector) ' Copy the contents of the vector to column i of the ' spreadsheet. ss.VData(i) = yVector Next i ReDim yVector(1 To ss.NumberOfVariables) For i = 1 To ss.NumberOfCases ' Copy row i from the spreadsheet to the vector. yVector = ss.CData(i) ' Use matrix function MatrixElemAdd to add 100 to each ' element in yVector. MatrixObject.MatrixElemAdd (yVector, 100, yVector) ' Copy the contents of the vector to row i of the ' spreadsheet. ss.CData(i) = yVector Next i

End Sub

Note: internally, the .VData(variable), CData(case), and .Data properties perform copying operations either from or to a dimensioned array. You can only use these properties as arguments in matrix function calls if those functions do not write values back to the respective input matrix (so you could not, for example, use them directly in the call to the MatrixElemAdd function as shown above). Also, the dimensions of the arrays used in conjunction with these properties must match the respective dimensions (row, column, or both) of the spreadsheet.

Changing individual values in a spreadsheet. When using the Vdata, CData, and Data properties it is also important to remember that they will actually "move" data; for example, the Data property will move the data from the respective spreadsheet to the specified array. Therefore, when performing operations in a loop, for example in order to update particular cells in the spreadsheet only, it is much more efficient to use the Value(case,variable) property.