The String Function
String() is a VBA function that repeats a specified string a specified number of times. It’s a very specific formula. It doesn’t do anything the good old ampersand can’t do, but sure can clean up your code if you have to repeat things a lot.
If, for instance, you’re creating a fixed-width text file in VBA, you will probably be adding a lot of spaces for padding. You could fill a string variable like this
sText = sText & Range(”A1″).Value
For i = 30 to Len(Range(”A1″).Value) Step -1
sText = sText & " "
Next i
to fill out the value to a length of 30. The String function can eliminate that loop.
sText = sText & Range(”A1″).Value & String(30-Len(Range(”A1″).Value), " ")
The first argument is the number of repetitions. The second argument is the string to be repeated. In this case, the number of repetitions is 30 less the length of A1 and the string to be repeated is a space.
REPT() is the Excel worksheet corollary to this function. I use it often to pad out item codes. For example, if the codes must be 7-characters long, I use:
=REPT(”0″,7-LEN(A1))
re: sText = sText & Range(”A1″).Value & String(30-Len(Range(”A1″).Value), ” “)
You can eliminate a math operation and a reference resolution:
sText = Left(sText & Range(”A1″).Value & String(30, ” “), 30)
or, in XL:
=RIGHT(REPT(”0″,7)&A1,7)
Hi,
very interesting - but why don’t you use the Space$()-Function?
Have a nice day, Ben.
Just a clarification — the function doesn’t repeat a STRING, just a single character. If you give it a multi-character string as the second parameter, it just repeats the first character of the string.
For example:
STRING(3, “Hello”)
…returns:
HHH
Good point Randy.