Excel - Repeat Character in VBA

Asked By Bra on 12-Nov-08 05:22 PM
Thanks for taking the time to read my question

I would like add a space to my string x times, where x = an integer.

How can I do that with out using a loop statement?

eg: "there are 10 spaces between here" & space * x & "here."



James_Thomlinso replied on 12-Nov-08 05:31 PM
There is a function called space that you could use

dim str as string
str = "this" & space(10) & "that"

Jim Thomlinson
Mike replied on 12-Nov-08 05:32 PM

this adds 10 spaces to the end without a loop and with the use of
left/mid/right you could put the spaces anywhere

x = 10
mystring = "Mike H"
mystring = mystring + WorksheetFunction.Rept(" ", x)

Dave Peterson replied on 12-Nov-08 06:26 PM
Just to add to Jim's reply...

There's another function that can be used with other non-space characters:

dim str as string
str = String(12, "x")

(It can also be used with space characters <bg>.)


Dave Peterson
James_Thomlinso replied on 12-Nov-08 06:48 PM
Yeah but I loved the ironly that Brad had the function correct and that the
only error was *x instead of (x).

Jim Thomlinson
Rick Rothstein replied on 12-Nov-08 06:53 PM
And just to add onto Dave reply, the String function only repeats single
characters; however, if you wanted to repeat multiple characters, all you
would have to do is couple the String or Space function with the Replace
function. For example, to produce a text string of, say, 100 "XO" character
pairs in a row, just do this...

HundredXOs = Replace(Space(100), " ", "XO")

or this would work also...

HundredXOs = Replace(String(100, "Z"), "Z", "XO")

where you can use any character you want to in place of the "Z".

Rick (MVP - Excel)
Bra replied on 13-Nov-08 09:19 AM
Thanks to all for your posts!!

It is quite ironic that I was so close but yet had no idea.

Hope everyone has a great day today,