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