Creating a string from an array of strings. Fastest method?

Question

I have an array of strings (A through E) that I want to join into one string ("A B C D E"). Should I loop through the array or use the Join function?

Dim MyArray(5) as String
Dim MyString as String

MyArray(1) = "A"
MyArray(2) = "B" 
MyArray(3) = "C" 
MyArray(4) = "D" 
MyArray(5) = "E"

Which is faster and more advisable?

This?

MyString = MyArray(1)
For i = 2 To 5
    MyString = MyString & " " & MyArray(i)
Next

Or this?

MyString = Join(MyArray, " ")

I assume join will be faster as it's implemented in native code while you're growing the string at each iteration with the for loop and call twice the concatenation operator., maybe getting the system time before and after (with a larger array) could give some clue on which one is better. — Tensibai, Sep 04 '15 at 14:46
I would advise to use the solution that is more readable to you personally. For me, that would be the Join because it is much simpler conceptually, and avoids off-by-one errors and other possible subtle bugs. Why do you believe the performance of this particular piece of code is more important to your application versus everything else your application does? — mellamokb, Sep 04 '15 at 15:15
When building a string never concatenate a long string with two short strings (forcing the long string to be copied twice). So rather than `MyString = MyString & " " & MyArray(i)` use `MyString = MyString & (" " & MyArray(i))` — brettdj, Sep 06 '15 at 14:50

score 4 · Accepted Answer · answered Sep 04 '15 at 16:32

For a 100k array

Sub test()

    Dim aArr(1 To 100000) As String
    Dim i As Long
    Dim sString As String
    Dim snTimer As Single

    FillArray aArr

    snTimer = Timer

    For i = 1 To 100000
        sString = sString & Space(1) & aArr(i)
    Next i

    Debug.Print Timer - snTimer
    snTimer = Timer

    sString = Join(aArr, Space(1))

    Debug.Print Timer - snTimer

End Sub

Join is the clear winner

 2.050781 
 0

the reason is that every time you concatenate with & memory has to be reallocated to accommodate the new array (which is all strings are anyway). With Join, you're just copying one array (the source array) to another array (the string) and VBA already knows the size.

I've found this quite useful, but I just noticed it depends on the number/length of the elements. For instance, splitting a long text into an array of words, and then adding a space after each. I just tested 10 million iterations of ``"dog" & " "`` versus ``join()`` on the same two elements pre-set in a string array or variant array. In that scenario, the simple concatenation was clearly faster (about 1.7 seconds to 3.5 seconds for Variant/Variant(0 to 1), and about 2.2 seconds for String(0 to 1) in my test). — Mark E., Apr 14 '22 at 07:58

score 3 · Answer 2 · edited May 23 '17 at 12:07

If you want to combine many strings efficiently you can define a stringbuilder class.

Running the code below to build up a string of numbers up to a million takes just a fraction of a second (0.3s). Building an array and using Join takes not far off the same time (0.25s), the call to the Join function takes only about 10% of that time.

If the strings are already in an array then it makes sense to use Join but with a small number of strings the difference is unlikely to be noticeable anyway.

Sub JoinStringTest()

Dim i As Long, t As Double
Dim sb As New StringBuilder
Dim sbRet As String
Dim joinRet As String

t = Timer
For i = 1 To 1000000
  sb.Append CStr(i)
Next
sbRet = sb.Text
Debug.Print "SB", Timer - t

t = Timer
Dim a(1000000) As String
For i = 1 To 1000000
  a(i) = CStr(i)
Next i
joinRet = Join(a, "")
Debug.Print "Join", Timer - t

Debug.Print sbRet = joinRet

End Sub

Creating a string from an array of strings. Fastest method?

2 Answers2

Linked