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, " ")

Answer 1

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.

Answer 2

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