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.
Copyright © 2021. Cloud Software Group, Inc. All Rights Reserved.