Don’t repeat vbCrLf (line feed) in String function (VBA)
In this article, I will show you what happens when you repeat vbCrLF (line feed) in the String function.
String(times, character)
The String function returns a string of characters repeated a specified number of times.
However, if there are multiple characters, only the first string will be repeated.
This time, we will use the String function in the following way.
String(count, vbCrLf)
As it turns out, this function does not return a string that repeats vbCrLf.
Do not use this usage.
To begin with, I will explain what vbCrLf is and what happens when vbCrLf is repeated with the String function.
What does vbCrLf look like?
vbCrLf is a constant for line feed.
MsgBox “1 line item" & vbCrLf & “2 line items"
If you write it like this, you will get a message with a new line between the first and second lines.
Normally, we are only aware of what we use for line breaks, but this vbCrLf is a combination of two constants.
It is vbCr and vbLf. I’ve also written some simple code for checking.
As you can see, vbCrLf is a combination of two constants, vbCr and vbLf.
The role of these two constants will be discussed later.
What happens when you repeat vbCrLf in the String function?
The String function repeats only the first string if there are multiple characters.
Now let’s see what happens when we repeat vbCrLf. Here is the code for confirmation.
Two If statements are used to determine if the return value of the String function contains vbCr and vbLf.
If you check the result in the Immediate window, you will see that vbCr is included, but vbLf is not.
In other words, the String function repeats only the first character of vbCrLf, vbCr.
This is not the intended behavior of repeating vbCrLf, so let’s not use it this way.
Difference between vbCr and vbLf
| Description. | vbCrLf | vbCr | vbLf |
| Overview | Linefeed + carriage return | Carriage return (cursor to the top) | Linefeed (Next line) |
| character code (e.g. JIS, Unicode, etc.) | chr(13)+chr(10) | chr(13) | chr(10) |
| newline character (either or both of CR and LF depending on system) | Common newline codes in Windows | Common newline codes for MacOS | Common newline codes in Linux |
| Example. | If you enter a new line in Windows Notepad. | – | Line breaks in Excel cells. Line feed in a Linux text editor. |
MacOS uses vbCr, Linux uses vbLf, and Windows uses a combination of both, vbCrLf, for line breaks.
Because of this difference, when using MsgBox on Windows, use vbCrLf to break the string.
Incidentally, I checked two cases to see what would happen if I only used vbCr.
- MsgBox: Line feed is performed.
- Line feed in cell: No line feed
MsgBox seems to create a new line, but if you use other Windows applications, it may behave unexpectedly.
To avoid unnecessary debugging, use vbCrLf.
If you want to make multiple line breaks in vbCrLf, you can create your own function.
Now, we have explained that vbCrLf cannot be repeated with the String function. (If you want to repeat vbCr, vbLf, there is no problem with the String function.
Nevertheless, concatenating vbCrLF as a string many times is laborious and unsuitable for readability and maintenance.
Why don’t you create a simple home-made function like the following?
Function fncCrLf(ByVal repeatNum As Long) As String: fncCrLf = ""
Dim count As Long
For count = 1 To repeatNum
fncCrLf = fncCrLf & vbCrLf
Next
End Function