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…