Using Functions to Convert Data from One Type to Another
VBA provides a full set of functions for converting data from one data type to another. These functions fall into two distinct groups: simple data conversion and more complex data conversion.
Table below lists VBA's functions for simple data conversion.VBA's functions for simple data conversion
Function(Arguments) Data Type Returned CBool(number) Boolean CByte(expression) Byte CCur(expression) Currency CDate(expression) Date CDbl(expression) Double CInt(expression) Integer CLng(expression) Long CSng(expression) Single CStr(expression) String CVar(expression) Variant
For example, the following statements declare the untyped variable varMyInput and the Integer variable intMyVar and then display an input box prompting the user to enter an integer. In the third statement, the user's input is assigned to varMyInput, which automatically becomes a Variant/String. The fourth statement uses the CInt function to convert varMyInput to an integer, assigning the result to intMyVar. The fifth statement compares intMyVar to 10, converts the result to Boolean by using the CBool function, and displays the result (True or False) in a message box.
Dim varMyInput Dim intMyVar As Integer varMyInput = InputBox("Enter an integer:", "10 Is True, Other Numbers Are False") intMyVar = CInt(varMyInput) MsgBox CBool(intMyVar = 10)
Recall that a Boolean variable is only either True or False. So in the final line of this example, we're saying in effect, "If the value in the variable intMyVar is 10, the Boolean result will be True. If the value is anything other than 10, the result will be False."
Table below lists VBA's functions for more complex data conversion. Some of these functions are used relatively often in programming, so we'll examine them in detail following this table.VBA's functions for complex data conversion
Function(Arguments) Returns Asc(string) The ANSI character code for the first character in the string. Chr(number) The string for the specified character code (a number between 0 and 255). Format(expression, A variant containing expression formatted format) as specified by format. (You'll see how Format works in "Using the Format Function to Format an Expression" later in the tutorial.) Hex(number) A string containing the hexadecimal value of number. Oct(number) A string containing the octal value of number. RGB(number1, number2, A Long integer representing the color value specified by number1, number3) number2, and number3. QBColor(number) A Long containing the RGB value for the specified color. Str(number) A Variant/String containing a string representation of number. Val(string) The numeric portion of string; if string does not have a numeric portion, Val returns 0.
In this tutorial:
- Using Built-in Functions
- What Is a Function?
- Passing Arguments to a Function
- Using Functions to Convert Data from One Type to Another
- Using the Asc Function to Return a Character Code
- Using the Str Function to Convert a Value to a String
- Using the Format Function to Format an Expression
- Using the Chr Function and Constants to Enter Special Characters in a String
- Using Functions to Manipulate Strings
- Using InStr and InStrRev to Find a String within Another String
- Using LTrim, RTrim, and Trim to Trim Spaces from a String
- Using Len to Check the Length of a String
- Using the StrComp Function to Compare Apples to Apples
- Using VBA's Mathematical Functions
- Excel VBA's Date and Time Functions
- Using the DateDiff Function to Return an Interval
- Using File-Management Functions