识别基于文本的单元格中的无效字符

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)字符。

3 个解决方案

#1

The asc(character) command will convert a character to it's ASCII value.

asc(字符)命令将字符转换为ASCII值。

hex(asc(character)) will convert the character to it's HEX value.

十六进制(asc(字符))将字符转换为它的十六进制值。

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

以下是一些示例代码:http://www.freevbcode.com/ShowCode.asp?ID = 4486

#2

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 Scripting库,并尝试此代码。它看起来有点复杂,但你会被正则表达式所能做的所震撼,并且你将拥有一个有价值的工具供将来使用。

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.

你可以谷歌“正则表达”和你需要做的任何事情,并从几乎任何语言中得到答案,因为像SQL一样,正则表达式模式似乎与语言无关。

#3

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

#1