Adding a new line in VBA
In VBA, a newline can be added by using the following:
- vbNewLine constant
- vbCrLf constant
- vbCr constant
- vbLf constant
- char(10) character
Let us see examples of these in the section below.
An example of vbNewLine constant for adding a new line
We have two string-type variables and assign both texts after declaration.
In a MsgBox, we will concatenate both strings and add a new line using the vbNewLine constant.
See the code and output below:
The code:
Sub newline_ex() Dim Str1 As String, Str As String Str1 = "This is Line 1" Str2 = "This is Line 2" MsgBox Str1 & vbNewLine & Str2 End Sub
Output:
Adding a new line in the Excel cell example
Writing in Excel cells is also simple.
You just need to ensure the cell is enabled “Wrap Text” for which you want to insert a new line.
To demonstrate that, we will write three-line text in the H8 cell as follows:
Code:
Sub vbNewLine_ex()
'Adding vbNewLine in a cell
Range("H8") = "Line Number 1" & vbNewLine & "Line Number 2" & vbNewLine & "Line Number 3"
End Sub
Output:
Using vbCrLf constant example
This is like pressing the Enter key.
The code:
Sub newline_ex() Dim Str1 As String, Str As String Str1 = "This is Line 1" Str2 = "This is Line 2" 'vbCrLf for adding new line MsgBox Str1 & vbCrLf & Str2 End Sub
Output:
Using vbCr constant example
You may also use the vbCr constant to add a line break between two or more sentences.
See an example below:
Code:
Sub vbCr_ex() 'vbCr for adding new line MsgBox "This is an " & vbCr & "Example of" & vbCr & "cbCr constant!" End Sub
Output:
vbLf example
The vbLf meant to go to the next line
It represents a linefeed character for print and display functions.
Example code
Sub vbLf_ex() 'vbLf for adding new line MsgBox "This is an " & vbLf & "Example of" & vbLf & "vbLf constant!" End Sub
Output:
Using Chr(10) ASCII for newline
In VBA, the Chr(10) returns a linefeed character. You may also use it to add new lines in strings.
