Macro (SVB) Program Examples - Creating a Random Sized Spreadsheet

This example application shows how to create a new Statistica Spreadsheet of a specific size. The program will create a dialog box where the user can enter the respective spreadsheet dimensions, then display another dialog box prompting the user to select a variable to highlight in red.

Sub Main
								On Error GoTo Leave 
								' go here if dialog box cancels 						
								' Create a new, empty, spreadsheet
								Dim S As Spreadsheet 
								Set S = Application.Spreadsheets.New

 						Begin Dialog UserDialog 290,119,"Select Spreadsheet Size"  
							' %GRID:10,7,1,1 TextBox 170,14,80,21,.Rows
							TextBox 170,49,80,21,.Columns
					  OKButton 40,91,90,21
						 Text 40,14,110,14,"Number of Rows:",.Text1
							Text 20,49,130,14,"Number of Columns:",.Text2
					  CancelButton 150,91,100,21
							End Dialog

							Dim dlg As UserDialog
							' Prefill with current size of the spreadsheet
							dlg.Rows = CStr(S.NumberOfCases)
						 dlg.Columns = CStr(S.NumberOfVariables) 						' display the dialog
					 	Dialog dlg

							Dim R As Integer 					 Dim C As Integer							' read the values that were set
							R = CInt(dlg.Rows)
						 C = CInt(dlg.Columns)							'increase or decrease the size of the Spreadsheet as necessary
							If (R > S.NumberOfCases) Then ' need to increase number of cases
							S.AddCases(S.NumberOfCases, R-S.NumberOfCases)
							ElseIf (R < S.NumberOfCases) Then ' need to decrease number of cases
							S.DeleteCases(R+1, S.NumberOfCases)
							End If

 				  If (C > S.NumberOfVariables) Then ' need to increase number of variables
					  S.AddVariables("NewV", S.NumberOfVariables, C-S.NumberOfVariables) 
					 	ElseIf (C < S.NumberOfVariables) Then ' need to decrease number of cases
					  S.DeleteVariables(C+1, S.NumberOfVariables)
					  End If					 ' fill the Spreadsheet with random values
							S.EntireRange.FillRandomValues
							S.Visible = True 					' Make column 1 be sequential For i = 1 To S.NumberOfCases
							S.value(1, i) = i ' Set Column 1 to sequential numbers
							Next

					 S.Variable(1).Font.Bold = True		' Ask which variable to sort by	nv = 0 ' initial number of selected variables
 					Dim MyVars(1 To 1) As Long ' returned array of selected vars (index from 1)
					 ret = SelectVariables1(S, "Select Variable to sort by", 1, 1, MyVars, _	 nv, "Variables:")

				 If ret And nv > 0 Then 					' if a good return, and a variable was selected 				'copy the selected variable number
					vNo = MyVars(1)				 'sort by the selected variable 				S.SortData(vNo)					'Set the entire column Red
				 S.Variable(vNo).Font.Color = RGB(255,0,0) 
					End If Leave: 'Exit program
End Sub