Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    select foreign characters (excel 2003)

    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.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: select foreign characters (excel 2003)

    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

  3. #3
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

    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.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: select foreign characters (excel 2003)

    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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: select foreign characters (excel 2003)

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

  6. #6
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

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

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: select foreign characters (excel 2003)

    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

  8. #8
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

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

  9. #9
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

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

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: select foreign characters (excel 2003)

    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>

  11. #11
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

    Thank you, that worked great!

  12. #12
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

    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.

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: select foreign characters (excel 2003)

    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.

  14. #14
    Star Lounger
    Join Date
    Aug 2003
    Posts
    57
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: select foreign characters (excel 2003)

    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

  15. #15
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: select foreign characters (excel 2003)

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •