vba - 使用 VBA 识别并删除电子表格中的不间断空格字符

Question

好吧，我已经创建了(在 StackOverflow 的最佳帮助下)一个用户定义函数，它标识给定单元格内特定的(不可见的)、不可打印的控制字符。然后，UDF 创建一个消息框，告诉用户已找到并删除了哪些字符。

这些函数不会费心去定位 32 个 ASCII 控制字符中的每一个，它只会查找在 Excel 中没有图形表示的字符。

这是当前状态的函数:

     Function findInvisChar(sInput As String) As String
        Dim sSpecialChars As String
        Dim i As Long
        Dim sReplaced As String
        Dim ln As Integer


        sSpecialChars = "" & Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & Chr(32) 'This is your list of characters to be removed
        'For loop will repeat equal to the length of the sSpecialChars string
        'loop will check each character within sInput to see if it matches any character within the sSpecialChars string
        For i = 1 To Len(sSpecialChars)
            ln = Len(sInput) 'sets the integer variable 'ln' equal to the total length of the input for every iteration of the loop
            sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
            'If ln <> Len(sInput) Then sReplaced = sReplaced & Mid$(sSpecialChars, i, 1)
            If ln <> Len(sInput) Then sReplaced = sReplaced & IIf(Mid$(sSpecialChars, i, 1) = Chr(10), "<Line Feed>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(1), "<Start of Heading>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(9), "<Character Tabulation, Horizontal Tabulation>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(13), "<Carriage Return>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(28), "<File Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(29), "<Group separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(30), "<Record Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(31), "<Unit Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about invisible characters which were removed        

Next

        MsgBox sReplaced & "These were removed"
        sInput = UCase(sInput)
        findSpecial = sInput


    End Function 'end of function

我想做的，也是让这个函数识别不间断的空格字符。它们的 unicode 值为 U+00A0。这是我创建的代码部分，用于识别不间断空格(如果它确实出现在目标单元格中​​的话):

IIf(Mid$(sSpecialChars, i, 1) = ChrW(A0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1))

我在使用 ChrW() 函数时遇到问题。据我了解，它接受 unicode 字符的十六进制值，在这种情况下，如果我没有记错的话，该值将是 A0。

我在Excel文档中放置了一个不间断空格来测试该功能，但是当我这样做时，该功能不起作用。消息框只是显示为空白，而不是告诉用户“...这些字符已被删除。

我是否错误地使用了 ChrW() 函数？或者我的代码或测试方法是否存在我可能遗漏的其他问题？

Answer 1

我会回答我自己的问题，这样它就不会在没有答案的情况下徘徊。

问题是我之前没有告诉函数在要删除的字符串变量列表 sSpecialChars 中查找并删除不间断空格。我将 ChrW(&HA0) 添加到该字符串中，现在一切顺利。

感谢 Portland Runner 向我展示了使用 ChrW() 函数的正确方法。

供引用，我的最终代码如下:

Function findInvisChar(sInput As String) As String
Dim sSpecialChars As String
Dim i As Long
Dim sReplaced As String
Dim ln As Integer


sSpecialChars = "" & Chr(1) & Chr(2) & Chr(3) & Chr(4) & Chr(5) & Chr(6) & Chr(7) & Chr(8) & Chr(9) & Chr(10) & Chr(11) & Chr(12) & Chr(13) & Chr(14) & Chr(15) & Chr(16) & Chr(17) & Chr(18) & Chr(19) & Chr(20) & Chr(21) & Chr(22) & Chr(23) & Chr(24) & Chr(25) & Chr(26) & Chr(27) & Chr(28) & Chr(29) & Chr(30) & Chr(31) & Chr(32) & ChrW(&HA0) 'This is your list of characters to be removed
'For loop will repeat equal to the length of the sSpecialChars string
'loop will check each character within sInput to see if it matches any character within the sSpecialChars string
For i = 1 To Len(sSpecialChars)
    ln = Len(sInput) 'sets the integer variable 'ln' equal to the total length of the input for every iteration of the loop
    sInput = Replace$(sInput, Mid$(sSpecialChars, i, 1), "")
    If ln <> Len(sInput) Then sReplaced = sReplaced & Mid$(sSpecialChars, i, 1)
    If ln <> Len(sInput) Then sReplaced = sReplaced & IIf(Mid$(sSpecialChars, i, 1) = Chr(10), "<Line Feed>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(1), "<Start of Heading>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(9), "<Character Tabulation, Horizontal Tabulation>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(13), "<Carriage Return>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(28), "<File Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(29), "<Group separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(30), "<Record Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = Chr(31), "<Unit Separator>", Mid$(sSpecialChars, i, 1)) & IIf(Mid$(sSpecialChars, i, 1) = ChrW(&HA0), "<Non-Breaking Space>", Mid$(sSpecialChars, i, 1)) 'Currently will remove all control character but only tell the user about Bell and Line Feed
Next

MsgBox sReplaced & " These were identified and removed"
findInvisChar = sInput


End Function 'end of function