Macro (SVB) Programs Example - Converting a Spreadsheet's Data Type
The subroutine SpreadsheetTypeConvert
can be used to programmatically change a spreadsheet's
data type (either the entire spreadsheet or a specific range of variables).
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 starting
point of the variable range, and the third parameter is the end point
of the variable range. If the start point parameter is left blank, the
first variable in the spreadsheet is assumed; if the end point parameter
is left blank, the remaining variables in the spreadsheet will be assumed
(dependent upon which start point was specified). The fourth parameter
is the new data type (displayed as an enumeration containing ByteType,
DoubleType, IntegerType,
and TextType); if left blank,
Double will be assumed (see Variable
Types for additional information regarding spreadsheet data types).
Simply copy and paste the following into your macro
(outside of the main sub) to
utilize SpreadsheetTypeConvert:
'Include this in the declaration section:
'this governs the way that you can declare
'a specific data type by calling
'SpreadsheetTypeConvert()
Enum DataType
ByteType = 0
DoubleType '=1
IntegerType '=2
TextType '=3
End Enum
'Include this in your macro as a separate function
Sub SpreadsheetTypeConvert _
(Optional ByRef s As Spreadsheet = ActiveSpreadsheet _
,Optional FirstVariable
As Integer = 1, _
Optional LastVariable As
Integer = -1, _
Optional NewType As
DataType = 1)
'This subroutine allows a spreadsheet
to have
'either a specified range or its entire
'content to have its variable data types
'changed
Dim
CaseCount As Integer
'if there is no active sheet (and no specific spreadsheet
'mentioned) then look for the active data set
On
Error
GoTo
ResetTarget
'Either the parameter was left blank or -1
'was entered (indicating the rest of the range).
'All variables beyond FirstVariable will be converted
If
LastVariable = -1 Then
LastVariable = s.Variables.Count
End If
'start and end range must be positive
If FirstVariable <= 0 Or
LastVariable <= 0 Then
MsgBox
"Variables must be greater than zero"
Exit Sub
End If
'uppperbound variable should logically be larger
'or equal to the lower bound variable
If LastVariable < FirstVariable Then
MsgBox
_
"Last variable must be proceed first variable"
Exit Sub
End If
'this loop handles going through the variables
For j = FirstVariable To LastVariable
'apply the data type that was specified
Select
Case NewType
Case
ByteType
s.Variable(j).ColumnType = scByte
Case
DoubleType
s.Variable(j).ColumnType = scDouble
Case
IntegerType
s.Variable(j).ColumnType = scInteger
Case
TextType
s.Variable(j).ColumnType = scText
End Select
Next j
Exit Sub
'no
active spreadsheet found,
'search for the active data set
ResetTarget:
'this variable keeps count of the number
'of open spreadsheets
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
'if there
is an active data set
'then set it as the target spreadsheet
Set s = Application.ActiveDataSet
'start the process over again through recursion
'(with the active data set this time).
Call SpreadsheetTypeConvert(s,FirstVariable, _
LastVariable,NewType)
'else, no spreadsheets appear to be open
Else
MsgBox
"No spreadsheets are currently open"
End If
End Sub