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:

    =REPT(“0?,7-LEN(A1))

  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:

    =RIGHT(REPT(“0?,7)&A1,7)

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

    …returns:

    HHH

Leave a Reply

Your email address will not be published. Required fields are marked *

To create code blocks or other preformatted text, indent by four spaces:

    This will be displayed in a monospaced font. The first four 
    spaces will be stripped off, but all other whitespace
    will be preserved.
    
    Markdown is turned off in code blocks:
     [This is not a link](http://example.com)

To create not a block, but an inline code span, use backticks:

Here is some inline `code`.

For more help see http://daringfireball.net/projects/markdown/syntax