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:
Here, charactercode is a number that identifies the character to add.
Table below lists the most useful character codes and character constants.VBA character codes and character constants
Code Built-in Character Character Constant Chr(9) vbTab Tab Chr(10) vbLf Line feed Chr(11) vbVerticalTab Softreturn (Shift+Enter) Chr(12) vbFormFeed Page break Chr(13) vbCr Carriage return Chr(13) + vbCrLf Carriage return/line feed combination Chr(10) Chr(14) - Column break Chr(34) - Double straight quotation marks (") Chr(39) - Single straight quote mark/apostrophe (') Chr(145) - Opening single smart quotation mark (') Chr(146) - Closing single smart quotation mark/apostrophe (') Chr(147) - Opening double smart quotation mark (") Chr(148) - Closing double smart quotation mark (") Chr(149) - Bullet Chr(150) - En dash Chr(151) - Em dash
Here's a practical example. Say you wanted to build a string containing a person's name and address from individual strings containing items of that information. 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 easily convert it into a table.
To do this, you could use the following code:
Sub FormatTabular() Dim i As Integer Dim strFirstName As String Dim strLastName As String Dim strAddress As String Dim strCity As String Dim strState As String Dim strAllInfo As String strFirstName = "Mike" strLastName = "Fin" strAddress = "15 Batwing Dr." strCity = "Tulsa" strState = "NY" strAllInfo = strFirstName & vbTab & strLastName _ & vbTab & strAddress & vbTab & strCity _ & vbTab & strState & vbCr Selection.TypeText strAllInfo End Sub
String variables are assigned to the string strAllInfo by concatenating the strings strFirstName, strLastName, and so on with tabs - vbTab characters - between them. The final character added to the built string is vbCr (a carriage-return character), which creates a new paragraph.
The final line enters the strAllInfo string into the current document, thus building a tabdelimited 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 strFirstName string, the second column the strLastName string, and so on.
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