Why does Application.Clean() remove printable characters?

Question

Bounty Question:

While there is an answer which provides a reasonable explanation, I want to determine if the truncation of the string occurs because Clean() is a WorksheetFunction and the reasoning behind that issue. Do WorksheetFunctions when called by VBA pass the string into a "cell" so to speak which truncates at the null Chr(0)?

Original Question:

Why does TestIt's Application.Clean method remove all characters after Chr(0) even though they are printable? TestIt2 correctly returns 8 characters after doing a replace on null Chr(0).

Edit:
Worth noting is that some versions of Excel will require you to write Application.WorksheetFunction.Clean()
to test this error.

Code

Sub TestIt()
    Dim TryIt As String

    TryIt = "Test" & Chr(0) & "asdf"
    MsgBox Len(TryIt) 'Prints 9
    TryIt = Application.Clean(TryIt)
    MsgBox Len(TryIt) 'Prints 4

End Sub

and

Sub TestIt2()
    Dim TryIt As String

    TryIt = "Test" & Chr(0) & "asdf"
    MsgBox Len(TryIt) 'Prints 9
    TryIt = Replace(TryIt, Chr(0), "")
    MsgBox Len(TryIt) 'Prints 8

End Sub

Answer 1

@Gserg is right in the comments above

Visual Basic cannot handle strings terminated by a NULL character. In fact all DLLs terminate strings that way. Chr(0) is treated as the terminating character for strings.

Here is the best way to check it.

Sub TestIt()
    Dim TryIt As String

    TryIt = "Test" & Chr(0) & "asdf"

    Debug.Print Len(TryIt)
    Debug.Print TryIt

    ThisWorkbook.Sheets(1).Range("A1").Value = TryIt
End Sub

This will write just "Test" in cell A1

The best way is to handle it like you are doing it. Use .Replace. The other alternate (longer) way is to use INSTR.

Answer 2

The answer to this appears to be very simple: removing all characters following chr(0) is a bug.

Clean is designed to JUST remove chr(0) as well as other nonprinting characters, as indicated in the VBA language reference:

The Clean function was designed to remove the first 32 nonprinting characters in the 7-bit ASCII code (values 0 through 31) from text.

Clean works as expected for all of the other mentioned chr() values EXCEPT chr(0) after which it clearly removes all following characters.

If you must work with the null value, some kind of conversion will be necessary. You can get the expected result by doing something like TryIt = Replace(TryIt, Chr(0), Chr(1)) after which the result works as expected (and as it does for all of the other 31 chr() codes that Clean was designed to remove):

Sub TestIt3()

Dim TryIt As String
Dim iCounter As Integer

For iCounter = 0 To 31
    TryIt = "Test" & Chr(iCounter) & "asdf"
    TryIt = Replace(TryIt, Chr(0), Chr(1))
    Debug.Print "chr(" & iCounter & ")" & "  " & Len(TryIt)
    TryIt = Application.Clean(TryIt)
    Debug.Print "cleaned - chr(" & iCounter & ")" & "  " & Len(TryIt)
Next iCounter

End Sub