Check that only ASCII chars are used

  • Thread starter Thread starter Makelei
  • Start date Start date
M

Makelei

Hi,
What would be the possibilities to check that only ASCII chars are used
within one file?

Thanks in advance once again
Markku
 
So are you saying you want to check if any text cells contain characters in
the range chr(x) where x is between 128 and 255

Also, could any formula cells return text with these characters.

For the result do you just want a yes/no answer or any information about
which cells contain non ASCII characte5rs

Regards,
Peter T
 
Hi,
I just want to high light the cell with pink. I have about 30 000 rows and
30 columns in use.

This is to be used as data is entered to cell. Sub Worksheet_Change(ByVal
Target As Range)

BR
MakeLei
 
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count = 1 Then
CheckASCII Target
End If
End Sub
Sub CheckASCII(Target As Range)
Dim text As String
Dim ascii As Long
Dim pos As Long
text = Target.text
For pos = 1 To Len(text)
Select Case Asc(Mid(text, pos, 1))
Case 27 To 255 'CHECK THESE!
Case Else
Target.Interior.ColorIndex = vbRed
Exit Sub
End Select
Next
End Sub
 
What you now say you want is very different to what you asked in your OP

this is only lightly tested -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bIsASCII As Boolean
Dim nClr1 As Long, nClr2 As Long
Dim ba() As Byte
Dim cel As Range

For Each cel In Target
ba = CStr(cel.Value)

If IsNumeric(cel) Then
bIsASCII = True
Else
bIsASCII = IsAllASCII(ba)
End If

With cel.Interior
nClr1 = .ColorIndex
nClr2 = 0
If bIsASCII Then
If nClr1 = 38 Then nClr2 = xlNone
Else
If nClr1 <> 38 Then nClr2 = 38
End If
If nClr2 Then .ColorIndex = nClr2

End With
Next
End Sub

Function IsAllASCII(ba() As Byte) As Boolean
Dim bFlag As Boolean
Dim i As Long

For i = 0 To UBound(ba) - 1 Step 2
If ba(i) < 128 And ba(i + 1) = 0 Then
' it's an ASCII
Else
bFlag = True
Exit For
End If
Next
IsAllASCII = Not bFlag

End Function

Regards,
Peter T
 
Select Case Asc(Mid(text, pos, 1))
Case 27 To 255 'CHECK THESE!
Case Else
Click to expand...

The OP did ask for ASCII characters, which is the 128 with codes 0 to 127
and also include non printing characters, such as line breaks below '27'.
However if looking for ANSI and Unicode above 255, with your approach change
Asc to AscW

Regards,
Peter T
 
I think the code below should be
If IsNumeric(cel) Then
bIsASCII = True
Else
bIsASCII = IsAllASCII(ba)
End If
Click to expand...

bIsASCII = IsAllASCII(ba)

IsNumeric returns true even if a number consist of None ASCII characters.

Keiji
 
IsNumeric returns true even if a number consist of None ASCII characters

Have I missed something, how can a number contain a non ASCII character.
0-9, comma and dot are all ASCII. Is it different in your system language?

The point of "If IsNumeric(cel)" was to avoid unnecessarily calling the
IsAllASCII function with numeric or empty cells. The boolean bIsASCII is
still required to reset any 'pink' cells if necessary.

Regards,
Peter T
 
Two things I think you might find interesting. First, your IsAllASCII can be
accomplished with a one-liner routine (although the function's argument is
changed to a String value)...

Function IsAllASCII(S As String) As Boolean
IsAllASCII = Not x Like "*[!" & Chr(0) & "-" & Chr(127) & "]*"
End Function

Second, because the function is a one-liner, we can eliminate the function
call altogether by incorporating the one-liner inside the Change event code;
plus we can change your logic to simplify the Change event code
considerably. This event code procedure functions the same as your
originally posted code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
Cel.Interior.ColorIndex = 38
Else
Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

In the above form, the function color assignment is obvious (even if the
If..Then statement might cause one to pause<g>); here is even shorter, but
more obfuscated, code that functions identically to the above...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
Cel.Interior.ColorIndex = xlNone - 4180 * (Cel.Value Like "*[!" & _
Chr(0) & "-" & Chr(127) & "]*")
Next
End Sub

