VBA String Literals - Escaping, non-printable characters and line-continuations Using VBA string constants

Help us to keep this website almost Ad Free! It takes only 10 seconds of your time:
> Step 1: Go view our video on YouTube: EF Core Bulk Insert
> Step 2: And Like the video. BONUS: You can also share it!

Example

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"
'Output:
'Hello
'World

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

Dim EmptyString As String
EmptyString = vbNullString
Debug.Print EmptyString = ""
'Output:
'True

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.


Got any VBA Question?