Creating a string from an array of strings. The fastest method?

Question

I have an array of strings (A to E) that I want to combine into a single string ("ABCDE"). Should I go through an 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 expedient?

It?

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

Or that?

 MyString = Join(MyArray, " ") 

Answer 1

For an array of 100 KB

 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 - clear winner

  2.050781 0 

the reason is that every time you combine with memory & , you need to reallocate to place a new array (all the same all the rows). With Join, you simply copy one array (the original array) to another array (string), and VBA already knows the size.

Answer 2

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

Running the code below to create a string of numbers up to a million takes only a fraction of a second (0.3 s). Creating an array and using Join is just around the corner (0.25 s), calling the Join function takes about 10% of that time.

If the lines are already in the array, then it makes sense to use Join , but with a small number of lines the difference is unlikely to be noticeable in any case.

 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