Excel VBA: Identify invalid characters in text based cell

Question

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).

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.

Any suggestions would be appreciated.

Answer 1

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

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

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

Answer 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.

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).

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.

Answer 3

Function IsGoodAcii(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

IsGoodAcii=True
End Function