VBA Express : Excel - Methods of repeating a string in VBA

Excel

Methods of repeating a string in VBA - FOR loop not required

Ease of Use

Easy

Version tested with

2000, 2003

Submitted by:

brettdj

Description:

A repeated string ..... duh

Discussion:

For loops have their place but tend to be overused in Excel. If you want to duplicate a string a number of times there are a couple of more efficient methods to employ.

Code:

instructions for use

			Option Explicit 
Sub RepeatString() 
    Dim Mystr As String, OutPutStr1 As String, OutPutStr2 As String, OutPutStr3 As String 
    Dim i As Long, v As Long 
    Mystr = " KB article XX " 
    i = 10 
     
     'Method 1 - Worksheet function REPT
    OutPutStr1 = Application.WorksheetFunction.Rept(Mystr, i) 
    Debug.Print "REPT Function " & vbNewLine & OutPutStr1 
     
     'Method 2 - Space and Replace. Useful in VB if REPT isn't available
    OutPutStr2 = Replace(Space(i), " ", Mystr) 
    Debug.Print "Space and Replace " & vbNewLine & OutPutStr2 
     
     'Method 3 - The ugly FOR loop
    For v = 1 To i 
        OutPutStr3 = OutPutStr3 & Mystr 
    Next v 
    Debug.Print "The ugly FOR loop " & vbNewLine & OutPutStr3 
     
End Sub

How to use:

Copy the code above.
Open your workbook.
Hit Alt+F11 to open the Visual Basic Editor (VBE).
Paste the first section of code into the ThisWorkbook module
From the menu, choose Insert-Module.
Paste the second section of code into the code window at right.
Close the VBE, and save the file if desired.

Test the code:

Run the macro, RepeatString
The code dumps the three output strings to the Immediate Window of the VBE

Sample File:

No Attachment

Approved by mdmackillop

This entry has been viewed 289 times.