Introduction


This article is about data casting. I am going to cover VBA/VB6 explicit casting.  It doesn't have anything to do with fly fishing, reel fishing, surf fishing, acting, or broken limbs.  You may have done some casting and not known it because VB does some conversions for you implicitly.  Although it is a tutorial, I'm including some tips and tricks.

Firstly, I'd like to acknowledge the genesis of this article.  The primary impetus to start writing was reading Jim Dettman's excellent article (http:/A_12.html) on the MS-Access DLookup function.  Secondly, I had recently completed an upgrade to one of my Access applications using and enhancing the DLookup function with some data type casting.  Thirdly, from my involvement with the local Delphi user group and answering questions in the Delphi TA, I've become a fan of Delphi's QuotedStr() function.  In general, I look back on so many EE questions in various Topic Areas that involved data type casting, that I thought it worth writing an article.

Article Topics


  • What is Casting?

  • Common places to encounter casting

  • Why would this ever be a problem?

  • Problems with casting

  • Intrinsic Casting functions in VBA/VB6

  • Casting Tricks & Tips


What is Casting?


Casting causes a variable's contents or expression/literal to be treated like a different kind of data.  The data conversion takes place in a temporary memory location.  There are several ways to convert data in VB.  Since most of you should be familiar with concatenation, you should understand that concatenation is an operation between string (data type) expressions.  When you concatenate a number or date to a string, there is some implicit casting.  While there are functions that extract a part of data while changing its data type
(e.g. Format(Date(),"dddd")), this article is about a data type change of the entire data.

Common places to encounter casting


Dynamic SQL - building a SQL statement through concatenation with local variables, control values and other database data.

VBA code - converting a string to a byte array as the first part of an encryption routine.
Dim bText() As Byte
bText = StrConv(parmPlainText, vbFromUnicode)

Function and Subroutine calls - defining a parameter as a particular data type may require the calling/invoking code to cast an express to a matching data type.  Also, a routine may have to cast data passed in through a Variant parameter.

T-SQL - If you have seen many T-SQL scripts, you have probably noticed the use of the CAST() and CONVERT() functions.  

Excel - has several workbook functions to cast one type of data into another, such as DEC2HEX(), DEC2BIN(), HEX2DEC(), COMPLEX().
 
Why would this ever be a problem?


There are times when you trade convenience and flexibility for speed.  If your program reads a CSV file, you may need to read these values into local variables of String or Variant data types, then cast some of the data into specific data types for later processing.  

Problems with casting


  • Sometimes you get an error message that doesn't quite describe casting as the solution

Error 6: Overflow
Error 13: Type Mismatch
Error 49: Bad DLL calling convention
Error 1006: Unable to get the [property name] property of the [object] class

  • VB var types are different than DB var types

These statements were executed in the Immediate window.  X is a variant data type.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:

x=#1/2/2009#:?x,vartype(x),vbvartype.vbDate,TypeName(x),dbDate
1/2/2009       7             7            Date           8 
 
x=1/2:?x,vartype(x),vbvartype.vbDouble,TypeName(x),dbDouble
 0.5           5             5            Double         7 
 
x=2009:?x,vartype(x),vbvartype.vbInteger,TypeName(x),dbInteger
 2009          2             2            Integer        3 
 
x=200900:?x,vartype(x),vbvartype.vbLong,TypeName(x),dbLong
 200900        3             3            Long           4 
 
x="aikimark":?x,vartype(x),vbvartype.vbString,TypeName(x),dbText
aikimark       8             8            String         10 
 
x=true:?x,vartype(x),vbvartype.vbBoolean,TypeName(x),dbBoolean
True           11            11           Boolean        1


  • Sometimes the intrinsic casting functions don't work the way you expect (or want)

date conversions
1:
2:
3:
4:
5:

?cdate("1/2/2009")
1/2/2009 
?cdate(1/2/2009)
12:00:22 AM

Number concatenation -- which is why I always use the & concatenation operator
1:
2:
3:
4:
5:
6:
7:

a=1
b=2
?a + b
 3 
?cstr(a) + cstr(b)
12


Intrinsic Casting functions in VBA/VB6


1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:

CBool()
CByte()
CCur()
CDate()
CDbl()
CDec()
CInt()
CLng()
CSng()
CStr()
CVar()

