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