VBA Using VBA string constants


VBA defines a number of string constants for special characters like:

  • vbCr : Carriage-Return 'Same as "\r" in C style languages.
  • vbLf : Line-Feed 'Same as "\n" in C style languages.
  • vbCrLf : Carriage-Return & Line-Feed (a new-line in Windows)
  • vbTab: Tab Character
  • vbNullString: an empty string, like ""

You can use these constants with concatenation and other string functions to build string-literals with special-characters.

Debug.Print "Hello " & vbCrLf & "World"

Debug.Print vbTab & "Hello" & vbTab & "World"
'    Hello    World

Dim EmptyString As String
EmptyString = vbNullString
Debug.Print EmptyString = ""

Using vbNullString is considered better practice than the equivalent value of "" due to differences in how the code is compiled. Strings are accessed via a pointer to an allocated area of memory, and the VBA compiler is smart enough to use a null pointer to represent vbNullString. The literal "" is allocated memory as if it were a String typed Variant, making the use of the constant much more efficient:

Debug.Print StrPtr(vbNullString)    'Prints 0.
Debug.Print StrPtr("")              'Prints a memory address.