String Repeat function in VBA

Question

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:

?Join(Split(String$(5,"."),"."),"abcde")

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.

"abcdeabcdeabcdeabcdeabcde"
 [NEW]

StringRepeat

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 number, short expression lengths), avoiding the Exponential lookback approach is not as fast as a straight-up loop and copy:

RepeatString Simple

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

• Aleksei Zabrodskii: I would just copy original string n times in a simple loop, no need to make first copy special or check for out of bounds error, just a simple loop with mem copy: first starting at 0, then at Len, 2*len, etc. Not sure about 0円 byte in VB, so keep that in mind.
• ThunderFrame: @elmigranto that's what the StringRepeatSimple alternative does, but for number = 1000 and expression = "ab", StringRepeat performs about 20 times faster than StringRepeatSimple.
• ThunderFrame: In my testing, I had to check that CopyMemory wasn't going to copy past the end of the buffer, or Excel would crash.
• Mathieu Guindon: Related: a lightning-fast StringBuilder - how does it compare?
• ThunderFrame: @Mat'sMug I forgot about that question. Yes, this function is faster than the StringBuilder, but it's only 80 times faster for 1000 repeats.

Answer 1

FWIW, this might be a rare case where the Mid statement is apropos here. This doesn't require any API. This should yield similar performance characteristics since we only allocate the buffer once just as we do with the API version.

Public Function Replicate(RepeatString As String, NumOfTimes As Long)
 Dim s As String
 Dim c As Long
 Dim l As Long
 Dim i As Long
 l = Len(RepeatString)
 c = l * NumOfTimes
 s = Space$(c)
 For i = 1 To c Step l
 Mid(s, i, l) = RepeatString
 Next
 Replicate = s
End Function

• Toby Speight: You have presented an alternative solution, but haven't reviewed the code. Please edit it to explain your reasoning (how your solution works and how it improves upon the original) so that everyone can learn from your thought process.
• fionasdad: You are right about this being a case for Mid, but only if utilizing a special feature that is little known. Your version takes twice as long as OP's StringRepeat(). I don't have the Rep to add an answer here, so I will place my function in the next comment. Using Mid the way I have, my function is FASTER that OP's StringRepeat()...
• fionasdad: Public Function Repeat$(ByVal n&, Pattern$) Dim r& r = Len(Pattern) If n < 1 Then Exit Function If r = 0 Then Exit Function If r = 1 Then Repeat = String$(n, Pattern): Exit Function Repeat = Space$(n * r) Mid$(Repeat, 1) = Pattern: If n > 1 Then Mid$(Repeat, r + 1) = Repeat End Function

Answer 2

I see you want to use pointers and so maybe you should use pointer safe methods -

Private Declare PtrSafe Sub CopyMemory Lib "kernel32.dll" Alias "RtlMoveMemory" (ByVal Destination As LongPtr, ByVal source As LongPtr, ByVal Length As LongPtr) 

All the Long should also be LongPtr.

That being said StrPtr is undocumented and the user will get a type mismatch (at least on 64bit) unless bufferPointer as LongPtr is declared.

Same goes for Dim expressionPointer As LongPtr.

Otherwise, I think this is pretty clever, kudos.