PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Check that only ASCII chars are used

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Check that only ASCII chars are used

 
 
Makelei
Guest
Posts: n/a
 
      26th May 2009
Hi,
What would be the possibilities to check that only ASCII chars are used
within one file?

Thanks in advance once again
Markku
 
Reply With Quote
 
 
 
 
Peter T
Guest
Posts: n/a
 
      26th May 2009
what sort of file, xls, txt....

Regards,
Peter T

"Makelei" <(E-Mail Removed)> wrote in message
news:51794650-72BC-4345-84BF-(E-Mail Removed)...
> Hi,
> What would be the possibilities to check that only ASCII chars are used
> within one file?
>
> Thanks in advance once again
> Markku
 
Reply With Quote
 
Makelei
Guest
Posts: n/a
 
      26th May 2009
Hi,
xls and 2003

BR
MakeLei

"Peter T" wrote:

> what sort of file, xls, txt....
>
> Regards,
> Peter T
>
> "Makelei" <(E-Mail Removed)> wrote in message
> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
> > Hi,
> > What would be the possibilities to check that only ASCII chars are used
> > within one file?
> >
> > Thanks in advance once again
> > Markku
>
>
>
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
> Hi,
> xls and 2003
>
> BR
> MakeLei
>
> "Peter T" wrote:
>
>> what sort of file, xls, txt....
>>
>> Regards,
>> Peter T
>>
>> "Makelei" <(E-Mail Removed)> wrote in message
>> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
>> > Hi,
>> > What would be the possibilities to check that only ASCII chars are used
>> > within one file?
>> >
>> > Thanks in advance once again
>> > Markku
>>
>>
>>
 
Reply With Quote
 
Makelei
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
> > Hi,
> > xls and 2003
> >
> > BR
> > MakeLei
> >
> > "Peter T" wrote:
> >
> >> what sort of file, xls, txt....
> >>
> >> Regards,
> >> Peter T
> >>
> >> "Makelei" <(E-Mail Removed)> wrote in message
> >> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
> >> > Hi,
> >> > What would be the possibilities to check that only ASCII chars are used
> >> > within one file?
> >> >
> >> > Thanks in advance once again
> >> > Markku
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
Patrick Molloy
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
news:A1358135-4909-4EE3-884E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
>> > Hi,
>> > xls and 2003
>> >
>> > BR
>> > MakeLei
>> >
>> > "Peter T" wrote:
>> >
>> >> what sort of file, xls, txt....
>> >>
>> >> Regards,
>> >> Peter T
>> >>
>> >> "Makelei" <(E-Mail Removed)> wrote in message
>> >> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
>> >> > Hi,
>> >> > What would be the possibilities to check that only ASCII chars are
>> >> > used
>> >> > within one file?
>> >> >
>> >> > Thanks in advance once again
>> >> > Markku
>> >>
>> >>
>> >>
>>
>>
>>
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
news:A1358135-4909-4EE3-884E-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
>> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
>> > Hi,
>> > xls and 2003
>> >
>> > BR
>> > MakeLei
>> >
>> > "Peter T" wrote:
>> >
>> >> what sort of file, xls, txt....
>> >>
>> >> Regards,
>> >> Peter T
>> >>
>> >> "Makelei" <(E-Mail Removed)> wrote in message
>> >> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
>> >> > Hi,
>> >> > What would be the possibilities to check that only ASCII chars are
>> >> > used
>> >> > within one file?
>> >> >
>> >> > Thanks in advance once again
>> >> > Markku
>> >>
>> >>
>> >>
>>
>>
>>
 
Reply With Quote
 
Peter T
Guest
Posts: n/a
 
      26th May 2009
> 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" <(E-Mail Removed)> wrote in message
news:E3F0C4BF-E59B-47E1-B9D9-(E-Mail Removed)...
> 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" <(E-Mail Removed)> wrote in message
> news:A1358135-4909-4EE3-884E-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
>>> > Hi,
>>> > xls and 2003
>>> >
>>> > BR
>>> > MakeLei
>>> >
>>> > "Peter T" wrote:
>>> >
>>> >> what sort of file, xls, txt....
>>> >>
>>> >> Regards,
>>> >> Peter T
>>> >>
>>> >> "Makelei" <(E-Mail Removed)> wrote in message
>>> >> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
>>> >> > Hi,
>>> >> > What would be the possibilities to check that only ASCII chars are
>>> >> > used
>>> >> > within one file?
>>> >> >
>>> >> > Thanks in advance once again
>>> >> > Markku
>>> >>
>>> >>
>>> >>
>>>
>>>
>>>
 
Reply With Quote
 
Makelei
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
> news:A1358135-4909-4EE3-884E-(E-Mail Removed)...
> > 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" <(E-Mail Removed)> wrote in message
> >> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
> >> > Hi,
> >> > xls and 2003
> >> >
> >> > BR
> >> > MakeLei
> >> >
> >> > "Peter T" wrote:
> >> >
> >> >> what sort of file, xls, txt....
> >> >>
> >> >> Regards,
> >> >> Peter T
> >> >>
> >> >> "Makelei" <(E-Mail Removed)> wrote in message
> >> >> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
> >> >> > Hi,
> >> >> > What would be the possibilities to check that only ASCII chars are
> >> >> > used
> >> >> > within one file?
> >> >> >
> >> >> > Thanks in advance once again
> >> >> > Markku
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>
>
>
 
Reply With Quote
 
keiji kounoike
Guest
Posts: n/a
 
      26th May 2009
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" <(E-Mail Removed)> wrote in message
> news:A1358135-4909-4EE3-884E-(E-Mail Removed)...
>> 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" <(E-Mail Removed)> wrote in message
>>> news:10EDE398-46FF-4A91-9563-(E-Mail Removed)...
>>>> Hi,
>>>> xls and 2003
>>>>
>>>> BR
>>>> MakeLei
>>>>
>>>> "Peter T" wrote:
>>>>
>>>>> what sort of file, xls, txt....
>>>>>
>>>>> Regards,
>>>>> Peter T
>>>>>
>>>>> "Makelei" <(E-Mail Removed)> wrote in message
>>>>> news:51794650-72BC-4345-84BF-(E-Mail Removed)...
>>>>>> Hi,
>>>>>> What would be the possibilities to check that only ASCII chars are
>>>>>> used
>>>>>> within one file?
>>>>>>
>>>>>> Thanks in advance once again
>>>>>> Markku
>>>>>
>>>>>
>>>
>>>
>
>
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Column grouping and hiding being over-ridden when sheet opened | RE: Delete VB code from a worksheet »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Check if path + filename exceeds 255 chars sp_mclaugh@yahoo.com Windows XP General 0 13th Jan 2007 10:23 PM
upper 128 ASCII chars in a text file Sean Kirkpatrick Microsoft VB .NET 5 13th Oct 2005 07:03 AM
URGENT! check if cell has only ALPHA chars Microsoft Excel Programming 5 29th Sep 2004 09:27 PM
Extended ascii chars =?Utf-8?B?SmFtZXMgUGFoaXJpcw==?= Microsoft VB .NET 3 11th Feb 2004 11:42 AM
What ASCII chars are valid for what in XP? Martin Richecoeur Windows XP Basics 2 21st Aug 2003 04:31 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 03:38 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top