Macro (SVB) Programs Example - Resize a Spreadsheet
The subroutine SpreadsheetResize can be used to programmatically resize a specific spreadsheet (or the active spreadsheet) by simply referencing the spreadsheet and entering in its new dimensions.
The first parameter is the target spreadsheet; if this is left blank, the active spreadsheet will be assumed (note, if there are no active spreadsheets, the active data set will be used). The second parameter is the new number of cases, and the third parameter is the new number of variables; if either of these parameters are left blank, the default value of ten will be assumed.
Copy and paste the following into your macro (outside of the main sub) to utilize SpreadsheetResize:
'Include this in your macro as a separate function Sub SpreadsheetResize( _ Optional ByRef s As Spreadsheet = ActiveSpreadsheet _ ,Optional CaseNumber As Integer = 10 _ ,Optional VarNumber As Integer = 10) 'pass the target spreadsheet, the desired number of cases ', and desired number of variables. If no spreadsheet is 'indicated then the active spreadsheet is used (if there 'is no active spreadsheet, then the active data set is 'used). If no case or variable number is specified, then 'the default of ten will be used. 'Note, the spreadsheet's original data will be 'preserved (unless the spreadsheet is shrunk, in 'which case some data will be truncated). On Error GoTo ResetTarget 'verify that the requested case/vars 'number are above zero If Not(CaseNumber > 0 And VarNumber > 0) Then MsgBox "Invalid number of cases or variables" Exit Sub End If Dim CaseCount As Integer, VarCount As Integer, _ Difference As Integer CaseCount = s.Cases.Count VarCount = s.Variables.Count 'cases resizing 'more cases are needed If CaseNumber > CaseCount Then Difference = CaseNumber - CaseCount For i = 0 To (Difference -1) s.AddCases(CaseCount,1) 'add the cases CaseCount = CaseCount + 1 Next i 'else less cases are needed ElseIf CaseCount > CaseNumber Then Difference = CaseCount - CaseNumber For i = 0 To (Difference - 1) 'delete cases s.DeleteCases(CaseCount,CaseCount) CaseCount = CaseCount - 1 Next i End If 'variables 'more variables are needed If VarNumber > VarCount Then Difference = VarNumber - VarCount For i = 0 To (Difference -1) 'add variables s.AddVariables("",VarCount,1,,,,,) VarCount = VarCount + 1 Next i 'else less variables are needed ElseIf VarCount > VarNumber Then Difference = VarCount - VarNumber For i = 0 To (Difference - 1) 'delete variables s.DeleteVariables(VarCount,VarCount) VarCount = VarCount - 1 Next i End If Exit Sub 'no active spreadsheet found, 'search for the active spreadsheet ResetTarget: Dim SpreadsheetCount As Integer For Each i In Application.Spreadsheets SpreadsheetCount = SpreadsheetCount + 1 Next i 'if there is at least one spreadsheet If SpreadsheetCount > 0 Then 'look for the data set (that wasn't active) Set s = Application.ActiveDataSet Call SpreadsheetResize(s,CaseNumber,VarNumber) Else 'there are zero spreadsheets open MsgBox "No spreadsheets are currently open" End If End Sub
Copyright © 2021. Cloud Software Group, Inc. All Rights Reserved.