vba 使用vba检查excel中的特殊字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18254281/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Checking for special characters in excel using vba
提问by compscimaster
. I have an excel spreadsheet which contains some strings with unicode control characters that aren't visible in Windows 7. Therefore, I would like to write a macro to iterate through each cell in a column, checking if a control character is present. If a control character is found, I would like to populate the adjacent cell in the next column with the character name and the index it can be found within the string.
. 我有一个 excel 电子表格，其中包含一些带有 unicode 控制字符的字符串，这些字符串在 Windows 7 中不可见。因此，我想编写一个宏来遍历列中的每个单元格，检查是否存在控制字符。如果找到控制字符，我想用字符名称和可以在字符串中找到的索引填充下一列中的相邻单元格。
This is what I have so far:
这是我到目前为止：
Sub control_chr()
'
' control_chr Macro
'
'
Dim control_characters(Chr(28), Chr(29), Chr(30), Chr(31), Chr(32))
Dim r As Range, cell As Range
Set r = Range("F4:F1029")
For Each cell In r
For Each Character In control_characters
the next step would be to search the cell for each character and populate adjacent cells with the results. My first thought was to use the SEARCH() function since it returns the index of where the character is found. This is my first time using visual basic and I'm not sure how to proceed
下一步是在单元格中搜索每个字符并用结果填充相邻的单元格。我的第一个想法是使用 SEARCH() 函数，因为它返回找到字符位置的索引。这是我第一次使用visual basic，我不知道如何继续
采纳答案by Doug Glancy
Here's some code that does what you asked:
这是一些可以满足您要求的代码：
Sub ListControlChars()
Dim control_characters As Variant
Dim r As Range, cell As Range, ResultCell As Range
Dim CharPosition As Long
Dim i As Long
control_characters = Array(28, 29, 30, 31, 32)
Set r = ActiveSheet.Range("F4:F1029")
For Each cell In r
Set ResultCell = cell.Offset(0, 1)
ResultCell.ClearContents
CharPosition = 0
For i = LBound(control_characters) To UBound(control_characters)
CharPosition = InStr(cell, Chr(control_characters(i)))
If CharPosition > 0 Then
ResultCell = ResultCell.Value & "Char " & control_characters(i) & ": Position " & CharPosition & " - "
End If
Next i
Next cell
End Sub
If you want to do it in Excel you could set it up like this:
如果您想在 Excel 中执行此操作，您可以这样设置：
The formula in B2 is:
B2中的公式为：
=IFERROR(SEARCH(CHAR(B),$A2),"")
