9.3. 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 9.1 lists VBA's functions for simple data conversion.
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)Table 9.2 lists VBA's functions for more complex data conversion.
9.3.1. Using the Asc Function to Return a Character Code
The Asc function returns the character code for the first character of a string. Character codes are the numbers by which computers refer to letters. For example, the character code for a capital A is 65 and for a capital B is 66; a lowercase a is 97, and a lowercase b is 98.
The syntax for the Asc function is straightforward:
Asc(string)
Here, string is any string expression. For example, Asc("A") returns 65.
The following statements use the Asc function to return the character code for the first character of the current selection in the active document and display that code in a message box:
strThisCharacter = Asc(Selection.Text) MsgBox strThisCharacter, vbOKOnly, "Character Code"
9.3.2. Using the Val Function to Extract a Number from the Start of a String
The Val function converts the numbers contained in a string into a numeric value. Val follows these rules:
It reads only numbers in a string.
It starts at the beginning of the string and reads only as far as the string contains characters that it recognizes as numbers.
It recognizes the period as a decimal separator but not the comma.
This means that if you feed Val a string consisting of tabbed columns of numbers, such as the second line here, it will read them as a single number (in this case, 445634.994711):
Item# Price Available On Order Ordered 4456 34.99 4 7 11
If, however, you feed it something containing a mix of numbers and letters, Val will read only the numbers and strings recognized as numeric expressions (for example, Val("4E5") returns 400000, because it reads the expression as exponentiation). For example, if fed the address shown here, Val returns 8661, ignoring the other numbers in the string (because it stops at the L of Laurel, the first character that isn't a number, a tab, a line feed, or a space):
8661 Laurel Avenue Suite 3806, Oakland, CA 94610
The syntax for Val is straightforward:
Val(string)
Here, string is a required argument consisting of any string expression.
The following statement uses Val to return the numeric variable StreetNumber from the string Address1:
StreetNumber = Val(Address1)
9.3.3. Using the Str Function to Convert a Value to a String
Just as you can convert a string to a value, you can also convert a value to a string. You'll need to do this when you want to concatenate the information contained in a value with a string—if you try to do this simply by using the + operator, VBA attempts to perform a mathematical operation rather than concatenation.
For example, suppose you've declared a String variable named strYourAge and a numeric variable named intAge. You can't use a strYourAge + intAge statement to concatenate them, because they're different types; you first need to create a string from the intAge variable and then concatenate that string with the strYourAge string. (Alternatively, you can use the & operator to concatenate the two variables.)
To convert a value to a string, use the Str function. The syntax for the Str function is this:
Str(number)
Here, number is a variable containing a numeric expression (such as an Integer data type, a Long data type, or a Double data type).
The following short procedure provides an example of converting a value to a string:
Sub Age()
Dim intAge As Integer, strYourAge As String
intAge = InputBox("Enter your age:", "Age")
strYourAge = "Your age is" & Str(intAge) & "."
MsgBox strYourAge, vbOKOnly + vbInformation, "Age"
End S9.3.4. Using the Format Function to Format an Expression
The Format function is a powerful tool for changing numbers, dates and times, and strings into the format in which you need them.
The syntax for the Format function is as follows:
Format(expression[, format[, firstdayofweek[, firstweekofyear]]])
These are the components of the syntax:
expression is any valid expression.
format is an optional argument specifying a named format expression or a user-defined format expression. More on this in a moment.
firstdayofweek is an optional constant specifying the day that starts the week (for date information): The default setting is vbSunday (1), but you can also set vbMonday (2), vbTuesday (3), vbWednesday (4), vbThursday (5), vbFriday (6), vbSaturday (7), or vbUseSystem (0; uses the system setting).
firstweekofyear is an optional constant specifying the week considered first in the year (again, for date information):
| Constant | Value | Year Starts with Week |
| vbUseSystem | 0 | Use the system setting. |
| vbFirstJan1 | 1 | The week in which January 1 falls (the default setting). |
| vbFirstFourDays | 2 | The first week with a minimum of four days in the year. |
| vbFirstFullWeek | 3 | The first full week (7 days) of the year. |
You can define your own formats for the Format function as described in the following sections if none of the predefined numeric formats (described next) suits your needs.
9.3.4.1. Using Predefined Numeric Formats
Table 9.3 lists the following predefined numeric formats that you can use with the Format function.
| Format Name | Explanation | Example |
|---|---|---|
| General Number | The number is displayed with no thousand separator. | 124589 |
| Currency | The number is displayed with two decimal places, a thousand separator, and the currency symbol appropriate to the system locale. | $1,234.56 |
| Fixed | The number is displayed with two decimal places and at least one integer place. | 5.00 |
| Standard | The number is displayed with two decimal places, at least one integer place, and a thousand separator (when needed). | 1,225.00 |
| Percent | The number is displayed multiplied by 100, with two decimal places, and with a percent sign. | 78.00% |
| Scientific | The number is displayed in scientific notation. | 5.00E + 00 |
| Yes/No | A non-zero number is displayed as Yes; a zero number is displayed as No. | Yes |
| True/False | A non-zero number is displayed as True; a zero number is displayed as False. | False |
| On/Off | A non-zero number is displayed as On; a zero number is displayed as Off. | Off |
For example, the following statement returns $123.45:
Format("12345", "Currency")9.3.4.2. Creating a Numeric Format
If none of the predefined numeric formats suits your needs, you can create your own numeric formats by using your choice of combination of the characters listed in Table 9.4.
For example, the following statement returns a currency formatted with four decimal places:
Format("123456", "$00.0000")9.3.4.3. Creating a Date or Time Format
Similarly, you can create your own date and time formats by mixing and matching the characters listed in Table 9.5.
For example, the following statement returns Saturday, April 01, 2008:
Format(#4/1/2008#, "dddddd")
9.3.4.4. Creating a String Format
Format also lets you create custom string formats using the options shown in Table 9.6.
For example, the following statement assigns to strUser a string consisting of four spaces if there is no input in the input box:
strUser = Format(InputBox("Enter your name:"), "@@@@")9.3.5. Using the Chr Function and Constants to Enter Special Characters in a String
To add special characters (such as a carriage return or a tab) to a string, specify the built-in constant (for those special characters that have built-in constants defined) or enter the appropriate character code using the Chr function. The syntax for the Chr function is straightforward:
Chr(charactercode)
Here, charactercode is a number that identifies the character to add.
Table 9.7 lists the most useful character codes and character constants.
Say that you wanted to build a string containing a person's name and address from individual strings containing items of that information, and that you also wanted the individual items separated by tabs in the resulting string so that you could insert the string into a document and then convert it into a table. To do this, you could use a statement like the following one. Here, VBA uses a For... Next loop to repeat the action until the counter i reaches the number stored in the variable intNumRecords:
For i = 1 to intNumRecords
AllInfo = FirstName & vbTab & MiddleInitial & vbTab _
& LastName & vbTab & Address1 & vbTab & Address2 _
& vbTab & City & vbTab & State & vbTab & Zip _
& vbTab & BusinessPhone & vbTab & HomePhone _
& vbTab & BusinessEMail & vbTab & HomeEMail & vbCr
Selection.TypeText AllInfo
Next iThe second line (split here over five physical lines) assigns data to the string AllInfo by concatenating the strings FirstName, MiddleInitial, LastName, and so on with tabs — vbTab characters — between them. The final character added to the string is vbCr (a carriage-return character), which creates a new paragraph.
The third line enters the AllInfo string into the current document, thus building a tab-delimited list containing the names and addresses. This list can then be easily converted into a table whose columns each contain one item of information: the first column contains the FirstName string, the second column the MiddleInitial string, and so on.