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