Polish and other non-ASCII special characters in VBA and Excel’s macros

adminMicrosoft2 Comments
4 min. reading

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:

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:

Polish letter Search for Replace with Reference
ę ê " & ChrW$(&H119) & " https://www.compart.com/en/unicode/U+0119
ó ó " & ChrW$(&HF3) & " https://www.compart.com/en/unicode/U+00F3
ą ¹ " & ChrW$(&H105) & " https://www.compart.com/en/unicode/U+0105
ś œ " & ChrW$(&H15B) & " https://www.compart.com/en/unicode/U+015B
ł ³ " & ChrW$(&H142) & " https://www.compart.com/en/unicode/U+0142
ż ¿ " & ChrW$(&H17C) & " https://www.compart.com/en/unicode/U+017C
ź ??? " & ChrW$(&H17A) & " https://www.compart.com/en/unicode/U+017A
ć æ " & ChrW$(&H107) & " https://www.compart.com/en/unicode/U+0107
ń ??? " & ChrW$(&H144) & " https://www.compart.com/en/unicode/U+0144
Ę ??? " & ChrW$(&H118) & " https://www.compart.com/en/unicode/U+0118
Ó ??? " & ChrW$(&HD3) & " https://www.compart.com/en/unicode/U+00D3
Ą ??? " & ChrW$(&H104) & " https://www.compart.com/en/unicode/U+0104
Ś ??? " & ChrW$(&H15A) & " https://www.compart.com/en/unicode/U+015A
Ł ??? " & ChrW$(&H141) & " https://www.compart.com/en/unicode/U+0141
Ż ??? " & ChrW$(&H17B) & " https://www.compart.com/en/unicode/U+017B
Ź ??? " & ChrW$(&H179) & " https://www.compart.com/en/unicode/U+0179
Ć ??? " & ChrW$(&H106) & " https://www.compart.com/en/unicode/U+0106
Ń ??? " & ChrW$(&H143) & " https://www.compart.com/en/unicode/U+0143

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.

2 comments on “Polish and other non-ASCII special characters in VBA and Excel’s macros”

  2. cBr
    Reply

    You don’t have to make all changes in code. This piece of code will convert Polish special characters into their Unicode equivalents in your output string:

    Dim sLPl$   'string with Polish letters
Dim sUcd$   'string with Unicode codes
Dim tLPl()    'array with Polish letters
Dim tUcd()   'array with Unicode codes

sLPl = "ę,ó,ą,ś,ł,ż,ź,ć,ń,Ę,Ó,Ą,Ś,Ł,Ż,Ź,Ć,Ń"
sUcd = "&H119,&HF3,&H105,&H15B,&H142,&H17C,&H17A,&H107,&H144,&H118,&HD3,&H104,&H15A,&H141,&H17B,&H179,&H106,&H143"

tLPl() = Application.Transpose(Split(sLPl, ","))
tUcd() = Application.Transpose(Split(sUcd, ","))
For i = 1 To UBound(tLPl)
    OutputString = Replace(OutputString, tLPl(i, 1), ChrW$(tUcd(i, 1)))
Next i

Leave a Reply