string buffer in VBA?

[geoffgeoffgeoff]

Hi,

I was wondering whether there is some way to construct large strings in Excel, without the overhead of string concatenation - maybe something like StringBuffer in Java?

 

[Norie]

What exactly do you want to do?

I can't see what you mean about the 'overhead of string concatenation'.

And StringBuffer in Java doesn't seem to offer any advantages.

Can't you just use the VBA text functions Mid, Left, Right, InStr, InStrRev?

Or their worksheet function equivalents?

 

[johnywhy]

Use the worksheet. In Excel 2016, a cell can contain 32,767 characters. Use your VBA to store data in a cell, on a "scrap" worksheet.

 

[Rick Rothstein]

You can use the Mid as a statement (not as a function) and do "buffer stuffing" although you will need to track where to place every new item. For example, let's create a 50-character buffer and stuff in the words "one", "two" and "three" into it using a space between to delimit them...

Sub ExampleOfBufferStuffing()
  Dim Count As Long, Start As Long, Buffer As String, Word As Variant
  Buffer = Space(50)
  Start = 1
  For Each Word In Array("One", "Two", "Three")
    Mid(Buffer, Start, Len(Word)) = Word
    Start = Start + Len(Word) + 1
  Next
 
  ' Let's see what is in the buffer
  ' Note the angle brackets showing the buffer
  Debug.Print "<" & Buffer & ">"
End Sub

Edit Note: I just noticed the original thread is 15 years old and that johnywhy resurrected it. Still, the code I posted will do what the OP asked about.

 

[johnnyL]

Edit Note: I just noticed the original thread is 15 years old and that johnywhy resurrected it. Still, the code I posted will do what the OP asked about.

LOL. I got roped in like that a couple of weeks ago in a different thread. After I posted to thread, I checked back a while later, and I was WTF? This is an old thread, why did someone piggy back this thread with a 'new' post and rope me in like that.