Although not in the official list of VB conversion functions, the Val() function is quite a reliable and versatile method of casting data into a numeric value.

Note: the .Net framework is full of casting features, such as the .ToString method.  I will not cover these in this article.

Casting Tricks & Tips


Dynamic SQL
JDettman's article introduced you to delimited string variables in a DLookup() criteria parameter.  In addition, there are date variables, requiring a pound sign (#) delimiter.  Here is a general purpose function that you can use to properly delimit your variables, no matter what their data type. Sometimes we cast data by the delimiters around expressions we type into our code.  As Jim highlighted, the data type of the field needs to match the data type of the compared value/expression.  When they don't match we frequenly receive a "Data type mismatch in criteria expression" error (-2147217913), but sometimes, we just don't get the results we expected.  

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:

Public Function Delimed(parmVar) As String
  Const QuoteChar = """"
  Const PoundChar = "#"
  If VarType(parmVar) = vbDate Or IsDate(parmVar) Then
    Delimed = PoundChar & parmVar & PoundChar
  ElseIf IsNumeric(parmVar) Then
    Delimed = CStr(parmVar)
  ElseIf VarType(parmVar) = vbString Then
    Delimed = QuoteChar & parmVar & QuoteChar
  Else
    Delimed = CStr(parmVar)
  End If
End Function


Usage:
This example revisits the DLookup article's example set, comparing column values in a table.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:

Dim vCompany As Variant
Dim sCompany As String
Dim lCompany As Long
Dim vFrom As Variant
Dim vTo As Variant
Dim sSQL As String
Dim rs As Recordset
vCompany = 2525
sCompany = "2525"
lCompany = 2525
sSQL = "Select * From CompanyTable Where ID =" & Delimed(vCompany)
Debug.Print "vCompany=ID", sSQL
sSQL = "Select * From CompanyTable Where ID =" & Delimed(sCompany)
Debug.Print "sCompany=ID", sSQL
sSQL = "Select * From CompanyTable Where ID =" & Delimed(lCompany)
Debug.Print "lCompany=ID", sSQL
 
vCompany = "EE Publishing"
sCompany = "EE Publishing"
sSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(vCompany)
Debug.Print "vCompany=CompanyName", sSQL
sSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(sCompany)
Debug.Print "sCompany=CompanyName", sSQL
 
vFrom = "2001-10-1"
vTo = "10/11/2009"
sSQL = "Select * From CompanyTable Where JoinDate Between " & Delimed(vFrom) & " And " & Delimed(vTo)
Debug.Print "vFrom/vTo", sSQL


Result of the Delimed() function on the SQL string
1:
2:
3:
4:
5:
6:
7:
8:
9:

=== Immediate Window ============
vCompany=ID		Select * From CompanyTable Where ID =2525
sCompany=ID		Select * From CompanyTable Where ID =2525
lCompany=ID		Select * From CompanyTable Where ID =2525
vCompany=CompanyName	Select * From CompanyTable Where CompanyName ="EE Publishing"
sCompany=CompanyName	Select * From CompanyTable Where CompanyName ="EE Publishing"
vFrom/vTo		Select * From CompanyTable Where JoinDate Between #2001-10-1# And #10/11/2009#
=================================

 
Warning: The Delimed() function is not perfect.  It can not overcome limitations in the runtime environment.  This is most evident with the following date example executed in the Immediate window.  If you ever created dynamic SQL with a date string that was not #-delimited, then this should help you understand what value was being passed to the database query parser.

1:
2:
3:
4:
5:
6:
7:
8:

=== Immediate Window ============
?Delimed("12/1/2001")
#12/1/2001#
 
?Delimed(12/1/2001)
5.99700149925037E-03
=================================



Casting for my own convenience
In a recent application, I created a configuration settings table to help with the migration of the database from development (me), to the on-site test environment, to the production environment.  Originally, there were two text columns, containing the config-name and config-value.  I used the DLookup() function to retrieve values from the table.  I ran into some quirks of the DLookup() function that cause me to add a third text column to the table (config-datatype) and wrap the DLoopkup results in a function that cast the results into the desired data type.  This made the application code much simpler.  When the database is opened, application code can match the attached table connection string against that in the configuration settings table and reattach them, if necessary.  There are certain actions that don't work in my development environment, so I look at the DevEnvironment config-value to detect this.  When the tester gets a new copy of the application, he renames the configuration settings tables and restarts the database.  He repeats this process when moving his tested database into production.

My DLookup() problems were:
  • The returned Variant data type isn't typed

  • If a value isn't found, the function returns Null

  • Null values may be fine for assigning to fields, but they can't be assigned to string variables and some controls


This is the function that wraps the DLookup() value from the configuration table, casting the result into the desired data type.
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:

Public Function GetConfigSetting(parmConfigName As String) As Variant
  Dim varValue As Variant
  Dim strType As String
  varValue = DLookup("ConfigValue", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
  If IsNull(varValue) Then
    GetConfigSetting = vbNullString
  Else
    strType = DLookup("ConfigDataType", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
    Select Case strType
      Case "Text"
        GetConfigSetting = CStr(varValue)
      Case "Long"
        GetConfigSetting = CLng(varValue)
      Case "Single"
        GetConfigSetting = CSng(varValue)
      Case "Double"
        GetConfigSetting = CDbl(varValue)
      Case "Date"
        GetConfigSetting = CDate(varValue)
      Case "Boolean"
        GetConfigSetting = CBool(varValue)
      Case Else
        GetConfigSetting = varValue
    End Select
  End If
End Function


ConfigurationSettings table
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:

ID	ConfigName		ConfigValue				ConfigDataType
1	ImportPath		C:\Temp\					Text
2	DevEnvironment		True					Boolean
3	BE_Connect		C:\Specimen DB 9-3\Spec Database_BE.mdb	Text
4	ActivityLog_Connect 	C:\Specimen DB 9-3\Activity Log.mdb		Text
5	Snapshot_Connect       	C:\ Specimen DB 9-3\Spec Snapshot.mdb	Text
6	BE_Pattern		*_BE.mdb					Text
7	ActivityLog_Pattern  	*Activity Log.mdb				Text
8	Snapshot_Pattern    	*SpecDBMS Snapshot.mdb			Text
 
Fieldname		DataType 	FieldLength
ID			AutoNumber	4 
ConfigName		Text		50 
ConfigValue		Text		255 
ConfigDataType		Text		50


ConfigDataType Lookup tab is defined as:
RowSource is a one column value list: Text;Long;Single;Double;Date;Boolean
BoundColumn: 1
ColumnWidths: 2"

=========================================
====== BONUS  MATERIAL ======================
==========================================
Performance Tip:


About a decade ago, I gave a performance presentation to the local VB user group.  One of my tests was a Select Case statement, very similar to the one you see in the GetConfigSetting() function.  If you create a configurations settings table that is going to be executed very frequently, you would do well to change the ConfigDataType field lookup tab.  The comparison of integer values is quite a bit quicker than string value comparisons.

Also, note that the ordering of compared values will also help performance, placing the most likely encountered values nearer to the top of the list.

ConfigDataType Lookup tab is defined as:
RowSource is a two column value list: 8;Text;3;Long;4;Single;5;Double;7;Date;11;Boolean
BoundColumn: 1
ColumnWidths: 0";2"

High performance version of the GetConfigSetting() function:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:

'===================================================
'Performance tweaked version of the casting function
'===================================================
Public Function GetConfigSetting(parmConfigName As String) As Variant
  Dim varValue As Variant
  Dim lngType As Long
  varValue = DLookup("ConfigValue", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
  If IsNull(varValue) Then
    GetConfigSetting = vbNullString
  Else
    lngType = DLookup("ConfigDataType", "ConfigurationSettings", "ConfigName = " & Delimed(parmConfigName))
    Select Case lngType
      Case vbString
        GetConfigSetting = CStr(varValue)
      Case vbBoolean
        GetConfigSetting = CBool(varValue)
      Case vbLong
        GetConfigSetting = CLng(varValue)
      Case vbSingle
        GetConfigSetting = CSng(varValue)
      Case vbDouble
        GetConfigSetting = CDbl(varValue)
      Case vbDate
        GetConfigSetting = CDate(varValue)
      Case Else
        GetConfigSetting = varValue
    End Select
  End If
End Function


If this article was helpful, please click the YES link below.