We can use the following constants in Excel VBA to add a line break or carriage return to a text string :
- vbNewLine
- vbCrLf
- vbLf
- vbCr
Example #1: Use vbNewLine Constant to Add a New Line to a Text String
We can use the vbNewLine constant to add a new line in a string in Excel VBA. In this example, we will use the constant to put the second and third strings to new lines in the Immediate window.
We use the following steps:
- Open a new worksheet in a workbook.
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a new module in the Visual Basic Editor.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub newLine() Dim firstString As String Dim secondString As String Dim thirdString As String firstString = "VLOOKUP Function" secondString = "INDEX Function" thirdString = "MATCH Function" Debug.Print firstString & vbNewLine & secondString & vbNewLine & thirdString End Sub |
- Place the cursor anywhere in the sub-procedure and press F5 to run the code.
The outcome of the code is displayed in the Immediate window:
Note: If you do not see the Immediate window, activate it by pressing Ctrl + G.
Example #2: Use vbCrLf Constant to Add a New Line to a Text String
We can use the vbCrLf constant to add a new line in a string in Excel VBA. The vbCrLf stands for Carriage Return and Line Feed. Carriage Return (Cr) moves the cursor to the beginning of the line, and Line Feed (Lf) pushes the cursor down to the following line.
In this example, we will use the constant to put the second and third strings to new lines and display the result in cell A1 of the active worksheet.
We use the following steps:
- Open a new worksheet in a workbook.
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a new module in the Visual Basic Editor.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub usevbCrLf() Dim firstString As String Dim secondString As String Dim thirdString As String firstString = "VLOOKUP Function" secondString = "INDEX Function" thirdString = "MATCH Function" Range("A1") = firstString & vbCrLf & secondString & vbCrLf & thirdString End Sub |
- Place the cursor anywhere in the sub-procedure and press F5 to run the code.
- Press Alt + F11 to switch to the active worksheet.
The outcome of the code is shown in cell A1:
Note that you may need to adjust the row height and column width of cell A1 to see all the text strings.
Example #3: Use vbLf Constant to Add a New Line to a Text String
We can use the vbLf constant to add a new line in a string in Excel VBA. The vbLf stands for Line Feed character. When the constant is used within strings, it returns line feed characters that add new lines to the second and subsequent strings.
In this example, we will use the constant to put the second and third strings to new lines and display the result in a message box.
We use the following steps:
- Open a new worksheet in a workbook.
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a new module in the Visual Basic Editor.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub usevbLf() Dim firstString As String Dim secondString As String Dim thirdString As String firstString = "VLOOKUP Function" secondString = "INDEX Function" thirdString = "MATCH Function" MsgBox firstString & vbCrLf & secondString & vbCrLf & thirdString End Sub |
- Place the cursor anywhere in the sub-procedure and press F5 to run the code.
The code opens the active worksheet and displays the three text strings in a message box:
Example #4: Use vbCr Constant to Add a New Line to a Text String
We can use the vbCr constant to add a new line in a string in Excel VBA. The vbCr stands for Carriage Return character. When the constant is used within strings, it returns line feed characters that add new lines to the second and subsequent text strings. Note that this constant works well in message box displays, but if you want the results displayed in a cell, you will need to apply wrap text to the cell to see the strings in different lines.
In this example, we will use the constant to put the second and third strings to new lines and display the result in a cell.
We use the following steps:
- Open a new worksheet in a workbook.
- Press Alt + F11 to open the Visual Basic Editor.
- Insert a new module in the Visual Basic Editor.
- Type the following sub-procedure in the module.
|
1 2 3 4 5 6 7 8 9 10 11 |
Sub usevbCr() Dim firstString As String Dim secondString As String Dim thirdString As String firstString = "VLOOKUP Function" secondString = "INDEX Function" thirdString = "MATCH Function" Range("A1") = firstString & vbCr & secondString & vbCr & thirdString End Sub |
- Place the cursor anywhere in the sub-procedure and press F5 to run the code.
- Press Alt + F11 to switch to the active worksheet.
The results appear in a single line.
For the three strings to be displayed in separate lines, we must apply wrap text to the cell in the following steps.
- Select cell A1 and press Ctrl + 1 to open the Format Cells dialog box.
- In the Format Cells dialog box that appears, open the Alignment tab, select the Wrap text option in the Text control section and click OK.
- Adjust the row height and column width of cell A1 so that all three strings are displayed in separate lines, as shown below:
Conclusion
This tutorial looked at the four VBA constants we can use to add new lines to a text string. The constants are vbNewLine, vbCrLf, vbLf, and vbCr. When you use the vbCr to add new lines in a string in a cell, you must apply wrap text to the cell to see all the text strings displayed. We hope you found the tutorial helpful.