Differences between the constants vbCr, vbLf & vbCrLf

antaeusguy

Board Regular
Joined
Mar 8, 2010
Messages
81
Hello

I tried the constants vbCr, vbLf & vbCrLf in a MsgBox:

MsgBox "Hi" & vbCr & "There!"
MsgBox "Hi" & vbLf & "There!"
MsgBox "Hi" & vbCrLf & "There!"

and it appears to have the same results (the word "There!" appears on a new paragraph).

However, there could be differences in their functions, between Carriage return and Linefeed.

I wish to know the differences between Carriage return and Linefeed (I tried to look in Excel Help but there were no explaination what is a Carriage return and a Linefeed, or a combination of both.

Thank you in advance for your help! :)
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Hi. In a MsgBox they all do the same thing as does

MsgBox "Hi" & vbNewLine & "There!"

but you may see a difference if you write to a cell using those VB characters.
 
Upvote 0
Hi VoG, thanks for your reply. :)

Indeed I tried vbCr and vbLf writing to a cell value and I could see the differences.

vbCr & vbLf both creates a new paragraph in MsgBox but when it comes to writting values to a cell, it seems to me vbCr didn't have any effect but vbLf acted like as if I used Alt+Enter to create a new paragraph within a cell.
 
Upvote 0
Hi,antaeusguy.

You can find the history of Carriage return and Linefeed here.

http://en.wikipedia.org/wiki/Teleprinter

Control characters


A typewriter or electromechanical printer can print characters on paper, and execute operations such as move the carriage back to the left margin of the same line (carriage return), advance to the same column of the next line (line feed), and so on. Commands to control non-printing operations were transmitted in exactly the same way as printable characters by sending control characters with defined functions (e.g., the line feed character forced the carriage to move to the same position on the next line) to teleprinters. In modern computing and communications a few control characters, such as carriage return and line feed, have retained their original functions (although they are often implemented in software rather than activating electromechanical mechanisms to move a physical printer carriage) but many others are no longer required and are used for other purposes.


Different Operation System use different character for a standard line feed:

Unix ---vbCr
Windows---vbCrLf
Mac---vbLf
 
Upvote 0
For I don't know what reason, major operating systems use different end of line characters. Windows uses CRLF (two characters) to mark the end of a line. But in my experience it generally still breaks to a new line on any LF. This is what you've noticed too.

For what it's worth:

Windows: CRLF
Unix and Unix like systems: LF
Mac: CR

In many cases, any of these end of line characters will "work" - especially with text file processing. GUI programs are more sensitive (working in cells, textboxes, word processing programs).

ξ
 
Upvote 0
I'm coming in to this discussion well after it had ended, but I wanted to make a note here that might someday be helpful to someone.

I was using vbCrLf to create line breaks within cells. It did exactly what I wanted it to. But one day we noticed that it was also including a space (Chr(13)), and this space caused a lot of probelms. I found, on another post on this forum, that changing to vbLf results in that extra space not being included, and getting rid of that space solved our problems (the problem became apparent when the space began showing up on screens and in print as a little square box everyplace that I had vbCrLf). Perhaps there's some reason to use vbCrLf instead of vbLf but in my case vbLf was certainly superior.

So if you're having similar problems I'd recommend trying vbLf. But the following code should clean up any of these spaces that already exist in your spreadsheet.

Code:
Sub CleanThisNonsenseUp()
Dim myst$, i%
myst = ActiveSheet.Range("b6").Value
For i = 1 To Len(myst)
     MsgBox Asc(Mid(myst, i, 1))
Next
End Sub
 
Upvote 0
I'm coming in to this discussion well after it had ended, but I wanted to make a note here that might someday be helpful to someone........
.......Me too!......

Hi, I also hit a problem. And often I hit this Thread on a search trying to solve it....so I thought i would share my solution ( or workaround )
_....................................

Just brief details: When Pasting into a Thread Editor ( a code to a HTML ( or php) Window in this case ) sometimes carriage Returns seem to “vanish” in the final Posted Post.
It has caught me out a few times when pasting codes.
_...................

Anyway, a workaround:....

My “Theory” to it ( probably as naively wrong as all of them.. ) ... back in the early days... a carriage return brought the Printer back to the start at the left
But then you needed also ...
A Line feed to go to the next line to be printed on.

Somewhere along the line the exact translation to what similarly happens in a modern computer world is a bit abstract. So a carriage return ( or Line feed alone ) might work. But maybe doing a carriage return and a Line feed would not do any harm and might occasionally help....
Ties up a bit may be with what amulee said in Post #4
https://en.wikipedia.org/wiki/Teleprinter


_....................................

So Try this: After copying to the clipboard from the code Window, run this code ( From Word or Excel )

