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.

Posted in Uncategorized

5 thoughts on “The String Function

  1. 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:


  2. 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:


  3. 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”)



Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.