Results 1 to 6 of 6
  1. #1
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Fast string concatenation (2000)

    I stumbled across some code (which can be viewed here) that claims the following:

    "Each time that you use the ampersand ("&") operator, Visual Basic allocates memory for the new string and copy the old string into the new string. If you concatenate a lots of small strings into one big string, the operation is quite ineffective because Visual Basic allocates memory, copy, and deallocates memory a lots of times !
    You can solve this problem by explicitly allocating big chunks of strings when it needed.
    The following class lets you easily and efficiently concatenate strings. It automatically allocates more space when it needed, in chunks of 1000 characters. You can download the sample project below, and see that string concatenation with this class might be much faster than doing it with ampersand operator."

    This is VB-related and I'm wondering firstly if it would work in VBA and, if so, would it be of value or detriment to use in typical VBA projects. I can see, in principle, where it might be used - concatenating long text destined for a message box for instance - but I am ignorant of memory/performance issues specific to VBA. Any recommendations from the wise?

    Alan

  2. Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: Fast string concatenation (2000)

    It works in VBA too, and whether you need it depends on what you do. If you concatenate a few hundred strings, the difference in performance is hardly noticeable. If you need to concatenate hundreds of thousands of strings, the class approach is hugely faster. As a test, I concatenated 1,000, 10,000 and 100,000 short strings. Here are the results (in seconds):

    <table border=1><td>number of strings</td><td>standard method</td><td>class method</td><td align=right>1,000</td><td align=right>0.0003</td><td align=right>0.0001</td><td align=right>10,000</td><td align=right>0.08</td><td align=right>0.02</td><td align=right>100,000</td><td align=right>30.6</td><td align=right>0.27</td></table>
    Note that the standard method takes excessively long for 100,000 strings.

  4. #3
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast string concatenation (2000)

    Thank you Hans. This is just the kind of "words from the wise" I was after. For my own humble projects, I'd be dealing with tens or perhaps even a couple of hundred concatenations, so the alternative method would not even reach the point of consideration. BTW, there are a few other code samples on that site that might be worth a look:

    Faster items adding in ComboBox
    Painting a Gradient between 2 colors on a form
    Drawing a rotated text on a form
    Creating a circle shaped window
    ...

    thanks again for the thorough lodown

    Alan

  5. #4
    2 Star Lounger
    Join Date
    Feb 2001
    Location
    Brussels, Brussel, Belgium
    Posts
    159
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Fast string concatenation (2000)

    yes, http://www.xbeat.net/vbspeed/ is good site too for this kind of thing (with lots of things that word in VBA also)

    i believe you can speed things up even further by applying brackets when you concatenate a string (i don't think you'll find this in the vbspeed site). this will result in a noticeable speed gain when you cancatenate long strings.
    so in stead of
    a=a&b
    you'd use
    a=(a&[img]/forums/images/smilies/cool.gif[/img]

  6. #5
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Fast string concatenation (2000)

    I think using parentheses on the right side of an assignment in VBA does an implicit "by value" on the expression. I don't know enough about memory management to know why this might be faster, or when.

  7. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Fast string concatenation (2000)

    Using parens forces the evaluation of expressions inside the parens and controls the order to the evaluation. I'm not sure why it makes it faster, if it does.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •