Go Back  Xtreme Visual Basic Talk > Legacy Visual Basic (VB 4/5/6) > VBA / Office Integration > Excel > Excel:vbCR, vbCrLf, Chr(13)=Chr$(13), Chr(10)=Chr$(10)

Thread Tools Display Modes
Old 11-11-2008, 11:32 AM
kcrichar kcrichar is offline
Join Date: Nov 2008
Location: Redwood City, CA
Posts: 1
Default Excel:vbCR, vbCrLf, Chr(13)=Chr$(13), Chr(10)=Chr$(10)

I thought I'd contribute to the masses since I run accross this from time to time. I needed to figure out what was happening within Excel and between Excel and other apps regarding carriage returns and line feeds.

Excel recognizes all variations of Chr(10), Chr(13), vbCr, vbLf, vbCrLf, and vbNewLine. How it displays them in a spreadsheet varies. For instance; a Chr(13) shows up as a square box in both the cell value and the filtered Data list but it doesn't act like a carriage return. It just acts like a nonprintable character. Chr(10) on the other hand doesn't show up as a square box in the cell view but it does show up as a square box in the filtered Data list. It does, however, behave like you'd expect a cariage return to behave.

My results tell me that in Excel:
Chr(13) = Chr$(13) = vbCr
If you need to manipulate these, say remove and replace the carriage return with something else you only need to search for either of the 3 and you will find all. That is if you search for Chr(13) and replace it with some other string you will also find and replace Chr$(13) and vbCr.

The same is true for:
Chr(10) = Chr$(10) = vbLf

vbNewLine = vbCrLf = Chr(13)Chr(10) = Chr$(13)Chr$(10) and so on.
Thus if you search to replace Chr(10) with "nothing" where vbNewLine was used the results are: Chr(13) because the use of the const vbNewLine in actuality is the combination of Chr(13) concatenated with Chr(10).

Only the equivelent uses of Chr(10) will result in a cariage return behavior in Excel cell values.

I ran the following macro so I could see what was happening.

Sub TestCr()
Range("A1") = "VBA Code"
Range("B1") = "VBA Descrption"
Range("C1") = "VBA Code Results" 'put "=code("A#")" for each line to see the ASCI binary value.
Range("D1") = "Length" 'put "=Len("A#")" for each line to see how many characters the results are.

Range("A2") = vbCr
Range("A3") = vbLf
Range("A4") = vbCrLf
Range("A5") = vbNewLine
Range("A6") = Chr(13)
Range("A7") = Chr$(13)
Range("A8") = Chr(10)
Range("A9") = Chr$(10)

Range("B2") = "vbCr"
Range("B3") = "vbLf"
Range("B4") = "vbCrLf"
Range("B5") = "vbNewLine"
Range("B6") = "Chr(13)"
Range("B7") = "Chr$(13)"
Range("B8") = "Chr(10)"
Range("B9") = "Chr$(10)"

'What VBA code matches with Chr(13)
Range("E2") = Replace(Range("A2").Value, Chr(13), "Chr(13)")
Range("E3") = Replace(Range("A3").Value, Chr(13), "Chr(13)")
Range("E4") = Replace(Range("A4").Value, Chr(13), "Chr(13)")
Range("E5") = Replace(Range("A5").Value, Chr(13), "Chr(13)")
Range("E6") = Replace(Range("A6").Value, Chr(13), "Chr(13)")
Range("E7") = Replace(Range("A7").Value, Chr(13), "Chr(13)")
Range("E8") = Replace(Range("A8").Value, Chr(13), "Chr(13)")
Range("E9") = Replace(Range("A9").Value, Chr(13), "Chr(13)")

'What VBA code matches with Chr$(13)
Range("F2") = Replace(Range("A2").Value, Chr$(13), "Chr$(13)")
Range("F3") = Replace(Range("A3").Value, Chr$(13), "Chr$(13)")
Range("F4") = Replace(Range("A4").Value, Chr$(13), "Chr$(13)")
Range("F5") = Replace(Range("A5").Value, Chr$(13), "Chr$(13)")
Range("F6") = Replace(Range("A6").Value, Chr$(13), "Chr$(13)")
Range("F7") = Replace(Range("A7").Value, Chr$(13), "Chr$(13)")
Range("F8") = Replace(Range("A8").Value, Chr$(13), "Chr$(13)")
Range("F9") = Replace(Range("A9").Value, Chr$(13), "Chr$(13)")

Range("G2") = Replace(Range("A2").Value, Chr(10), "Chr(10)")
Range("G3") = Replace(Range("A3").Value, Chr(10), "Chr(10)")
Range("G4") = Replace(Range("A4").Value, Chr(10), "Chr(10)")
Range("G5") = Replace(Range("A5").Value, Chr(10), "Chr(10)")
Range("G6") = Replace(Range("A6").Value, Chr(10), "Chr(10)")
Range("G7") = Replace(Range("A7").Value, Chr(10), "Chr(10)")
Range("G8") = Replace(Range("A8").Value, Chr(10), "Chr(10)")
Range("G9") = Replace(Range("A9").Value, Chr(10), "Chr(10)")

Range("H2") = Replace(Range("A2").Value, Chr$(10), "Chr$(10)")
Range("H3") = Replace(Range("A3").Value, Chr$(10), "Chr$(10)")
Range("H4") = Replace(Range("A4").Value, Chr$(10), "Chr$(10)")
Range("H5") = Replace(Range("A5").Value, Chr$(10), "Chr$(10)")
Range("H6") = Replace(Range("A6").Value, Chr$(10), "Chr$(10)")
Range("H7") = Replace(Range("A7").Value, Chr$(10), "Chr$(10)")
Range("H8") = Replace(Range("A8").Value, Chr$(10), "Chr$(10)")
Range("H9") = Replace(Range("A9").Value, Chr$(10), "Chr$(10)")

I hope someone is able to benefit from this.
Attached Images
File Type: jpg excel stuff.jpg (79.8 KB, 5 views)
Reply With Quote
Old 11-11-2008, 12:20 PM
shg shg is offline
Junior Contributor

* Expert *
Join Date: May 2008
Posts: 297

Thanks for posting.

The functions like Chr, Dir, Left, Right, Format, ... all return Variants with subtype String. They have equivalents Chr$, Dir$, Left$, ... that return Strings, and are faster if you are returning the result to a string variable. They are otherwise identical.
Entia non sunt multiplicanda sine necessitate.
Reply With Quote

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off

Forum Jump


Free Publications
The ASP.NET 2.0 Anthology
101 Essential Tips, Tricks & Hacks - Free 156 Page Preview. Learn the most practical features and best approaches for ASP.NET.
Programmers Heaven C# School Book -Free 338 Page eBook
The Programmers Heaven C# School book covers the .NET framework and the C# language.
Build Your Own ASP.NET 3.5 Web Site Using C# & VB, 3rd Edition - Free 219 Page Preview!
This comprehensive step-by-step guide will help get your database-driven ASP.NET web site up and running in no time..