Microsoft KB Archive/169972

From BetaArchive Wiki
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.

Article ID: 169972

Article Last Modified on 11/10/2000



APPLIES TO

  • Microsoft Excel 97 Standard Edition



This article was previously published under Q169972


SUMMARY

Visual Basic for Applications includes a number of constants that you can use in macro 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)]
                

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:

MsgBox "Hello" & vbCr & "World!"


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.



Additional query words: XL97 square rectangle box

Keywords: kbprogramming kbcode KB169972