CODEWITHSUNDEEP
vbaexcelnon-ascii-characters
alex_gabriel
alex_gabriel

Reputation: 373

Excel 2007 vbe not recognising accented characters

So I was trying to write a vba code to convert accented characters to regular characters. I had declared all the accented characters as a constant, and the regular characters as another constant. But I have trouble inserting certain accented characters into the vba script as the visual basic editor recognise them as '??' only.

This is what I'm trying to do:

Const AccChars = "ŠŽšžŸÀÁÂÃÄÅĀÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿěłļņī"
Const RegChars = "SZszYAAAAAAACEEEEIIIIDNOOOOOUUUUYaaaaaaceeeeiiiidnooooouuuuyyellni"

So that I can replace AccChars with RegChars.

But when I copy the above lines to vbe, the Const AccChars appears like this:

Const AccChars = "ŠŽšžŸÀÁÂÃÄ?ĀÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäå?èéêëìíîïðñòóôõöùúûüýÿ?????"

How can I get vbe accept characters like Ç,ě,ł,ļ,ņ,ī,Å .

I tried changing the font from tools->options and even the Language for non-Unicode programs (System Locale) from control panel. I also tried reading the string from another text file, which wasn't effective too.

Any suggestions would be highly appreciated.

Upvotes: 1

Views: 1053

Answers (1)

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

I don't think you can get the VBE (or a regular messagebox) to display characters with a code > 255. But you can construct a string that will contain those characters by adding the appropriate Unicode characters. So for your AccChars string in your post:

Dim AccChars As String
AccChars = "ŠŽšžŸÀÁÂÃÄÅAÇÈÉÊËÌÍÎÏÐÑÒÓÔÕÖÙÚÛÜÝàáâãäåçèéêëìíîïðñòóôõöùúûüýÿ" _
        & ChrW(283) & ChrW(322) & ChrW(316) & ChrW(326) & ChrW(299)

You can then test for those characters in the strings you are trying to translate.

EDIT (additional info): If you write the string AccChars to an Excel cell, or some other object that can display Unicode characters (such as a UserForm.TextBox), you will see the characters appropriately rendered if you select a font that includes Unicode characters.

Upvotes: 4

Related Questions