With introduction of the "new" Microsoft Office’s file formats (XML-based, i.e. DOCX, XLSX etc.) Microsoft has finally solved problem of Unicode support and storing and displaying special characters in documents, spreadsheets, presentations etc. The only problem is that they’ve forgotten about Visual Basic for Applications and macros which still are using dully ANSI encoding.
The fastest solution is out of Excel and is based on altering Windows 10 control panel settings to instruct Windows how it should interact with applications that do not fully support Unicode.
But, suppose you don’t want to or can’t change your system settings. Then what?
Quick Google search revealed some possible solution, including:
- a great answer at Stack Overflow — based on an example from no longer available website,
- another answer to the same question — useless in my case, since Polish national characters are out of ANSI scope,
- MrExcel.com forum answer with some nifty VBA procedure — useless for the same reason, as above,
- and some idea with using a hidden sheet — rejected, because I found it too weird as for me.
Since I failed on finding a correct solution, I had to dig myself own one. Here’s the deal…
I have combined these two sources:
and came up with the following list:
Having this table aside all that was left was to do a quick find & replace sequence, for example:
And pieces of my code turned into something like this:
If JEDNOSCI = "5" Then Dzies = "pi" & ChrW$(&H119) & "tna" & ChrW$(&H15B) & "cie "
Note that some letters are missing in above table; I failed on finding corresponding Excel counterparts, sorry.