In case you were wondering where the 4180 came from, it is formed by
subtracting 38 (the color index value you want to assign for non-ASCII text)
from the predefined xlNone constant.
 
Hi Rick,

That's great!
Couple of things, although the one liner is very cool, In a change event I
think it's worth only applying formats if different to what already exist.
That's partly to improve speed (write is slow) but much more usefully to
preserve Undo unless absolutely necessary.

In a light test, after pasting a large number of cells w/out ASCII
characters, your one liner seemed take much longer to complete than the
example I posted (even if all formats do need to be changed). Or try copying
just one column to another with .

Regards,
Peter T

Rick Rothstein said:
Two things I think you might find interesting. First, your IsAllASCII can
be accomplished with a one-liner routine (although the function's argument
is changed to a String value)...

Function IsAllASCII(S As String) As Boolean
IsAllASCII = Not x Like "*[!" & Chr(0) & "-" & Chr(127) & "]*"
End Function

Second, because the function is a one-liner, we can eliminate the function
call altogether by incorporating the one-liner inside the Change event
code; plus we can change your logic to simplify the Change event code
considerably. This event code procedure functions the same as your
originally posted code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
Cel.Interior.ColorIndex = 38
Else
Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

In the above form, the function color assignment is obvious (even if the
If..Then statement might cause one to pause<g>); here is even shorter, but
more obfuscated, code that functions identically to the above...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
Cel.Interior.ColorIndex = xlNone - 4180 * (Cel.Value Like "*[!" & _
Chr(0) & "-" & Chr(127) & "]*")
Next
End Sub

In case you were wondering where the 4180 came from, it is formed by
subtracting 38 (the color index value you want to assign for non-ASCII
text) from the predefined xlNone constant.

--
Rick (MVP - Excel)


Peter T said:
What you now say you want is very different to what you asked in your OP

this is only lightly tested -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bIsASCII As Boolean
Dim nClr1 As Long, nClr2 As Long
Dim ba() As Byte
Dim cel As Range

For Each cel In Target
ba = CStr(cel.Value)

If IsNumeric(cel) Then
bIsASCII = True
Else
bIsASCII = IsAllASCII(ba)
End If

With cel.Interior
nClr1 = .ColorIndex
nClr2 = 0
If bIsASCII Then
If nClr1 = 38 Then nClr2 = xlNone
Else
If nClr1 <> 38 Then nClr2 = 38
End If
If nClr2 Then .ColorIndex = nClr2

End With
Next
End Sub

Function IsAllASCII(ba() As Byte) As Boolean
Dim bFlag As Boolean
Dim i As Long

For i = 0 To UBound(ba) - 1 Step 2
If ba(i) < 128 And ba(i + 1) = 0 Then
' it's an ASCII
Else
bFlag = True
Exit For
End If
Next
IsAllASCII = Not bFlag

End Function

Regards,
Peter T
Click to expand...
Click to expand...
 
Fair points. Does this help any (speedwise)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
If Cel.Interior.ColorIndex <> 38 Then Cel.Interior.ColorIndex = 38
Else
If Cel.Interior.ColorIndex = 38 Then Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

--
Rick (MVP - Excel)


Peter T said:
Hi Rick,

That's great!
Couple of things, although the one liner is very cool, In a change event I
think it's worth only applying formats if different to what already exist.
That's partly to improve speed (write is slow) but much more usefully to
preserve Undo unless absolutely necessary.

In a light test, after pasting a large number of cells w/out ASCII
characters, your one liner seemed take much longer to complete than the
example I posted (even if all formats do need to be changed). Or try
copying just one column to another with .

Regards,
Peter T

Rick Rothstein said:
Two things I think you might find interesting. First, your IsAllASCII can
be accomplished with a one-liner routine (although the function's
argument is changed to a String value)...

Function IsAllASCII(S As String) As Boolean
IsAllASCII = Not x Like "*[!" & Chr(0) & "-" & Chr(127) & "]*"
End Function

