vba 识别基于文本的单元格中的无效字符
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/262537/
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
Identify invalid characters in text based cell
提问by Steve Hawkins
I recently inherited a VBA macro that needs to have validation logic added to it. I need to be able to determine if any characters in a text based cell are non ASCII characters (i.e. have a binary value > 0x7F). The cells may contain some carriage control values (particularly linefeeds) that need to be retained (so the CLEAN function does not work for this validation). I have tried the IsText function, but found that it will interpret UTF-8 character sequences as valid text (which I don't want).
我最近继承了一个需要添加验证逻辑的 VBA 宏。我需要能够确定基于文本的单元格中的任何字符是否为非 ASCII 字符（即二进制值 > 0x7F）。单元格可能包含一些需要保留的回车控制值（特别是换行符）（因此 CLEAN 函数不适用于此验证）。我尝试过 IsText 函数，但发现它会将 UTF-8 字符序列解释为有效文本（我不想要）。
I don't need to actually manipulate the string, I just want to display an error to the user that runs the macro to tell him that there are invalid (non-ASCII) characters in a specific cell.
我不需要实际操作字符串，我只想向运行宏的用户显示一个错误，告诉他特定单元格中存在无效（非 ASCII）字符。
回答by dbb
If you want a technically pure approach you might try a regular expression. Add a reference in VBA to the Microsoft Scripting library, and try this code. It looks a little complex, but you will be blown away by what regular expressions can do, and you will have a valuable tool for future use.
如果您想要一种纯技术方法，您可以尝试使用正则表达式。在 VBA 中添加对 Microsoft 脚本库的引用，并尝试使用此代码。它看起来有点复杂，但您会被正则表达式的功能所震撼，并且您将拥有一个有价值的工具供将来使用。
Function IsTooHigh(c As String) As Boolean
Dim RegEx As Object
Set RegEx = CreateObject("vbscript.regexp")
With RegEx
.Global = True
.MultiLine = True
.Pattern = "[^\x00-\x7F]"
End With
IsTooHigh = RegEx.Test(c)
End Function
This function gives TRUE if any character in string c is not (^) in the range 0 (x00) to 127 (x7F).
如果字符串 c 中的任何字符不是 0 (x00) 到 127 (x7F) 范围内的 (^)，则此函数给出 TRUE。
You can Google for "regular expression" and whatever you need it to do, and take the answer from almost any language, because like SQL, regular expression patterns seem to be language agnostic.
您可以在 Google 上搜索“正则表达式”以及您需要它做什么，并从几乎任何语言中获取答案，因为与 SQL 一样，正则表达式模式似乎与语言无关。
回答by LeppyR64
The asc(character) command will convert a character to it's ASCII value.
asc(character) 命令将一个字符转换为它的 ASCII 值。
hex(asc(character)) will convert the character to it's HEX value.
hex(asc(character)) 将字符转换为它的十六进制值。
Once you've done that you can easily do some comparisons to determine if the data is bad and toss the errors if required.
完成后，您可以轻松地进行一些比较以确定数据是否错误，并在需要时抛出错误。
Here's some sample code: http://www.freevbcode.com/ShowCode.asp?ID=4486
回答by jpinto3912
Function IsGoodAscii(aString as String) as Boolean
Dim i as Long
Dim iLim as Long
i=1
iLim=Len(aString)
While i<=iLim
If Asc(Mid(aString,i,1))>127 then
IsGoodAscii=False
Exit Function
EndIf
i=i+1
Wend
IsGoodAscii=True
End Function
