VBA Strings

The String data type represents text. A string is an array of characters. VBA strings use 16-bit Unicode characters. VBA has a built-in Strings module with functions for manipulating strings. Efficiency is an important topic when it comes to dealing with strings.

String Literals

String literals are used to represent strings in code. Text surrounded by double quotes represents a string literal in VBA.

Public Sub Example()

    Debug.Print "Hello, World!" 'Prints: Hello, World!

End Sub

To create a string literal that contains double quotes, use double double quotes.

Public Sub Example()

    Debug.Print "Hello, World!"       'Prints: Hello, World!

    Debug.Print """Hello, World!"""   'Prints: "Hello, World!"

    Debug.Print "Hello, ""World""!"   'Prints: Hello, "World"!

    Debug.Print """"                  'Prints: "

    Debug.Print """"""                'Prints: ""

End Sub

String Type Conversion

To explicitly convert a value to a string use the CStr function. The Str function can be used to convert a number to a string. The Str function only recognizes the period as a decimal place, so when working with international numbers where the decimal may be a comma, use the CStr function.

Public Sub Example()

    Dim S As String

    S = CStr(350)         ' -> "350"

    S = CStr(3.5)         ' -> "3.5"

    S = CStr(True)        ' -> "True"

    S = CStr(#1/1/2021#)  ' -> "1/1/2021"

    S = CStr(3.5@)        ' -> "3.5"

End Sub

String Concatenation

Both the & and + operators can be used to concatenate strings in VBA. Using the & operator will implicitly convert non-string values to strings. Using the + operator will not implicitly convert non-strings to strings. Trying to concatenate a string value with a non-string value using + will cause a type mismatch error. The CStr function can be used to explicitly convert non-string values to strings. There is no significant difference in speed between using & and +.

Public Sub Example()

    Debug.Print "Hello, " + "World!"

    'Debug.Print "Hello, " + 1 'Causes Error

    Debug.Print "Hello, " + Cstr(1)

End Sub

Public Sub Example()

    Debug.Print "Hello, " & "World!"

    Debug.Print "Hello, " & 1

    Debug.Print "Hello, " & Cstr(1) 'Not necessary to use CStr with &

End Sub

Variable-Length Strings

Variable-Length Strings can hold a large number of characters (2 ^ 31). The size of a Variable-Length String is determined by the number of characters in the String. The Len and LenB functions can be used to return the number of characters and bytes respectively in a String.

Public Sub Example()

    Dim S As String

    S = "Hello, World!"

    Debug.Print S

    Debug.Print Len(S)
    Debug.Print LenB(S)

End Sub

Fixed-Length Strings

Fixed-length strings are given a specific length at design-time when they are declared which cannot be altered at run-time. When a string is too long to fit in the fixed-length string it will be truncated. When a string is shorter than the fixed-length string the remaining space will be set to space characters (Chr 32). An uninitialized fixed-length string will be all null characters (Chr 0) for the length of the string. Fixed-Length strings can store from 1 to 65,535 characters. The Len and LenB functions can be used to get the length of a string in terms of characters and bytes respectively.

Public Sub Example()

    Dim S As String * 10

    S = "Hello, World!"
    Debug.Print """" & S & """" 'Prints: "Hello, Wor"

    S = "ABCD"
    Debug.Print """" & S & """" 'Prints: "ABCD      "

    Debug.Print Len(S)
    Debug.Print LenB(S)

End Sub

Byte Arrays

Byte arrays and Strings are interchangeable. Byte Arrays can be assigned directly to Strings and Strings can be assigned directly to Byte Arrays.

Public Sub Example()

    Dim S As String
    S = "Hello, World!"

    Dim Arr() As Byte
    Arr = S

    Debug.Print Arr

End Sub

VBA Strings use two bytes to store each character so every two bytes in a byte array represents one character. Most common characters only require one byte to store and thus the second byte will be zero.

Public Sub Example()

    Dim Arr(0 To 9) As Byte
    Arr(0) = 72
    Arr(1) = 0
    Arr(2) = 101
    Arr(3) = 0
    Arr(4) = 108
    Arr(5) = 0
    Arr(6) = 108
    Arr(7) = 0
    Arr(8) = 111
    Arr(9) = 0

    Debug.Print Arr 'Prints: Hello

End Sub

String Comparison

Strings can be compared using comparison operators or the StrComp function.

Option Compare Binary

Public Sub Example()
    Debug.Print "A" = "a" 'Prints: False
End Sub

Option Compare Text

Public Sub Example()
    Debug.Print "A" = "a" 'Prints: True
End Sub

Public Sub Example()

    Debug.Print StrComp("A", "a", vbTextCompare) = 0 'Prints: True

End Sub

Mid

The Mid function can be used to retrieve sections of a string, iterate over each individual character in a string, and to assign sections of a string without reallocating an entirely new string. Using Mid to alter strings is faster than allocating new strings.

Public Sub Example()

    Dim S As String

    S = "Hello, World!"

    'Get slice of string
    Debug.Print Mid$(S, 1, 2)

    'Loop over each character
    Dim i As Long
    For i = 1 To Len(S)
        Debug.Print Mid$(S, i, 1)
    Next i

    'Reassign character
    Mid(S, 1, 1) = "A"
    Debug.Print S 'Prints: Aello, World!

End Sub

StrConv

The StrConv function can be used to convert a string's case and encoding.

*Applies to East Asia locales. **Applies to Japan only.

Public Sub Example()

    Debug.Print StrConv("hello, world!", vbProperCase) 'Prints: Hello, World!

End Sub

Public Sub Example()

    Dim Arr(0 To 4) As Byte
    Arr(0) = 72
    Arr(1) = 101
    Arr(2) = 108
    Arr(3) = 108
    Arr(4) = 111

    Debug.Print StrConv(Arr, vbUnicode) 'Prints: Hello

End Sub

LSet and Rset

LSet and RSet can be used to assign strings while aligning the text to the left or right of the string. LSet and RSet are used with fixed-length strings and strings that are already assigned so that they have a specific length. There is some performance improvement using LSet and RSet compared to normal string assignment. Any unassigned characters will be filled with space characters. If the string is longer than the string variable it will be truncated.

Public Sub Example()

    Dim S As String
    S = Space$(10)

    LSet S = "Hello"
    Debug.Print """" & S & """" 'Prints: "Hello     "

    RSet S = "Hello"
    Debug.Print """" & S & """" 'Prints: "     Hello"

End Sub

Public Sub Example()

    Dim S As String * 10

    LSet S = "Hello"
    Debug.Print """" & S & """" 'Prints: "Hello     "

    RSet S = "Hello"
    Debug.Print """" & S & """" 'Prints: "     Hello"

End Sub

Cleaning Text

Text may contain characters that need to be removed. Extra spaces, non-printable characters, non-breaking spaces, and new line characters are some examples of characters that may need to be removed from text.

Public Sub Example()

    Dim S As String
    S = "   Hello, World!   "

    Debug.Print """" & LTrim$(S) & """" 'Prints: "Hello, World!   "

    Debug.Print """" & RTrim$(S) & """" 'Prints: "   Hello, World!"

    Debug.Print """" & Trim$(S) & """"  'Prints: "Hello, World!"

End Sub

Public Function TrimAll(S As String) As String

    TrimAll = Trim$(S)

    Do While InStr(TrimAll, "  ") > 0
        TrimAll = Replace(TrimAll, "  ", " ")
    Loop

End Function

Public Function RemoveNonPrintableCharacters(S As String) As String

    Dim OutString As String
    Dim CurrentCharacter As String * 1
    Dim i As Long
    Dim j As Long

    OutString = Space$(Len(S))

    For i = 1 To Len(S)

        CurrentCharacter = Mid$(S, i, 1)

        If Asc(CurrentCharacter) > 31 Then
            j = j + 1
            Mid$(OutString, j, 1) = CurrentCharacter
        End If

        RemoveNonPrintableCharacters = Left$(OutString, j)

    Next i

End Function

Public Function ReplaceNonBreakingSpaces(S As String) As String

    ReplaceNonBreakingSpaces = Replace(S, Chr(160), Chr(32))

End Function

Public Function ReplaceNewLineCharacters( _
S As String, Optional Replacement As String = " ") As String

    ReplaceNewLineCharacters = _
        Replace(S, vbCrLf, Replacement)

    ReplaceNewLineCharacters = _
        Replace(ReplaceNewLineCharacters, vbLf, Replacement)

    ReplaceNewLineCharacters = _
        Replace(ReplaceNewLineCharacters, vbCr, Replacement)

End Function

Formatting Functions

VBA provides useful built-in functions for formatting text.

Public Sub Example()

    '''Dates
    Debug.Print Format$(Now, "mm/dd/yyyy")
    Debug.Print Format$(Now, "Short Date")
    Debug.Print Format$(Now, "mmmm d, yyyy")
    Debug.Print Format$(Now, "Long Date")
    Debug.Print Format$(Now, "Long Time")

    '''Numbers
    Debug.Print Format$(2147483647, "#,##0")
    Debug.Print Format$(2147483647, "Standard")
    Debug.Print Format$(2147483647, "Scientific")

    '''Strings
    'Fill from right to left
    Debug.Print Format$("ABCD", "'@@_@@_@@'")

    'Fill from left to right
    Debug.Print Format$("ABCD", "!'@@_@@_@@'")

    'Lower case
    Debug.Print Format$("ABCD", "<'@@_@@_@@'")

    'Upper case
    Debug.Print Format$("ABCD", ">'@@_@@_@@'")

    'No spaces for missing characters
    Debug.Print Format$("ABCD", "'&&_&&_&&'")

    'Escape double quotes
    Debug.Print Format$("ABCD", "!\""@@_@@_@@\""")

End Sub

Character Constants

Character Constants are constants used to represent certain special characters. Character constants are defined in the built-in VBA.Constants module.

Built-in Strings Module Functions

Public Function AscW2(Char As String) As Long

    'This function should be used instead of AscW

    AscW2 = AscW(Char)

    If AscW2 < 0 Then
        AscW2 = AscW2 + 65536
    End If

End Function

Public Function AscW2(Char As String) As Long

    'This function should be used instead of AscW

    AscW2 = AscW(Char) And &HFFFF&

End Function

Types of String Functions

The behaviors of several string functions in VBA can be altered by affixing a $, B, W, or some combination of the three to the end of the function name.

String Classes

Using custom string classes can facilitate working with strings and make working with strings more efficient. the StringBuilder and ArrayList classes have high-level functionality and are designed to mitigate the inefficiencies of with working with strings.

Public Sub Example()

    '''Must import clsStringBuilder to VBA project

    Dim SB As clsStringBuilder
    Set SB = New clsStringBuilder

    SB.Append "ABC"
    SB.Append "123"

    SB.Insert 3, "XYZ"

    Debug.Print SB.Substring(3, 5)

    SB.Remove 3, 5

    Debug.Print SB.ToString

End Sub

Public Sub Example()

    '''Must import clsArrayListString to VBA project

    Dim AL As clsArrayListString
    Set AL = New clsArrayListString

    'Build message
    AL.Append "Hello"
    AL.Append "How are you?"
    AL.Append "I'm great! Thank you."
    Debug.Print AL.JoinString(vbNewLine)

    AL.Reinitialize

    'Sort strings
    AL.Append "C"
    AL.Append "B"
    AL.Append "A"
    Debug.Print AL.JoinString
    AL.Sort
    Debug.Print AL.JoinString

End Sub