MS Access - VBA - String manipulation

Question

I developed some code for an Access Database that manipulates a string with a statement like:

myString = Left(myString, somePosition) & Right(myString, someOtherPosition)

the above is part of a loop that has thousands of iterations and the variable myString is thousand of characters long.

I know the above code is bad practice in Java and a StringBuffer should be used instead of a string.

My code is taking a lot of time to run (about 7 minutes) and I suspect the problem might be related to the heavy string manipulation that is going on. Can you please confirm if there is anything similar to StringBuffer in VBA that could improve the efficiency of my code?

Update: full code with StringBuilder

Function SelectColumns2(str As String, columns As String, separator As String) As String

'column_number is the number of the column we are reading when we loop through a line
'z is the counter of the field (a portion of str between two separators)
'i is the counter of the str (the position of the modified string)
Dim column_number As Integer, i As Double, z As Integer, leftPosition As Double
'stringbuilder that stores the string that will represent the final file
Dim sb As StringBuilder, leftStr As StringBuilder, rightStr As StringBuilder

Set sb = New StringBuilder
Set leftStr = New StringBuilder
Set rightStr = New StringBuilder
sb.Append str
column_number = 1
i = 1 ' full str
z = 0 ' full field

While sb.Length >= i
        z = z + 1
        If Mid(sb.Text, i, 1) = separator Then
            If InStr(1, columns, "/" & column_number & "/") = 0 Then
                leftStr.Append left(sb.Text, i - z)
                rightStr.Append right(sb.Text, sb.Length - i)
                sb.Clear
                sb.Append leftStr.Text
                sb.Append rightStr.Text
                leftStr.Clear
                rightStr.Clear
                i = i - z
            End If
            column_number = column_number + 1
            z = 0
        ElseIf Mid(sb.Text, i, 1) = Chr(10) Then
            If InStr(1, columns, "/" & column_number & "/") = 0 Then
                leftPosition = max((i - z - 1), 0)
                If leftPosition = 0 Then
                    leftStr.Append left(sb.Text, leftPosition)
                    rightStr.Append right(sb.Text, sb.Length - i)
                    sb.Clear
                    sb.Append leftStr.Text
                    sb.Append rightStr.Text
                Else
                    leftStr.Append left(sb.Text, leftPosition)
                    rightStr.Append right(sb.Text, sb.Length - i + 1)
                    sb.Clear
                    sb.Append leftStr.Text
                    sb.Append rightStr.Text
                End If
                leftStr.Clear
                rightStr.Clear
                i = i - z
            End If
            column_number = 1
            z = 0
        End If
        i = i + 1
Wend

SelectColumns2 = left(sb.Text, sb.Length - 1)

End Function

FWIW - if `myString` was `"some random string"` and `somePosition` was `2` then you are modifying `myString` to be `"song"`, i.e. taking the left 2 positions and right 2 positions - is that really what you intended? — YowE3K, Jan 03 '18 at 19:33

Erik A · Answer 1 · 2018-01-03T19:48:43.383

2

You can use CreateObject to create the .Net stringbuilder class. Note that you will have to have the relevant .Net library installed, and VBA does not support overloading, so it will handle a little differently than in VB.Net.

Sample code:

Public Sub TestSB()
    Dim sb As Object
    Set sb = CreateObject("System.Text.StringBuilder")
    sb.Append_3 "Hello"
    sb.Append_3 " "
    sb.Append_3 "World"
    sb.Append_3 "!"
    Debug.Print sb.ToString
End Sub

Alternatively, you can build your own stringbuilder. This answer provides a stringbuilder class, and this question also shows some sample code.

edited Jan 03 '18 at 19:48

answered Jan 03 '18 at 19:43

Erik A

31,639
12
42
67

1

I bet [this StringBuilder class](https://codereview.stackexchange.com/q/67596/23788) performs much better than that SO link, and quite possibly better than the .net one, given it eliminates the need for .NET/COM interop overhead. – Mathieu Guindon Jan 03 '18 at 20:02
That might certainly be true, it looks like really good code :) – Erik A Jan 03 '18 at 20:11
I tried the stringbuilder but now the code, although working as it should, is even slower. Probably I did not use the stringbuilder well. By the way the function I am building is supposed to take a csv file previously converted into a string and remove columns not required. See above in the original question the full code with the string builder. – UbuntuDude Jan 04 '18 at 18:24

score 1 · Answer 2 · answered Jan 04 '18 at 09:39

You can - for an extremely simple implementation - use Mid.

For example, this code runs in about 0.1 ms for the quite large strings entered:

Public Function ChopString() As String

    Dim Source      As String
    Dim LeftPart    As Long
    Dim RightPart   As Long
    Dim Result      As String

    Source = String(100000, "x")
    LeftPart = 30000
    RightPart = 40000

    Result = Space(LeftPart + RightPart)
    Mid(Result, 1) = Left(Source, LeftPart)
    Mid(Result, 1 + LeftPart) = Right(Source, RightPart)

    ChopString = Result

End Function

For smaller strings of a few K, it runs way faster.

MS Access - VBA - String manipulation

2 Answers2