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