Example - Accessing All Data in Spreadsheets via the .Data Property

Usually, one wants to apply various matrix operations to a data matrix available in an input data spreadsheet or a results spreadsheet. SVB provides a convenient way to access data via the .Data property of spreadsheets.

First, ensure that the Basic Statistics Library is enabled in the References dialog box:

Ribbon bar. Select the Edit tab. In the Tools group, click References.

Classic menus. From the Tools menu, select References.

In the References dialog box, select the STATISTICA Basic Statistics Library (1.0) check box, and click OK to close the dialog box.

Here is the example:

' This program will use the Basic Statistics
 ' library functions to compute a correlation
 ' matrix for 4 variables; the correlation matrix
 ' will then be inverted and the inverse set in
 ' the upper diagonal of the results Spreadsheet.

Sub Main

' Create the Basic Statistics Analysis object; you
 ' may have to edit the location of the input data file.

Set newanalysis = Analysis (scBasicStatistics, _
 Path + "\Examples\DataSets\Exp.sta")

' Select the Correlations option.

newanalysis.Dialog.Statistics = scBasCorrelationMatrices
 newanalysis.Run

' Select the variables for the analysis.

newanalysis.Dialog.VariableList = "5 6 7 8"

' Create the summary symmetric correlation matrix
 ' in a results Spreadsheet.

Dim ResSpreadsheet As Spreadsheet
 Set ResSpreadsheet=newanalysis.Dialog.Summary.Item(1)
 Dim nr As Long, nc As Long,i As Long, j As Long
 Dim x () As Double

' The .Data property returns the Spreadsheet data
 ' as a two-dimensional array of Doubles. The array
 ' is 1-referenced in each dimension, i.e., the first
 ' element in the array is x(1,1).
 ' Note that x() will contain a copy of the data in
 ' the results Spreadsheet ResSpreadsheet; x will not
 ' be the Spreadsheet object itself. So after you
 ' perform the desired computations you have to "set"
 ' the edited matrix x() back into the data, by assigning
 ' it to the .Data property (see below).

x=ResSpreadsheet.Data
 nr=ResSpreadsheet.NumberOfCases
 nc=ResSpreadsheet.NumberOfVariables

' Declare the array for the matrix inverse.

ReDim xinv(1 To nr,1 To nc) As Double
 MatrixObject.MatrixInverse(x,xinv)

' Set the values from the inverse correlation matrix
 ' in the upper diagonal matrix of x.

For i=1 To nr

For j=1 To i

x(j,i)=xinv(i,j)

Next j

Next i

' The following line is important! See also the comment
 ' earlier. By assigning array x to the property .Data of
 ' the Spreadsheet, we are copying the values of matrix
 ' x back into the results Spreadsheet.

ResSpreadsheet.Data=x

' Next set the headers of the modified results Spreadsheet
 ' to reflect the new contents.

ResSpreadsheet.Header= _
 "Correlation and Inverse Correlation Matrix" + _
 vbCrLf + _
 "(Correlations below Diagonal; Inverse above Diagonal)"

' Make the modified results Spreadsheet visible.

ResSpreadsheet.Visible=True

End Sub

As you can see from the example program, the .Data property will return a copy of the spreadsheet data, which you can then access like a two-dimensional array of Double values. Note that this array is 1-referenced, i.e., the first element of the array is element (1,1). After performing the desired calculations on the array, you can write it back to the spreadsheet by assigning to the .Data property the array (as illustrated in the example program).

Sizes of spreadsheets and arrays
Note that the statement x=ResSpreadsheet.Data used in the previous example will create a "snapshot" of the data in the spreadsheet, and copy it back to the array. It is a convenient shortcut method for transferring data from a spreadsheet to an array suitable for use with the matrix library functions. However, it assumes that the dimensions of the arrays will match, so you cannot write, for example, a 2 by 3 array of data back to a 4 by 6 spreadsheet.
Using the
Data property directly as an argument in matrix function calls. You cannot use the .Data property directly as an argument in matrix function calls if those functions write values back to the respective input matrix. For example, the statement MatrixInverse(x,ResSpreadsheet.Data) would not be legal because this function will write out the inverse matrix to the array passed as the second argument in the call. However, you can use the .Data property for input into read-only matrix functions. So the statement MatrixDisplay(ResSpreadsheet.Data,"The Data") would be valid, because it accesses the data returned by the .Data property in a read-only mode.