Second, because the function is a one-liner, we can eliminate the
function call altogether by incorporating the one-liner inside the Change
event code; plus we can change your logic to simplify the Change event
code considerably. This event code procedure functions the same as your
originally posted code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
Cel.Interior.ColorIndex = 38
Else
Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

In the above form, the function color assignment is obvious (even if the
If..Then statement might cause one to pause<g>); here is even shorter,
but more obfuscated, code that functions identically to the above...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
Cel.Interior.ColorIndex = xlNone - 4180 * (Cel.Value Like "*[!" & _
Chr(0) & "-" & Chr(127) & "]*")
Next
End Sub

In case you were wondering where the 4180 came from, it is formed by
subtracting 38 (the color index value you want to assign for non-ASCII
text) from the predefined xlNone constant.

--
Rick (MVP - Excel)


Peter T said:
What you now say you want is very different to what you asked in your
OP

this is only lightly tested -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bIsASCII As Boolean
Dim nClr1 As Long, nClr2 As Long
Dim ba() As Byte
Dim cel As Range

For Each cel In Target
ba = CStr(cel.Value)

If IsNumeric(cel) Then
bIsASCII = True
Else
bIsASCII = IsAllASCII(ba)
End If

With cel.Interior
nClr1 = .ColorIndex
nClr2 = 0
If bIsASCII Then
If nClr1 = 38 Then nClr2 = xlNone
Else
If nClr1 <> 38 Then nClr2 = 38
End If
If nClr2 Then .ColorIndex = nClr2

End With
Next
End Sub

Function IsAllASCII(ba() As Byte) As Boolean
Dim bFlag As Boolean
Dim i As Long

For i = 0 To UBound(ba) - 1 Step 2
If ba(i) < 128 And ba(i + 1) = 0 Then
' it's an ASCII
Else
bFlag = True
Exit For
End If
Next
IsAllASCII = Not bFlag

End Function

Regards,
Peter T

Hi,
I just want to high light the cell with pink. I have about 30 000 rows
and
30 columns in use.

This is to be used as data is entered to cell. Sub
Worksheet_Change(ByVal
Target As Range)

BR
MakeLei

:

So are you saying you want to check if any text cells contain
characters in
the range chr(x) where x is between 128 and 255

Also, could any formula cells return text with these characters.

For the result do you just want a yes/no answer or any information
about
which cells contain non ASCII characte5rs

Regards,
Peter T



Hi,
xls and 2003

BR
MakeLei

:

what sort of file, xls, txt....

Regards,
Peter T

Hi,
What would be the possibilities to check that only ASCII chars
are used
within one file?

Thanks in advance once again
Markku
Click to expand...
Click to expand...
Click to expand...
 
Does this help any (speedwise)?

It sure does!

Regards,
Peter T

Rick Rothstein said:
Fair points. Does this help any (speedwise)?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
If Cel.Interior.ColorIndex <> 38 Then Cel.Interior.ColorIndex = 38
Else
If Cel.Interior.ColorIndex = 38 Then Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

--
Rick (MVP - Excel)


Peter T said:
Hi Rick,

That's great!
Couple of things, although the one liner is very cool, In a change event
I think it's worth only applying formats if different to what already
exist. That's partly to improve speed (write is slow) but much more
usefully to preserve Undo unless absolutely necessary.

In a light test, after pasting a large number of cells w/out ASCII
characters, your one liner seemed take much longer to complete than the
example I posted (even if all formats do need to be changed). Or try
copying just one column to another with .

Regards,
Peter T

Rick Rothstein said:
Two things I think you might find interesting. First, your IsAllASCII
can be accomplished with a one-liner routine (although the function's
argument is changed to a String value)...

Function IsAllASCII(S As String) As Boolean
IsAllASCII = Not x Like "*[!" & Chr(0) & "-" & Chr(127) & "]*"
End Function

Second, because the function is a one-liner, we can eliminate the
function call altogether by incorporating the one-liner inside the
Change event code; plus we can change your logic to simplify the Change
event code considerably. This event code procedure functions the same as
your originally posted code...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
If Cel.Value Like "*[!" & Chr(0) & "-" & Chr(127) & "]*" Then
Cel.Interior.ColorIndex = 38
Else
Cel.Interior.ColorIndex = xlNone
End If
Next
End Sub

