Blog of Travis Hydzik

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%.

Related posts:

1. VBA automatically saves Excel 2003 Workbook in compatibility mode as Excel 2007 Workbook
2. Generic file selection window function in VBA
3. Rigid file manipulation functions for VBA/VBS

Tags: concatenation, faster, optimizing, string, VBA

This entry was posted on Tuesday, June 9th, 2009 at 8:12 am and is filed under Programming, Visual Basic. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.