3.2. Type Conversion

VBA provides two sets of built-in conversion functions. The first set, which includes Int and Str, is from the early versions of VB and is simply left in for backwards compatibility. The functions of the second set all start with the letter "C" and are the more recent conversion functions. Microsoft recommends that you use this latter set of functions, since they are locale-aware; that is, they take account of international date, time, and number settings on the host system.

The syntax for each of the latter conversion functions is basically the same. For example:

CBool(variablename)

where variablename is either the name of a variable, a constant, or an expression (like x-y) that evaluates to a particular data type. Regardless of the particular function you use, the data type being converted is immaterial; what matters is the data type to which you want to convert a particular value.

The conversion functions supported by VBA are:

CBool

Converts variablename to a Boolean data type. variablename can contain any numeric data type or any string capable of being converted into a number. If variablename is or "0", CBool returns False; otherwise, it returns True (–1).

CByte

Converts variablename to a Byte data type. variablename can contain any numeric data or string data capable of conversion into a number that is greater than or equal to and less than or equal to 255. If variablename is out of range, VBA displays an Overflow error message. If variablename is a floating point number, it's rounded to the nearest integer before being converted to byte data.

CDec

Converts variablename to a Decimal data subtype. The function accepts any numeric data within the limits of the Decimal data subtype or any string data that can be converted to a number within the range of the Decimal data subtype. This conversion function provides the only method of creating a Decimal data subtype.

CDate

Converts variablename to a Date/Time data type. CDate accepts numeric and string data that appears to be a date and converts it to the format specified by the locale information on the host computer. For example, on a machine set to the American date format mm/dd/yy, if you enter a date in the British date format dd/mm/yy in a text box and use the CDate function on the contents of the text box, CDate converts it to the American mm/dd/yy format.

CCur

Converts variablename to a Currency data type. CCur accepts any numeric or string data that can be expressed as a currency value. The function recognizes the decimal and thousands separators based on locale information on the host computer. It, as well as the currency variant subtype, is recognized by VBA only.

CDbl

Converts variablename to a double precision data type. The function accepts any numeric data within the limits of the Double data type or any string data that can be converted to a number within the range of the double data type.

CInt

Converts variablename to an Integer data type. CInt accepts any numeric data within the limits of the integer data type or any string data that can be converted to a number and is within the limits of the integer data type.

CLng

Converts variablename to a Long data type. The function accepts any numeric data within the limits of the long integer data type or any string data that can be converted to a number whose value lies within the range of a long integer.

CSng

Converts variablename to a Single data type. The function accepts any numeric data within the limits of the single data type or any string data that can be converted to a number within the range of the Single data type.

CStr

Converts variablename to a String data type. CStr accepts any kind of data.

CVar

Converts variablename to a Variant data type. CVar accepts any kind of data.

3.2.1. Implicit Type Conversion in VB

It's worth mentioning that Visual Basic handles a lot of data type conversion for you in the background. For example, the Text property of a VB text box is quite clearly a String data type, not a Variant, and the Prompt property of a message box is also a string. Given this, you might not expect the following code to run successfully without generating a runtime type mismatch error:

Private Sub Command1_Click()

    Dim iValue As Integer
    
    iValue = txtTextBox.Text
    MsgBox Prompt:=iValue
    
End Sub

But assuming that a number is entered in the text box, there is no error; instead, you can see from this example that VB allows you to assign a string representation of a number to an Integer data type, then assign this integer to the Prompt property of a message box. VB handles the conversion of data types without your having to do it explicitly.