excel vba repeat formula n times code example

Example: excel vba repeat string n times

'Fast VBA function to repeat a given string n times:

Function Repeat$(ByVal n&, s$)
    Dim r&
    r = Len(s)
    If n < 1 Then Exit Function
    If r = 0 Then Exit Function
    If r = 1 Then Repeat = String$(n, s): Exit Function
    Repeat = Space$(n * r)
    Mid$(Repeat, 1) = s: If n > 1 Then Mid$(Repeat, r + 1) = Repeat
End Function

'------------------------------------------------------------------------------

MsgBox Repeat(5, "Ab")					<--displays:  AbAbAbAbAb


'Here is a terse snippet for inline repeats, but the longer 
'Repeat() function above is 10 times faster:
MsgBox Replace(Space(5), " ", "Ab")		<--displays:  AbAbAbAbAb