Is there any build-in function in VBScript to repeat a string N-times?

Question

There is a function in VBScript String(number,character) returns a string that contains a repeating character of a specified length. E.g.:

String(5, "A")     ' output: "AAAAA"

Is there any function to repeat a string? E.g.:

RepeatString(5, "Ab")     ' output "AbAbAbAbAb"

In what context are you using the VBScript? There is nothing built in but you could declare a function of your own to do this. — apc, Oct 19 '16 at 12:35
@WiktorStribiżew, I think you should extract the portion of code that refers to VBScript and post it as an answer (with the link for reference) — Victor Moraes, Oct 19 '16 at 13:12
@VictorMoraes: I would, but Alex was quicker. I think his answer is enough. I was looking for a dupe, and did not focus on answering in the first place. — Wiktor Stribiżew, Oct 19 '16 at 13:17
@WiktorStribiżew your link offers two approaches whilst Alex only has one, but yeah, maybe it's enough — Victor Moraes, Oct 19 '16 at 13:38

score 41 · Accepted Answer · answered Oct 19 '16 at 12:35

41

No, nothing built in. Instead:

n      = 5
str    = "Ab"
result = replace(space(n), " ", str)

answered Oct 19 '16 at 12:35

Alex K.

171,639
30
264
288

Ingenious! `Replace(Space(3)," ","ha")` – dakab Sep 15 '21 at 15:45

Excel Hero · Answer 2 · 2020-06-17T05:14:49.530

3

For code brevity, the accepted answer is good. But the following function is literally 10 times as fast. And it's twice as fast as RepeatString(). It uses a little-known feature of Mid where it fills the remainder of a string buffer with a repeating pattern in one go...

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

edited Jun 17 '20 at 05:14

answered May 27 '20 at 18:21

Excel Hero

14,253
4
33
40

haven't done VBA in a while, so haven't kept up with the syntax, so this is fairly cryptic – go2null Oct 25 '20 at 17:11
No new syntax here. In fact this function would work in 1980's BASIC. – Excel Hero Oct 25 '20 at 17:22
I haven't seen `String$(n, s)` before. What does it do? I thought it behaved similarly to `Space$(n * r)`, but I am not able to find it in the Microsoft VBA docs. – chaotic3quilibrium Jul 20 '21 at 14:57
https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/string-function – Excel Hero Jul 20 '21 at 20:35
VBScript does'nt have the mid$ statement – Antoni Gual Via Jan 26 '23 at 14:52

MC ND · Answer 3 · 2016-10-20T04:59:08.737

For a general simple solution

Function RepeatString( number, text )
    Redim buffer(number)
    RepeatString = Join( buffer, text )
End Function

But if the text is short but the number of repetitions is high, this is a much faster solution

Function RepeatString( ByVal number, ByVal text )
    RepeatString=""
    While (number > 0)
        If number And 1 Then 
            RepeatString = RepeatString & text
        End If 
        number = number \ 2 
        If number > 0 Then
            text = text & text 
        End If 
    Wend
End Function

score 0 · Answer 4 · answered Jan 04 '23 at 11:16

0

Here's another way to accomplish this using built-in excel formula REPT()

Function RepeatMyStringTimes()
    Const myString As String = "Some String"
    Const Times As Byte = 7
    Debug.Print Excel.WorksheetFunction.Rept("S", Times)
End Function

answered Jan 04 '23 at 11:16

Przemo Szew

1
1

Gio · Answer 5 · 2022-10-07T21:48:26.300

-1

FYI I needed to have a 10-character string repeated more then 25 million times and the function Repeat$(ByVal n&, s$) took half a second to perform it, it saved my day! :)

edited Oct 07 '22 at 21:48

answered Oct 07 '22 at 21:46

Gio

1
1

hideo · Answer 6 · 2022-12-12T08:04:48.340

-1

We can create a simple custom function like this

Function RepeatString(Byval AString As String, byval Times As Long)
    If AString = vbNullString Or Times < 0 Then RepeatString = CVErr(xlErrNA): Exit Function
    Dim i As Long
    Dim result As String
    For i = 1 To Times
        result = result & AString
    Next
    RepeatString = result
End Function

Or use String function to return repeating character strings of the length specified.

Dim MyString
MyString = String(5, "*")    ' Returns "*****".
MyString = String(5, 42)    ' Returns "*****".
MyString = String(10, "ABC")    ' Returns "AAAAAAAAAA".

edited Dec 12 '22 at 08:04

answered Dec 12 '22 at 07:38

hideo

37
4

1

You shouldn't copy/paste content from the internet without attribution. That is plagiarism. – Geert Bellekens Dec 12 '22 at 08:15

Is there any build-in function in VBScript to repeat a string N-times?

6 Answers6