|
Check that only ASCII chars are used
Hi,
What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
what sort of file, xls, txt....
Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
Hi,
xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
Select Case Asc(Mid(text, pos, 1))
Case 27 To 255 'CHECK THESE! Case Else 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 "Patrick Molloy" wrote in message ... 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
Thanks Peter,
This is excellent for my purpose. BR MakeLei "Peter T" wrote: 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
I think the code below should be
If IsNumeric(cel) Then bIsASCII = True Else bIsASCII = IsAllASCII(ba) End If bIsASCII = IsAllASCII(ba) IsNumeric returns true even if a number consist of None ASCII characters. Keiji Peter T wrote: 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... I think the code below should be If IsNumeric(cel) Then bIsASCII = True Else bIsASCII = IsAllASCII(ba) End If bIsASCII = IsAllASCII(ba) IsNumeric returns true even if a number consist of None ASCII characters. Keiji Peter T wrote: 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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" <peter_t@discussions wrote in message ... 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
Does this help any (speedwise)?
It sure does! Regards, Peter T "Rick Rothstein" wrote in message ... 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" <peter_t@discussions wrote in message ... 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" wrote in message ... 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" <peter_t@discussions wrote in message ... 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 Peter T wrote: 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 "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... I think the code below should be If IsNumeric(cel) Then bIsASCII = True Else bIsASCII = IsAllASCII(ba) End If bIsASCII = IsAllASCII(ba) IsNumeric returns true even if a number consist of None ASCII characters. Keiji Peter T wrote: 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
Check that only ASCII chars are used
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 "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... 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 Peter T wrote: 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 "keiji kounoike" <"kounoike AT mbh.nifty.com" wrote in message ... I think the code below should be If IsNumeric(cel) Then bIsASCII = True Else bIsASCII = IsAllASCII(ba) End If bIsASCII = IsAllASCII(ba) IsNumeric returns true even if a number consist of None ASCII characters. Keiji Peter T wrote: 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 "Makelei" wrote in message ... 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 "Peter T" wrote: 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 "Makelei" wrote in message ... Hi, xls and 2003 BR MakeLei "Peter T" wrote: what sort of file, xls, txt.... Regards, Peter T "Makelei" wrote in message ... Hi, What would be the possibilities to check that only ASCII chars are used within one file? Thanks in advance once again Markku |
| All times are GMT +1. The time now is 06:49 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com