XL98: Using VbConstants in Text Strings in MS Excel 98

• Microsoft Excel 98 Macintosh Edition

SUMMARY

Microsoft Visual Basic for Applications includes a number of constants that you can use in code to represent certain "untypeable" characters, such as tabs, line feeds, and carriage returns. This article contains information about using these constants and information about problems that you may encounter when you use them.

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)]

   MsgBox "Hello" & vbCr & "World!"

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

Text Boxes and Cells

• In Microsoft Excel 98 Macintosh Edition, 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 either of the following constants:

        vbBack   vbCrLf
  
     For example, this behavior occurs if you execute the following line of
     code:
  
        ActiveCell.Value = "AAA" & vbBack & "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 vbCr, vbLf, vbNewLine, vbNullString, and vbTab 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 following 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, the character does not appear in the message box. For example, if you execute the following line of code

        MsgBox "AAA" & vbBack & "BBB"
  

  the message box displays the message:

        AAABBB
  

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