String Repeat function in VBA

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 = Sp

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

StackExchange Code Review Q#159080, answer score: 7