In the above form, the function color assignment is obvious (even if the
If..Then statement might cause one to pause<g>); here is even shorter,
but more obfuscated, code that functions identically to the above...

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Cel As Range
For Each Cel In Target
Cel.Interior.ColorIndex = xlNone - 4180 * (Cel.Value Like "*[!" & _
Chr(0) & "-" & Chr(127) & "]*")
Next
End Sub

In case you were wondering where the 4180 came from, it is formed by
subtracting 38 (the color index value you want to assign for non-ASCII
text) from the predefined xlNone constant.

--
Rick (MVP - Excel)


What you now say you want is very different to what you asked in your
OP

this is only lightly tested -

Private Sub Worksheet_Change(ByVal Target As Range)
Dim bIsASCII As Boolean
Dim nClr1 As Long, nClr2 As Long
Dim ba() As Byte
Dim cel As Range

For Each cel In Target
ba = CStr(cel.Value)

If IsNumeric(cel) Then
bIsASCII = True
Else
bIsASCII = IsAllASCII(ba)
End If

With cel.Interior
nClr1 = .ColorIndex
nClr2 = 0
If bIsASCII Then
If nClr1 = 38 Then nClr2 = xlNone
Else
If nClr1 <> 38 Then nClr2 = 38
End If
If nClr2 Then .ColorIndex = nClr2

End With
Next
End Sub

Function IsAllASCII(ba() As Byte) As Boolean
Dim bFlag As Boolean
Dim i As Long

For i = 0 To UBound(ba) - 1 Step 2
If ba(i) < 128 And ba(i + 1) = 0 Then
' it's an ASCII
Else
bFlag = True
Exit For
End If
Next
IsAllASCII = Not bFlag

End Function

Regards,
Peter T

Hi,
I just want to high light the cell with pink. I have about 30 000 rows
and
30 columns in use.

This is to be used as data is entered to cell. Sub
Worksheet_Change(ByVal
Target As Range)

BR
MakeLei

:

So are you saying you want to check if any text cells contain
characters in
the range chr(x) where x is between 128 and 255

Also, could any formula cells return text with these characters.

For the result do you just want a yes/no answer or any information
about
which cells contain non ASCII characte5rs

Regards,
Peter T



Hi,
xls and 2003

BR
MakeLei

:

what sort of file, xls, txt....

Regards,
Peter T

Hi,
What would be the possibilities to check that only ASCII chars
are used
within one file?

Thanks in advance once again
Markku
Click to expand...
Click to expand...
Click to expand...
 
In my system language, I can describe 0-9, comma and dot with None
ASCII. for example, there is another 0 that is 0xFF11 in Unicode, 9 is
0xFF09 and dot is 0xFF0E in my system. these are also recognized as
number. i also wonder whether ba(i+1) is guaranteed to be 0 if character
is ASCII in any system(or machine). i am talking about endian. but i'm
not sure about this because i don't have a machine with big endian, so i
can't check this.

Keiji
 
Interesting. It seems some unicode characters become numeric when in cells.
I found these

Sub test()
Dim i As Long, n As Long
Dim arr, v
arr = Array(1632, 2407, 2662, 2790, 3047, 3174, 3302, 3664)
For Each v In arr
For i = v To v + 9
n = n + 1
Cells(n, 1) = i
Cells(n, 2) = ChrW(i)
Next
Next
Range("C1:C" & n).Formula = "=VALUE(B1)"

End Sub

However, in VB/A Val(ChrW(1640)) = 0 and not 8. It seems Excel does some
additional conversion.

You are right, it is better not to do the IsNumeric check at all, unless the
objective is also to allow non ASCII numeric characters. Thanks for pointing
this out.

I don't think endian is relevant for Windows/Excel users, so this should
reliably define ASCII characters: ba(i) = 0-127 and ba(i+1) = 0

However, if for some reason the characters are read as big endian then would
need to do the reverse: ba(i+1) = 0-127 and ba(i) = 0

Regards,
Peter T
 
Back
Top