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? stackoverflow.com/questions/13195583/… — Daniel Gale
– Daniel Gale, Commented Feb 27, 2018 at 15:02
Do you want the strings to be as balanced in length as possible? — John Coleman
– John Coleman, Commented Feb 27, 2018 at 15:02
@JohnColeman Yes, the strings cannot exceed 450 or it will cause errors downstream of this code. — addohm
– addohm, Commented Feb 27, 2018 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
– Scott Craner, Commented Feb 27, 2018 at 15:05

John Coleman · Accepted Answer · 2018-02-27 15:08:58Z

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.

CLR · Accepted Answer · 2018-02-27 15:27:05Z

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 · Accepted Answer · 2018-02-27 15:50:19Z

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

Collectives™ on Stack Overflow

VBA Breaking Up a Large String

3 Answers 3

Comments

Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Linked

Related