MS-Excel / Functions and Formula

---

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 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.