VBA has built-in functions for repeating a single character:
Function String$(Number As Long, Character) As String
Function Space$(Number As Long) As String
But neither are of any use when you need to repeat a string that has more than one character.
You could repeat a string
"abcde" 5 times by doing something crafty like:
But that is neither intuitive nor performant.
In Excel, there is also
WorksheetFunction.Rept, but it is painfully slow, and only available in Excel.
So I made a custom function that builds the string, while minimizing the concatenations. In fact, it doesn't use any concatenation, but instead uses a buffer and
CopyMemory to fill the buffer. And rather than filling the buffer one instance at a time, the code fills the buffer using a lookback that reduces the number of buffer writes exponentially:
Given a string
"abcde" that repeats 5 times:
Create a buffer of 25 spaces
1st buffer write - assign the string to the first buffer position
"abcde " [NEW]
2nd buffer write - copy the existing populated buffer (5 characters) into the next buffer position
"abcdeabcde " [NEW]
3rd buffer write - copy the existing populated buffer (10 characters) into the next buffer position
"abcdeabcdeabcdeabcde " [ NEW ]`
4th buffer write - copy the lesser of the existing populated buffer (20 characters) and the remaining buffer (5 characters) into the next buffer position.
Private Declare Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As Long, ByVal source As Long, ByVal Length As Long) Public Function StringRepeat(number As Long, expression As String) As String Dim copyBufferLength As Long copyBufferLength = LenB(expression) 'Create a buffer StringRepeat = Space$(number * Len(expression)) Dim bufferLengthBytes As Long bufferLengthBytes = LenB(StringRepeat) Dim bufferPointer As Long bufferPointer = StrPtr(StringRepeat) 'Copy the original expression to the start of the buffer CopyMemory bufferPointer, StrPtr(expression), copyBufferLength Do While copyBufferLength < bufferLengthBytes Dim remainingByteCount As Long 'Check we're not going to overflow the buffer remainingByteCount = bufferLengthBytes - copyBufferLength If copyBufferLength > remainingByteCount Then CopyMemory bufferPointer + copyBufferLength, bufferPointer, remainingByteCount Else CopyMemory bufferPointer + copyBufferLength, bufferPointer, copyBufferLength End If copyBufferLength = copyBufferLength * 2 Loop End Function
The performance varies by the number of repeats, and the number of characters in the string to be repeated. I tried handling special cases like repeating a string 1 time (just return the string), and/or repeating a single character (return the result of
String$ instead), but while that speeds up the special cases, it slows down all other cases.
I'm not checking whether the
number input is positive, and I'm not checking that the string to repeat is at least 1 character long, as for now, I'm focusing on performance.
In some instances (small values of
expression lengths), avoiding the Exponential lookback approach is not as fast as a straight-up loop and copy:
Function StringRepeatSimple(number As Long, expression As String) As String Dim expressionLengthBytes As Long expressionLengthBytes = LenB(expression) 'Create a buffer StringRepeatSimple= Space$(number * Len(expression)) Dim bufferPointer As Long bufferPointer = StrPtr(StringRepeatSimple) Dim expressionPointer As Long expressionPointer = StrPtr(expression) Dim copyCounter As Long For copyCounter = 0 To number - 1 CopyMemory bufferPointer + copyCounter * expressionLengthBytes, expressionPointer, expressionLengthBytes Next copyCounter End Function