Macro (SVB) Programs Example - Formatting Cells in Spreadsheets

This example program will generate a results spreadsheet from the Basic Statistics module (see also Recording Simple Macro (SVB) Programs, Documenting an Analysis) and then format some of the cells in the spreadsheet.

' This program will generate a Descriptive Statistics
' results Spreadsheet via the Basic Statistics module,
' and then change the format for some of the numbers
' displayed in the Spreadsheet.

' 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

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.


' Select all numbers in the first column 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

Cells=ResSpreadsheet.CellsRange( _
1, 1, ResSpreadsheet.
NumberOfCases, 1)

' Cells is a Range object; one of the properties
' of the Range object is the Font; now set the font;

' the font is accessible as a Font object.

Set CellFont=Cells.Font

' Italic and Bold are properties of the Font.



End Sub

This program illustrates how each aspect of the STATISTICA output is accessible as an object with various properties and methods that allow you to modify or edit the respective feature. To summarize the program shown here, after creating the results spreadsheet, we access a range of cells as a Range object; then we access the font for the display of the numbers in that range as a Font object; the Font object contains various properties that allow you specify the font.

The resulting spreadsheet will look like this: