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.