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.
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.
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 IfEnd Function
Usage:
This example revisits the DLookup article's example set, comparing column values in a table.
Dim vCompany As VariantDim sCompany As StringDim lCompany As LongDim vFrom As VariantDim vTo As VariantDim sSQL As StringDim rs As RecordsetvCompany = 2525sCompany = "2525"lCompany = 2525sSQL = "Select * From CompanyTable Where ID =" & Delimed(vCompany)Debug.Print "vCompany=ID", sSQLsSQL = "Select * From CompanyTable Where ID =" & Delimed(sCompany)Debug.Print "sCompany=ID", sSQLsSQL = "Select * From CompanyTable Where ID =" & Delimed(lCompany)Debug.Print "lCompany=ID", sSQLvCompany = "EE Publishing"sCompany = "EE Publishing"sSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(vCompany)Debug.Print "vCompany=CompanyName", sSQLsSQL = "Select * From CompanyTable Where CompanyName =" & Delimed(sCompany)Debug.Print "sCompany=CompanyName", sSQLvFrom = "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
=== Immediate Window ============vCompany=ID Select * From CompanyTable Where ID =2525sCompany=ID Select * From CompanyTable Where ID =2525lCompany=ID Select * From CompanyTable Where ID =2525vCompany=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.
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.
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 IfEnd Function
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:
'==================================================='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 IfEnd Function
Comments (0)