VBA Breaking Up a Large String

Question

I'm looking for advice on breaking up a very large string into many smaller strings. As an example, I have a string of 5000 characters. I have to split that string up into a sequential array of variables no larger than 450 characters. Would the following be the most efficient way to do it?

Dim stringSection() as String

Private Sub extractSubStrings(giantString as String)

    Dim occurance as Integer

    occurance = 0
    For i = 1 to Len(giantString)
        If i Mod 450 = 1 Or i = Len(giantString) Then
            occurance = occurance + 1
            ReDim stringSection(occurance)
            stringSection = Mid(giantString, i-450, 450)
        End If
    Next i

End Sub

Have you tried Split? https://stackoverflow.com/questions/13195583/split-string-into-array-of-characters — Daniel Gale, Feb 27 '18 at 15:02
Do you want the strings to be as balanced in length as possible? — John Coleman, Feb 27 '18 at 15:02
@JohnColeman Yes, the strings cannot exceed 450 or it will cause errors downstream of this code. — addohm, Feb 27 '18 at 15:02
You are not trying to split on logical points with this code, does it matter? you just want an array of strings 450 characters long? — Scott Craner, Feb 27 '18 at 15:05
@ScottCraner Sequential. I want to chop it up, in order, 450 chars at a time. — addohm, Feb 27 '18 at 15:07
If you can't make use of a delimiter, there are also several examples here: http://www.vbforums.com/showthread.php?501338-Split-string-at-length — Daniel Gale, Feb 27 '18 at 15:07
@Jaberwocky - Why don't you use Mid() function? you can loop through the string until you reach the end if it — aberforth, Feb 27 '18 at 15:08
@Jaberwocky - Yes I did... Did you check the below solutions? Both of them use `MID()` function so not sure what's your point here? — aberforth, Feb 27 '18 at 15:26

score 5 · Accepted Answer · answered Feb 27 '18 at 15:08

Here is a VBA function that might help:

Function BreakUp(s As String, k As Long) As Variant
    'returns a 0-based variant array of strings of length <= k
    Dim i As Long, m As Long, n As Long
    Dim chunks As Variant
    n = Len(s)
    m = Int(n / k)
    If n - k * m > 0 Then 'leftover chunk at end
        ReDim chunks(0 To m)
    Else
        ReDim chunks(0 To m - 1)
    End If
    For i = 0 To UBound(chunks)
        chunks(i) = Mid(s, 1 + k * i, k)
    Next i
    BreakUp = chunks
End Function

After you grab the array then you can iterate over it, although if all you want to do is iterate over it then the above function has no real benefit over simply using Mid in a loop in the calling code.

score 1 · Answer 2 · answered Feb 27 '18 at 15:27

Here's a function that keeps things simple, utilising Split based on a forced delimiter. Change the delimiter to something suitable if your string can contain a /

Function cut_into_pieces(txt As String, piece_length As Long) As Variant
    Dim x As Long, delim As String
    delim = "/"
    pieces = ""
    For x = 0 To Int(Len(txt) / piece_length)
        this_piece = Mid(txt, 1 + (x * piece_length), piece_length)
        If this_piece <> "" Then cut_into_pieces = cut_into_pieces & this_piece & delim
    Next
    cut_into_pieces = Split(Left(cut_into_pieces, Len(cut_into_pieces) - 1), delim)
End Function

You would call this with arr = cut_into_pieces(giantString, 450)

Alex Martinez · Answer 3 · 2018-02-27T15:50:19.983

ReDim use a lot of memory and it's enever a good practice to use them in Loops where you will need to ReDim a big ampunt of times. Have you used Collection and Do Until Loops? Why don't you try this?

Private Sub extractSubStrings(ByVal giantString As String)

    Dim colOfSmallStrings As Collection

    Set colOfSmallStrings = New Collection

    Do
        colOfSmallStrings.Add Left$(giantString, 450)
        giantString = Mid$(giantString, 451, Len(giantString))

    Loop Until Len(giantString) < 450

    ' Add final piece of string
    colOfSmallStrings.Add Left(giantString, 450)
End Sub

VBA Breaking Up a Large String

3 Answers3