How to use VbConstants in text strings in Excel for Mac

This article was previously published under Q274462
This article has been archived. It is offered "as is" and will no longer be updated.
Summary
Microsoft Visual Basic for Applications includes a number of constants thatyou can use in code to represent certain "untypeable" characters, such astabs, line feeds, and carriage returns. This article contains informationabout using these constants and information about problems that you mayencounter 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(13)]   vbNewLine      Chr(13) Platform-specific new line character, whichever                  is appropriate for current platform   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)]				
Notes
  • Microsoft Visual Basic Help in Excel 2001 and Excel X is incorrect where it differs from the previous table.
  • In Excel 2004, the following VbConstants were added:
    • vbFormFeed; Chr(12); not useful in Microsoft Windows or on the Macintosh
    • vbVerticalTab; Chr(11); not useful in Windows or on the Macintosh
You can use these constants anywhere in Visual Basic code where you wantthem to appear. For example, you can use them to display a multilinemessage in a message box as in the following example:
   MsgBox "Hello" & vbCr & "World!"				
Make sure that you do not enclose the constants in quotation marks; ifyou do, the constant appears in the text string instead of the character itrepresents.

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

Text Boxes and Cells

  • In Microsoft Excel for Mac, 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, 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 following value:
    AAABBB
    because vbNullString creates a string of value 0 (the string has no length, so nothing appears in the cell).
  • The vbTab constant is replaced by a single space when you use them with cells. For example, if you execute the following line of code
         ActiveCell.Value = "AAA" & vbTab & "BBB"						
    the cell displays the value:
    AAA BBB

Message Boxes (MsgBox)

All of the constants listed in this article work correctly when used in aMsgBox, 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.
MacXLX Mac XLX XL2001 macro code VBA VB XL excel Visual Basic Editor automation
Properties

Article ID: 274462 - Last Review: 11/02/2013 17:31:00 - Revision: 5.0

  • Microsoft Excel 2004 for Mac
  • Microsoft Excel X for Mac
  • Microsoft Excel 2001 for Mac
  • kbnosurvey kbarchive dftsdahomeportal kbdtacode kbhowto KB274462
Feedback