为什么Application.Clean（）可以删除可打印字符？ [英] Why does Application.Clean() remove printable characters?

问题描述

赏金问题：

虽然有一个提供合理解释的答案，如果截断字符串是因为 Clean（）是一个 WorksheetFunction 以及该问题背后的原因。在 VBA 调用时，执行 WorksheetFunctions 将字符串传递到单元格，以便在$ code> null Chr（0）？

原始问题： p>

为什么 TestIt 的 Application.Clean 方法删除全部即使它们是可打印的， Chr（0）之后的字符？ TestIt2 在 null Chr（0）上执行替换后正确返回8个字符。

值得注意的是，某些版本的Excel需要您编写应用程序

代码

  Sub TestIt（）
 Dim TryIt As String 
 
 TryIt =Test& Chr（0）& asdf
 MsgBox Len（TryIt）'打印9 
 TryIt = Application.Clean（TryIt）
 MsgBox Len（TryIt）'打印4 
 
 End Sub 
  

和

  Sub TestIt2（）
 Dim TryIt As String 
 
 TryIt =Test& Chr（0）& asdf
 MsgBox Len（TryIt）'打印9 
 TryIt =替换（TryIt，Chr（0），）
 MsgBox Len（TryIt）'打印8 
 
 End Sub 
  

解决方案

非常简单：删除 chr（0）之后的所有字符都是一个错误。

清洁旨在删除 chr（0）以及其他非打印字符，如VBA语言参考中所示：

Clean功能旨在删除前32个非打印字符来自文本的7位ASCII代码（值0到31）。

清除按照预期的方式对所有其他提到的 chr（）值EXCEPT chr（0）明确删除所有以下字符。

如果您必须使用空值，则需要进行某种转换。您可以通过执行 TryIt = Replace（TryIt，Chr（0），Chr（1））之类的操作获得预期结果，之后结果将按预期方式工作对于清洁的所有其他31 chr（）代码旨在删除）：

  Sub TestIt3（）
 
 Dim TryIt As String 
 Dim iCounter As Integer 
 
对于iCounter = 0到31 
 TryIt =Test& Chr（iCounter）& asdf
 TryIt = Replace（TryIt，Chr（0），Chr（1））
 Debug.Printchr（& iCounter&）& & Len（TryIt）
 TryIt = Application.Clean（TryIt）
 Debug.Print已清除 -  chr（& iCounter&）& & Len（TryIt）
下一个iCounter 
 
 End Sub 
  

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

解决方案

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

这篇关于为什么Application.Clean（）可以删除可打印字符？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！