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