String Building Class

I read that someone uses a string builder class in vba. For some reason, that thought had never occurred to me, but I thought I’d give it a try. Here’s my first attempt.

Private msText As String

Public Property Get Text() As String

    Text = msText

End Property

Public Sub Append(sText As String, Optional sDelim As String = vbNewLine)
    msText = msText & sText & sDelim
End Sub

Public Sub AppendFormat(vNumber As Variant, sFormat As String, _
    Optional sDelim As String = vbNewLine)
    msText = msText & Format(vNumber, sFormat) & sDelim
End Sub

Public Sub Insert(sText As String, lPosition As Long)
    msText = Left$(msText, lPosition – 1) & sText & Mid$(msText, lPosition, Len(msText))
End Sub

Public Sub StrReplace(sOldText As String, sNewText As String, _
    Optional lStart As Long = 1, Optional lHowMany As Long = -1)
    msText = Replace(msText, sOldText, sNewText, lStart, lHowMany)
End Sub

Public Sub PrintImmediate()
    Debug.Print msText
End Sub

Public Sub Message()
    MsgBox msText, vbOKOnly
End Sub

Public Property Get Reverse()
    Dim i As Long
    Dim sReturn As String
    For i = Len(msText) To 1 Step -1
        sReturn = sReturn & Mid(msText, i, 1)
    Next i
    Reverse = sReturn
End Property

And to use it

Sub TestSB()
    Dim sb As CStringBuilder
    Set sb = New CStringBuilder
    sb.Append “The quick brown”, ” “
    sb.Append “fox jumped over”, ” “
    sb.Append “the lazy dog.”
    sb.StrReplace “lazy”, “stupid”
    sb.AppendFormat 14.5, “$#,##0.00”
    Debug.Print sb.Reverse
End Sub

Do you use a string builder class? What properties and methods am I missing?

Posted in Uncategorized

7 thoughts on “String Building Class

  1. Dick –

    Why roll your own?

    Public Property Get Reverse()
        Reverse = StrReverse(mstext)
    End Property


  2. One reason why people roll their own string building class in VBA is to get around the performance hit imposed by regular string concatenation. Each concatenation requires a string copy, so building a string by concatenation means repeatedly copying most of it. What should be a time proportional to the string length ends up taking a time proportional to the *square* of the length.

    You can make a very simple “string appender” class by just stuffing the individual strings into a Collection, and then copying them into an array and using Join() to get back your built-up string. (I’ve never tested to see how Join() performs, though.)

    Here is a nifty example of one that uses a string buffer instead:

  3. You may have read my comment over on Smurf’s blog. My implementation is much more simplistic and was driven mainly by the need to track variables through a complicated process and spit out the results at the end, and my hate for constantly typing “& vbCrLf”.

    Here is my CStringBuilderItem:

    Private m_ItemText As String

    Public Property Get ItemText() As String
        ItemText = m_ItemText
    End Property

    Public Property Let ItemText(ByVal NewText As String)
        m_ItemText = NewText
    End Property

    Here is my CStringBuilder Class:

    Private mCol As Collection

    Public Sub AddText(NewText As String, Optional sKey As String)
        Dim oSBI As CStringBuilderItem
        Set oSBI = New CStringBuilderItem
        oSBI.ItemText = NewText
        If Len(sKey) = 0 Then
            mCol.Add oSBI
            mCol.Add oSBI, sKey
        End If
        Set oSBI = Nothing

    End Sub

    Public Function ToString(Optional ByVal AddNewLine As Boolean = True) As String
        Dim returnValue As String
        Dim delimiter As String
        Dim i As Integer
        If mCol.Count = 0 Then
            returnValue = “”
            returnValue = mCol.Item(1).ItemText
            If mCol.Count > 1 Then
                If AddNewLine Then
                    delimiter = vbCrLf
                    delimiter = ” “
                End If
                For i = 2 To mCol.Count
                    returnValue = returnValue & delimiter & mCol.Item(i).ItemText
            End If
        End If
        ToString = returnValue
    End Function

    Public Sub ClearAll()
        Dim i As Integer
        Dim ii As Integer
        ii = mCol.Count
        For i = ii To 1 Step -1
            mCol.Remove i
    End Sub

    Public Property Get Item(vntIndexKey As Variant) As CStringBuilderItem
        Set Item = mCol(vntIndexKey)
    End Property

    Public Property Get Count() As Long
        Count = mCol.Count
    End Property

    Public Sub Remove(vntIndexKey As Variant)
        mCol.Remove vntIndexKey
    End Sub

    Public Property Get NewEnum() As IUnknown
        Set NewEnum = mCol.[_NewEnum]
    End Property

    Private Sub Class_Initialize()
        Set mCol = New Collection
    End Sub

    Private Sub Class_Terminate()
        Set mCol = Nothing
    End Sub

    Again, it’s nothing special, but it saves me time when writing debugging tracers.

  4. If you’re going to make an unnecessary text string class, why wouldn’t you also make an unnecessary number class which including number format for string representation as one of its properties?

    VBA doesn’t provide overloading, so if syntax checking is wanted, methods and properties can accept one and only one type for each argument. The alternative of passing arguments as variants means no SYNTAX checking, but the need to put RUNTIME type checking code into methods and properties and throw RUNTIME errors when they’re passed anything invalid.

    VBA’s object-oriented facilities are half-baked. Best avoided when possible. A text string class as a wrapper around a plain String variable is a fine example of an unnecessary and inefficient class.

  5. Hi folks, here’s my small but very efficient class:

    Private m_arrBuffer
    Private m_strDelimiter

    Private Sub Class_Initialize()
        m_arrBuffer = Array()
        m_strDelimiter = “”
    End Sub

    Private Sub Class_Terminate()
        m_arrBuffer = Empty
    End Sub

    Public Property Get Delimiter()
        Delimiter = m_strDelimiter
    End Property

    Public Property Let Delimiter(strDelimiter)
        m_strDelimiter = strDelimiter
    End Property

    Public Sub Append(strValue)
        ReDim Preserve m_arrBuffer(UBound(m_arrBuffer) + 1)
        m_arrBuffer(UBound(m_arrBuffer)) = strValue
    End Sub

    Public Sub AppendLine(strValue)
        Me.Append strValue & vbCrLf
    End Sub

    Public Sub Compact()
        If Not Me.Delimiter = “” Then
            strOriginalDelimiter = Me.Delimiter
            Me.Delimiter = “”
        End If
        strTemp = Me.ToString
        m_arrBuffer = Array()
        Me.Append strTemp
        Me.Delimiter = strOriginalDelimiter
    End Sub

    Public Function ToArray()
        ToArray = m_arrBuffer
    End Function

    Public Function ToString()
        ToString = Join(m_arrBuffer, m_strDelimiter)
    End Function

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.