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

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.