string buffer in VBA?

geoffgeoffgeoff

New Member
Joined
Oct 13, 2006
Messages
1
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? :rolleyes:
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,300
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Sep 12, 2008
Messages
40
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
  5. 2011
  6. 2010
  7. 2007
Platform
  1. Windows
  2. MacOS
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

MrExcel MVP
Joined
Apr 18, 2011
Messages
37,173
Office Version
  1. 2019
  2. 2010
Platform
  1. Windows
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...
VBA Code:
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.
 
Last edited:

johnnyL

Well-known Member
Joined
Nov 7, 2011
Messages
836
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows
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. :)
 

Forum statistics

Threads
1,136,878
Messages
5,678,324
Members
419,756
Latest member
vincent86kapelski

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top