MS-Excel / Functions and Formula

---

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.