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.
Matthew Cobbs:
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))
2 December 2004, 11:36 amJ.E. McGimpsey:
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)
2 December 2004, 8:19 pmBen:
Hi,
very interesting - but why don’t you use the Space$()-Function?
Have a nice day, Ben.
3 December 2004, 10:34 amRandy Harmelink:
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
27 April 2006, 11:03 pmDick Kusleika:
Good point Randy.
30 April 2006, 8:02 pm