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