3

I could have sworn I have stripped CRLF in the past but not sure why the following isn't working:

myString = "ABC" & vbCrLf & "DEF"
str1 = Replace(myString, vbLf, "")
str2 = Replace(str1, vbCrLf, "")
str3 = Replace(str2, vbNewLine, "") 
MsgBox str3

The code above doesn't work the result is:

ABC
DEF

myString = "ABC" & vbCrLf & "DEF"
str1 = Replace(myString, Chr(13), "")
str2 = Replace(str1, Chr(10), "")
MsgBox str2

The code above does work the result is:

ABCDEF

Solution: Thanks @ Mat for the answer (The problem on the first code was the order I was trying to remove the items) VbCrLf & VbNewLine is the same and trying to remove the combo vbCr+VbLf after removing VbLf won't work

Mickey D
  • 347
  • 2
  • 12

1 Answers1

5

The premise is flawed:

myString = "ABC" & vbCrLf & "DEF"

The string is made of "ABC", vbCrLf, and "DEF".

vbCrLf is vbCr and vbLf, which on any Windows box is vbNewLine.

When you do:

str1 = Replace(myString, vbLf, "")

You replace vbLf and leave the vbCr character in place.

str2 = Replace(str1, vbCrLf, "")

Then you replace vbCrLf but vbLf is already gone so vbCrLf isn't in the string.

str3 = Replace(str2, vbNewLine, "")

Then you replace vbNewLine which is basically doing the exact same thing as the previous instruction, and the result is a string that's been stripped of vbLf but still contains vbCr.

This code works as expected:

Sub Test()
    Dim foo As String
    foo = "foo" & vbCrLf & "bar"
    Debug.Print foo
    foo = Replace(foo, vbNewLine, vbNullString)
    Debug.Print foo
End Sub

As does this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbNewLine, vbNullString)
    Debug.Print foo
End Sub

Or this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbCrLf, vbNullString)
    Debug.Print foo
End Sub

Or even this:

Sub Test()
    Dim foo As String
    foo = "foo" & vbNewLine & "bar"
    Debug.Print foo
    foo = Replace(foo, vbCr, vbNullString)
    foo = Replace(foo, vbLf, vbNullString)
    Debug.Print foo
End Sub

Your second snippet works as intended, because you do remove both vbCr (Chr(13)) and vbLf (Chr(10)) characters. Simple as that.

Mathieu Guindon
  • 69,817
  • 8
  • 107
  • 235
  • Wow it was the order. Thanks very much for that clarification. I never thought about it that way (this time) but I did use it in the past and must have thought about it more logically. Thanks again – Mickey D May 09 '17 at 18:23
  • 1
    @MickeyD well... it *wasn't* the order. Your code removed `vbLf` and left `vbCr` in the string; every further replacement involving `vbCrLf` and `vbNewLine` was no-op. Rule of thumb if you're on Windows you don't need to care about `vbCr`, `vbLf` or `vbCrLf` - just use `vbNewLine` and everything will just work. If the first replacement you did was for `vbNewLine`, then any further `vbCrLf` or `vbCr` or `vbLf` replacement would be no-op as well. Also note `vbNullString` instead of `""`; the latter is allocated a whole 2 bytes, the former is a 0-byte null pointer. – Mathieu Guindon May 09 '17 at 18:30
  • @Matt thanks but I have to politely clearly explain the way I meant to explain it. Think about it this way. If I would have used str2 = Replace(myString, vbCrLf, "") first then it would have worked. I just tried it by itself and it worked fine. I didn't need to remove them individually: VbCr or VbLf . There would be no chr(10) or chr(13) to remove in any following steps so if I would try to remove them afterwords, my result will still be correct but it would have wasted 2 extra lines of code. Do you see what I'm saying? – Mickey D May 10 '17 at 00:02
  • Correct indeed - I was just saying that *just* reordering the instructions would have left you with one working instruction and then a bunch of redundant no-op ones ;-) – Mathieu Guindon May 10 '17 at 00:10
  • thanks again for clarifying it. 50/1 odds I will do the same mistake 10 years from now and have something to look back at. – Mickey D May 10 '17 at 00:50