none
VBA uses wrong Alt codes !!! RRS feed

  • Question

  • I've created a macro which replaces signs \|:/*?<> (which cannot be used in file names) with other characters. VBA is to use alt codes for that task and most characters are being replaced ok, yet some characters VBA puts are, as I've noticed, alien to the standard Alt codes table, Thus the character '¿' with the alt code 0191 is being typed by the VBA as ' ї ' and '►' as '+'.

    The issue here, as I've figured out, lies in the cyrillic keyboard layout which the VBA for some reasons utilizes (^0191, ^0192, ^0193, ^0194 are referenced by " ї, A, Б, В, Г " in the Cyrillic alt code table instead of " ¿, └, Á, Â, Ã " in the Western type). I've had a Russian keyboard layout installed, yet VBA still types the characters incorrectly even after I've removed it, Word itself (I'm using the 2003 version) fetches the correct characters (in compliance with the Western keyboard type).

    Can the VBA be somehow 'told' to use the Western type layout ?? How else can it possibly be made to use the right alt codes ?

    Thursday, June 13, 2013 12:06 PM

All replies

  • Hi Aquinax,

    Show us your code please.


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Thursday, June 13, 2013 1:03 PM
  • It's something like this:

    ' ? with ¿
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
    .Text = "?"
    .Replacement.Text = "^0191"
    .Forward = True
    .Wrap = wdFindContinue
    .Format = False
    .MatchCase = False
    .MatchWholeWord = False
    .MatchWildcards = False
    .MatchSoundsLike = False
    .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
    Selection.HomeKey Unit:=wdStory

    it's stupid, really. Don't get what's wrong VBA does here ..

    Kind regards.

    • Edited by Aquinax Friday, June 14, 2013 12:02 AM
    Friday, June 14, 2013 12:01 AM
  • Hi Aquinax,

    Replace:

    .Replacement.Text = "^0191"

    with

    .Replacement.Text = ChrW(0191)


    Regards, Jan Karel Pieterse|Excel MVP|http://www.jkp-ads.com
    Saturday, June 15, 2013 5:05 PM
  •  

    I sofved the issue.

    The reason for the wrong alt codes was "language for non-Unicode programs" in "Regional and Language options" which was set for the Russian language. Having switched it to English, the characters produced by the VBA engine reverted back to the Latin characters.

    Jan, thanks for pointing this out, it is true that 'ChrW(####)' used in the VBA code to produce alt code characters is more consistent and reliable than '^####', though unfortunately I have not figured out how to type more than one character with the help of it.

    Tuesday, June 18, 2013 12:00 PM
  • Three characters:

    .Replacement.Text = ChrW(0191) & ChrW(0192) & ChrW(0195)


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Wednesday, June 19, 2013 6:53 PM
  • Three characters:

    .Replacement.Text = ChrW(0191) & ChrW(0192) & ChrW(0195)


    Felipe Costa Gualberto - http://www.ambienteoffice.com.br

    Thanks Felipe, that works!
    Saturday, June 22, 2013 5:23 PM