Results 1 to 7 of 7
Like Tree1Likes
  • 1 Post By BradA

Differences between the constants vbCr, vbLf & vbCrLf

This is a discussion on Differences between the constants vbCr, vbLf & vbCrLf within the Excel Questions forums, part of the Question Forums category; Hello I tried the constants vbCr, vbLf & vbCrLf in a MsgBox: MsgBox "Hi" & vbCr & "There!" MsgBox "Hi" ...

  1. #1
    Board Regular
    Join Date
    Mar 2010
    Posts
    81

    Default Differences between the constants vbCr, vbLf & vbCrLf

    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!

  2. #2
    VoG
    VoG is offline
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002
    Location
    127.0.0.1
    Posts
    63,652

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    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.
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3
    Board Regular
    Join Date
    Mar 2010
    Posts
    81

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    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.

  4. #4
    New Member
    Join Date
    Mar 2011
    Posts
    1

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    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

  5. #5
    MrExcel MVP
    Moderator
    xenou's Avatar
    Join Date
    Mar 2007
    Location
    Clev. OH, USA
    Posts
    14,787

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    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).

    ξ

    Using: Office 2007/Win7 (work) Office 2010/Win7 (home)

    You are rich in proportion to the number of things you can let alone.
    -- Henry David Thoreau

  6. #6
    Board Regular
    Join Date
    Sep 2010
    Location
    Scottsdale, AZ
    Posts
    75

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    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
    DocAElstein likes this.

  7. #7
    Banned user
    Join Date
    May 2014
    Location
    Germany
    Posts
    1,336

    Default Re: Differences between the constants vbCr, vbLf & vbCrLf

    Quote Originally Posted by BradA View Post
    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:
    '    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 )
    Sub PutInAvbLfInClipboadText() '    "Replcace vbCr with vbCr & vbLf "
    'Get Current Text from Clipboard
    Dim objDat As DataObject
    Set objDat = New DataObject 'Set to a new Instance ( Blue Print ) of dataobject
    'Dim obj As Object
    'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")
    objDat.GetFromClipboard 'All that is in the Clipboard goes in this Data Object instance of the Class.
    Let TxtOut = objDat.GetText() '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 )
    Dim originalClipboardText As String: Let originalClipboardText = TxtOut
    Dim TextWithExtravbLF As String
    Let TextWithExtravbLF = Replace(TxtOut, vbCr, vbCr & vbLf, 1, -1) '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        )
    'Dump in Clipboard: This second instance of Data Object used to put in Clipboard
    Dim objCliS As DataObject   '**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
    Set objCliS = New DataObject '**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.
    ' ( or instead of those two lines  Dim obj As New DataObject ).    or  next two lines are.....Late Binding equivalent'
    '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/
    'Set obj = GetObject("New:{1C3B4210-F441-11CE-B9EA-00AA006B1A69}")' http://excelmatters.com/2013/10/04/late-bound-msforms-dataobject/
    objCliS.SetText TextWithExtravbLF 'Make Data object's text equal to a copy of ORefiginalText
    objCliS.PutInClipboard 'Place current Data object into the Clipboard
    ' Get from clipboard. This a Another Object from class to be sure we have the data in the Clipboard
    MsgBox prompt:="You dumped in Clipboard originally  this " & vbCr & TxtOut & vbCr & "and if you try to get it, you should get" & vbCr & TextWithExtravbLF & ""
    ' End clean up.
    'TheEnd: ' ( Come here always, even on a unpredictable error )
    Set objDat = Nothing '   Good practice...   maybe....
    Set objCliS = Nothing '  .......   http://www.mrexcel.com/forum/excel-questions/361246-vbnullstring.html#post4414065
    End Sub
    _.....
    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

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

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com