Code:
[color=darkgreen]'    http://www.eileenslounge.com/viewtopic.php?f=26&t=22603&start=20#p176255        http://www.excelfox.com/forum/f13/bbcode-table-2077/#post9687   ( Manual Solution Alternative: http://www.excelfox.com/forum/f13/bbcode-table-2077/#post9645 )[/color]
[color=blue]Sub[/color] PutInAvbLfInClipboadText() [color=darkgreen]'    "Replcace vbCr with vbCr & vbLf "[/color]
[color=darkgreen]'Get Current Text from Clipboard[/color]
[color=blue]Dim[/color] objDat [color=blue]As[/color] DataObject
[color=blue]Set[/color] objDat = [color=blue]New[/color] DataObject [color=darkgreen]'Set to a new Instance ( Blue Print ) of dataobject[/color]
[color=darkgreen]'Dim obj As Object[/color]
[color=darkgreen]'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")[/color]
objDat.GetFromClipboard [color=darkgreen]'All that is in the Clipboard goes in this Data Object instance of the Class.[/color]
[color=blue]Let[/color] TxtOut = objDat.GetText() [color=darkgreen]'retrieve the text in this instance of the Class. ( In this case all I have in it is the text typically I think as it is coming from a Ctrl C Copy from the VB Editor )[/color]
[color=blue]Dim[/color] originalClipboardText [color=blue]As[/color] String: [color=blue]Let[/color] originalClipboardText = TxtOut
[color=blue]Dim[/color] TextWithExtravbLF [color=blue]As[/color] [color=blue]String[/color]
[color=blue]Let[/color] TextWithExtravbLF = Replace(TxtOut, vbCr, vbCr & vbLf, 1, -1) [color=darkgreen]'Replace  (   in TxtOut  ,   vbCr     ,     with vbCr & vbLf     ,  start looking at  AND/OR Only consider and FINALLY OUTPUT from character 1 ( so all in this case ),    -1 means look at and change all occurrances        )[/color]
[color=darkgreen]'Dump in Clipboard: This second instance of Data Object used to put in Clipboard[/color]
[color=blue]Dim[/color] objCliS [color=blue]As[/color] DataObject   [color=darkgreen]'**Early Binding.   This is for an Object from the class MS Forms. This will be a Data Object of what we "send" to the Clipboard. So I name it CLIpboardSend. But it is a DataObject. It has the Methods I need to send text to the Clipboard[/color]
[color=blue]Set[/color] objCliS = [color=blue]New[/color] DataObject [color=darkgreen]'**Must enable Forms Library: In VB Editor do this:  Tools -- References - scroll down to Microsoft Forms 2.0 Object Library -- put checkmark in.  Note if you cannot find it try   OR IF NOT THERE..you can add that manually: VBA Editor -- Tools -- References -- Browse -- and find FM20.DLL file under C:\WINDOWS\system32 and select it --> Open --> OK.[/color]
[color=darkgreen]' ( or instead of those two lines  Dim obj As New DataObject ).    or  next two lines are.....Late Binding equivalent'[/color]
[color=darkgreen]'Dim obj As Object'  Late Binding equivalent'   If you declare a variable as Object, you are late binding it.  http://excelmatters.com/2013/09/23/vba-references-and-early-binding-vs-late-binding/[/color]
[color=darkgreen]'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/[/color]
objCliS.SetText TextWithExtravbLF [color=darkgreen]'Make Data object's text equal to a copy of ORefiginalText[/color]
objCliS.PutInClipboard [color=darkgreen]'Place current Data object into the Clipboard[/color]
[color=darkgreen]' Get from clipboard. This a Another Object from class to be sure we have the data in the Clipboard[/color]
MsgBox prompt:="You dumped in Clipboard originally  this " & vbCr & TxtOut & vbCr & "and if you try to get it, you should get" & vbCr & TextWithExtravbLF & ""
[color=darkgreen]' End clean up.[/color]
[color=darkgreen]'TheEnd: ' ( Come here always, even on a unpredictable error )[/color]
[color=blue]Set[/color] objDat = [color=blue]Nothing[/color] [color=darkgreen]'   Good practice...   maybe....[/color]
[color=blue]Set[/color] objCliS = [color=blue]Nothing[/color] [color=darkgreen]'  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065[/color]
[color=blue]End[/color] [color=blue]Sub[/color]

_.....
That above code simply replaces all
vbCr ‘s
With a
vbCr & vbLf
( So replaces a carriage return with a carriage return and a Line feed )
I am not sure of exactly what is going on. Either I am giving an extra carriage return to be “eaten” by the Forum editor, but maybe that does not tie up with further editing remaining stable . More likely it wants to see a vbLF to interpret the thing correctly. Just an idea from a computer Novice.

But anyway the workaround seems to work. :)

Worth a try if you are experiencing similar problems. And or experiment with a vbCrLf etc. etc.
( and post back and share if you notice anything different :) )

Alan

One Final Note. This workaround does not always seem to be necessary. Different Forum editor, and changes from time to time to the software there of, can influence the results.....

Ref:
Eileen's Lounge • View topic - Word VBA Replace multiple Spaces in Text with BB Code String

P.s. i also have a “manual” solution ( Workaround 1) that seems to work but is a bit more tedious...
BBCode Table

_........................................................................................
 
Upvote 0

Forum statistics

Threads
1,213,532
Messages
6,114,177
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top