XL97: Using VbConstants in Text Strings in MS Excel 97

MORE INFORMATION

The constants are listed in the following table.

   Constant       Definition
   -------------------------------------------------------------------
   vbBack         A backspace character [Chr(8)]
   vbCr           A carriage return [Chr(13)]
   vbCrLf         A carriage return and line feed [Chr(13) + Chr(10)]
   vbLf           A linefeed [Chr(10)]
   vbNewLine      A platform-specific new line character, either
                  [Chr(13) + Chr(10)] or [Chr(13)]
   vbNullChar     A null character of value 0 [Chr(0)]
   vbNullString   A string of value 0 [no Chr code]; note that this is
                  not the same as ""
   vbTab          A tab character [Chr(9)]
				

You can use these constants anywhere in Visual Basic code where you want them to appear. For example, you can use them to display a multiline message in a message box as in the following example: Make sure that you do not enclose the constants within quotation marks; if you do, the constant appear in the text string instead of the character it represents.

When you use these constants, you may notice the following problems.

Text Boxes and Cells

• In Microsoft Excel 97 for Windows, if you use these constants when you insert text into a text box or into a cell, a square character may appear in the text box or cell. This problem occurs if you use any of the following constants:

  vbBack vbCr vbCrLf vbNewLine vbTab

  For example, this behavior occurs if you execute the following line of code:

        ActiveCell.Value = "AAA" & vbNewLine & "BBB"
  						

  The cell displays the following value:

  AAA[square character] BBB

  You can remove the square character by manually editing the cell.
• If you use the vbNullChar constant, text following the constant may be cut off. For example, if you execute the following line of code

        ActiveCell.Value = "AAA" & vbNullChar & "BBB"
  						

  the cell displays only "AAA."
• The vbLf and vbNullString constants work correctly when you use them with text boxes and cells. For example, if you execute the following line of code

        ActiveCell.Value = "AAA" & vbLf & "BBB"
  						

  the cell displays the value:

  AAA
  BBB

  If you execute following line of code

        ActiveCell.Value = "AAA" & vbNullString & "BBB"
  						

  the cell displays the value:

  AAABBB

  Because vbNullString creates a string of value 0 (the string has no length so nothing appears in the cell).

Message Boxes (MsgBox)

All of the constants listed in this article work correctly when used in a MsgBox with just two exceptions.

• If you use the vbBack constant, a square character appears in the message box. For example, if you execute the following line of code

        MsgBox "AAA" & vbBack & "BBB"
  						

  the message box displays the message:

  AAA[square character]BBB

• If you use the vbNullChar constant, text following the constant is cut off.