How to use String function in VBA?

In VBA (Visual Basic for Applications), the String function is used to create a string that consists of repeated characters. The syntax for the String function is as follows:

String(Number, Character)

Where:

`Number` is the number of times the `Character` will be repeated.
`Character` can be a numeric code representing the character (using the ASCII code) or the character itself in quotes.

Here’s how you can use the String function in VBA:

Sub UseStringFunction()
    Dim repeatedStr As String
    
    ' Repeat the letter "A" 10 times.
    repeatedStr = String(10, "A")
    MsgBox repeatedStr   ' Output will be: AAAAAAAAAA
    
    ' Repeat the character with ASCII code 42 (*) 5 times.
    repeatedStr = String(5, 42)
    MsgBox repeatedStr   ' Output will be: *****
End Sub

Open Microsoft Excel or a similar application that supports VBA programming (Access, Word, etc.).
Press `Alt + F11` to open the VBA editor.
Insert a new module if there isn’t one already available.
Write a subroutine to demonstrate the usage of the String function:
Run the subroutine by pressing `F5` or from the Run menu.

The `String` function is particularly useful when you need to create padding for formatting purposes, such as creating fixed-width strings, or when you want to initialize a string variable with a specific character repeated several times.

Here is another way to use the String function with variables:

Sub UseStringFunctionWithVariables()
    Dim characterToRepeat As String
    Dim numberOfRepetitions As Integer
    Dim resultString As String
    
    characterToRepeat = "%"    ' Character to repeat
    numberOfRepetitions = 20   ' Number of times to repeat
    
    ' Create string using the String function
    resultString = String(numberOfRepetitions, characterToRepeat)
    
    ' Display the result
    MsgBox "The result string is: " & resultString
End Sub

Replace `%` with the character you wish to repeat, and `20` with the desired number of repetitions to customize the example.

facebooklinkedin