Macro (SVB) Programs Example - Creating a Custom Spreadsheet

This example illustrates how to edit individual aspects or cells of a spreadsheet. The program will copy the first row of a Descriptive Statistics summary results spreadsheet into a new spreadsheet, and then set the title of the new spreadsheet. The program also demonstrates how to set values and text in individual cells in the spreadsheet.

' This program will generate a Descriptive Statistics
' results Spreadsheet via the Basic Statistics module,
' create a new Spreadsheet, paste some cells from
' the results Spreadsheet to the new Spreadsheet, and
' then customize the new Spreadsheet.
Sub Main
' Create the Basic Statistics analysis object; you
' may have to edit the path name for the input data
' file (Spreadsheet), to match the installation of
' STATISTICA on your computer.

Dim s As New Spreadsheet

Set s = Spreadsheets.Open _
(Path & "\Examples\DataSets\Exp.sta")

Set newanalysis = Analysis (scBasicStatistics, s)

' Here we are "running" the Basic Statistics;
' the next four lines were recorded as a macro,
' and then modified for this example.

newanalysis.Dialog.Statistics = scBasDescriptives
newanalysis.Run
newanalysis.Dialog.Variables = "5 6 7 8"

' Create the Summary results Spreadsheet (remember that
' all results Spreadsheets are returned as collections).

Set ResSpreadsheetCollection=newanalysis.Dialog.Summary

' "Extract" the single individual object of type Spreadsheet
' from the collection.

Set ResSpreadsheet=ResSpreadsheetCollection.Item(1)

' Make the results Spreadsheet visible.

ResSpreadsheet.Visible=True

' Select all numbers in the first row of the
' Spreadsheet; note that the CellsRange property returns
' an object of type Range object; here is the complete
' syntax (see also the Object Browser):
' Property CellsRange(

' FirstRow As Long, FirstColumn As Long,
' LastRow As Long, LastColumn As Long)
' As Range
Set Cells=ResSpreadsheet.CellsRange( _
1, 1, 1, ResSpreadsheet.NumberOfVariables)

' Next select the cells in this range (for copying).

Cells.Select

' The Copy method will copy the highlighted
' range of cells along with the row and column headers.

ResSpreadsheet.Copy

' Create a new Spreadsheet; note that by default it
' will be created as a 10 x 10 (empty) data matrix.

Dim NewSpreadsheet As New Spreadsheet
NewSpreadsheet.Visible=True

' Select the first cell (row 1, column 1).

Set Cells=NewSpreadsheet.CellsRange( 1, 1,1,1)
Cells.Select

' Paste in the previously copied information.

NewSpreadsheet.Paste

' Next delete cases 3 through 10, and columns
' variables 6 through 10.

NewSpreadsheet.DeleteCases(3, 10)
NewSpreadsheet.DeleteVariables(6, 10)

' Next set the headers etc.

NewSpreadsheet.InfoBox="This is" + vbCrLf _
+"the Info Box" + vbCrLf +"Area"

NewSpreadsheet.Header="Header line 1"+ vbCrLf _
+ "Header line 2"+ vbCrLf + "Header line 3"

' Change the fonts of some text in the Spreadsheet.

Dim r As Range
Set r=NewSpreadsheet.VariableHeader(1,2)
r.Font.Bold=True
r.Font.Color=RGB(255,0,0)
Set r=NewSpreadsheet.Header
r.Font.Color=RGB(0,0,255)

 
' The following code will set custom values and text
' into the second line of the Spreadsheet
Dim i As Integer

NewSpreadsheet.CaseNameLength=40
NewSpreadsheet.CaseNameWidth=1
NewSpreadsheet.CaseName(2)="Case Name"
NewSpreadsheet.value(2,1)="Text"
For i = 2 To NewSpreadsheet.NumberOfVariables

NewSpreadsheet.value(2,i)=i

Next i

s.Close

End Sub

Note that, by default, spreadsheets are created as 10 x 10 empty data matrices. The DeleteCases and DeleteVariables methods allow you to delete the necessary numbers of rows and variables; AddCases and AddVariables can be used to add rows and columns.

The resulting custom spreadsheet will look (approximately) like this:

The exact appearance on your installation of STATISTICA may vary if you have different predefined defaults in effect for spreadsheets.