select foreign characters (excel 2003)

[joe88888]

I have a large data excel file with English and Chinese character, some cells contain only Chinese, and some cells contain both(both are the same fonts and same size). How can I select only Chinese (or English) characters so I can format them to different font size. Macro is ok. Thank you in advance.

[sdckapr]

I have never worked with chinese characters in excel.

Are they just a different font name (eg are they like using 'Wingdings', if you select the cell and change all to 'Arial' do the chinese chars go away)?

If this is the case you can look at the Font.name properties of each character object.

<pre>Option Explicit
Sub ChangeWingdingSize()
Dim rCell As Range
Dim sFontName As String
Dim x As Long
Dim dSize As Double

sFontName = "Wingdings"
dSize = 22

For Each rCell In Selection
For x = 1 To Len(rCell)
With rCell.Characters(x, 1).Font
If .Name = sFontName Then _
.Size = dSize
End With
Next
Next
End Sub</pre>


This looks for each character of each cell in the selection and if the fontname is "wingding", it sets the size. Change the fontname to whatever the chinese font is and set the size as desired.

If the chinese chars are not done this way, you will have to give us details on how to tell them apart.

Hope this helps,
Steve

[joe88888]

Thank you for the reply, But they both have the same font name. I guess for the English, one character take up one byte, and one Chinese character take up two byte spaces. Please check out the attachment to see how they are different.

[sdckapr]

I am afraid that my system is not set up to see chinese characters. All I see is attached.
All the "squares" are (I assume) the chinese characters. But to me all are the same character (it has a code of 63).


Can you provide any info on property differences that VB can see in the characters (if not fontname, some other?)
Steve

[HansV]

In the VBA for Excel 2000 and up, there is a function AscW that returns an Integer corresponding to the Unicode number for a character. The standard characters are 0 ... 255 (here, AscW is the same as Asc), but special characters are outside that range, either <0 or >255. I thought I could use that, but for some reason, my Excel 2002 doesn't like <code>rCell.Characters(x, 1)</code>. I get the attached error message...

[joe88888]

I am sorry, I do not know the property differences that VB can see in the characters .

[sdckapr]

I tried Hans's suggestion (though I don't think he tried the function on the text property of the object, which is probably why he got the error. FYI, XL97 does have AscW function)

<pre>Sub ChangeCharacterSize()
Dim rCell As Range
Dim sFontName As String
Dim x As Long
Dim dSize As Double

dSize = 22

For Each rCell In Selection
For x = 1 To Len(rCell)
With rCell.Characters(x, 1)
If AscW(.Text) > 255 _
Or AscW(.Text) < 0 Then _
.Font.Size = dSize
End With
Next
Next
End Sub</pre>


This changed the size of the "odd characters" in your attachment (even though I don't see them as Chinese) Change the dSize variable as desired for the fontsize.

Steve

[joe88888]

Steve and Hans, you save my life. Sub ChangeCharacterSize worked.
Thank you very much!

[joe88888]

One more question, how to use your macro in MS Word 2003 to do the same function?

[HansV]

Try this slight variation in Word:

<code>Sub ChangeCharacterSize()</code>
<code> Dim x As Long</code>
<code> Dim dSize As Double</code>

<code> dSize = 22</code>

<code> For x = 1 To Selection.Characters.Count</code>
<code> With Selection.Characters(x)</code>
<code> If AscW(.Text) > 255 _</code>
<code> Or AscW(.Text) < 0 Then _</code>
<code> .Font.Size = dSize</code>
<code> End With</code>
<code> Next</code>
<code>End Sub</code>

[joe88888]

Thank you, that worked great!

[joe88888]

The word macro workes fine for the small file. I have a file that is about 300 pages run forever and come up the error message "too many spelling errors that can not be displyed " . please help.

[HansV]

Turn off "Check Spelling as you Type" and "Check Grammar as you Type" in the Spelling & Grammar tab of Tools | Options..., at least while you run the macro.

[joe88888]

I turned off both, and run it for 8 hours, no error message pop up, but still take forever. any idea?
Is it possible to revise the macro so that it can automatically select few hundred lines first and run the
change size function, after the function is done(I mean the size is adjusded on the dispay), select the
second few hundred lines, so on.
thanks

thanks

[HansV]

Code corrected by HansV

I don't think that would help, but not using the Selection object might, as well as turning off screen updates while the macro runs. Here is a modified version:

Sub ChangeCharacterSize()
Dim x As Long
Dim dSize As Double

On Error GoTo ErrHandler

dSize = 22
Application.ScreenUpdating = False
For x = 1 To ActiveDocument.Characters.Count
With ActiveDocument.Characters(x)
If AscW(.Text) > 255 _
Or AscW(.Text) < 0 Then _
.Font.Size = dSize
End With
Next

ExitHandler:
Application.ScreenUpdating = True
Exit Sub

ErrHandler:
MsgBox Err.Description, vbExclamation
Resume ExitHandler
End Sub