Optimizing/faster String Concatenation in VBA

There are numerous links about Visual Basic string concatenation, one particular is Microsoft’s How To Improve String Concatenation Performance. But the article is overwritten for the point it is trying to make, so I will share a simplified example.

Lets say we want to perform the following concatenation:

Dim i As Long
Dim s As String
For i = 1 To 100000000
	s = "A" & i & "B"
Next i

This takes approximately 6000 ticks. The faster approach, yet more complex functionality would be as follows:

Dim sourceLength As Long
sourceLength = 1
Dim source As String
s = "A B"

Dim i As Long
Dim s As String
For i = 1 To 100000000
	source = CStr(i)
	If Len(source) > sourceLength Then
		sourceLength = Len(source)
		s = "A" & Space$(sourceLength) & "B"
	End If
	Mid$(s, 2, sourceLength) = source
Next i

Which takes approximately 3700 ticks, a saving of nearly 40%.

  • Sven

    off topic

    Hi
    i wonder, did u ever finished your Videosphere project?
    I just bought one myself and would be intrestet in your work.

    Regards
    Sven

  • thydzik

    Hi Sven

    No, have not finished it yet. But still plan to. Just been too busy with work, the usual story.

    I have recently purchased some optical encoders which I think may finally work as digital knobs.

  • JamesD

    Thanks for the useful info. It’s so interesting

  • Sven

    Busy, tell me about it ;-).
    I think i will get some connectors and an RF modulator to give it some use.
    Maybe gonna play some vintage black and white movies or so.