An issue when generating text with national symbols using VBA in Excel 2019

Question

I am using some VBA add-in to generate text from numbers, that text contains national Lithuanian, German or any other national symbols. This used to work fine with the previous versions of Excel so far, but the system has now been upgraded from scratch and it does not seem to work well with Excel 2019.

In VBA, the code looks fine and you can see the national symbols like "š" (well after setting the locale to Lithuanian, before they were appearing as multiple strange symbols):

, however when in excel the functional is called, the output has just � instead of any national symbol. All of the national symbols are marked with the same � sign in Excel 2019. But you can write manually national symbols just fine in Excel, just what comes out of VBA is garbage missing those symbols.

It looks like this in a cell:

Trys �imtai �, 00 ct

Does anybody know how to fix this and make them appear normal with how they are visible within the code?

A few other observations are that when copying and pasting the code from the VBA Editor to Notepad++ the national symbols get lost as well and they appear as different strange symbols. Also when trying to use the find function in the VBA Editor window to find anything with the national symbols - no results are ever found as long as you don't remove the national symbols...

Answer 1

In my understanding, support for "international" ("non-Latin") characters is determined by your Windows Regional Settings, not by Excel or by the VBA IDE.

Since I'm in Canada I can't easily test on my end to verify this, but interestingly, your example character of š displays the opposite of how it shows for you: it renders properly on the worksheet but as gibberish in VBA.

Incidentally I determined the Unicode id number for š (Unicode 353) by pasting the symbol into cell A1 and then in another cell using formula =UNICODE(A1). The opposite function (to return a character from it's code) is UNICHAR. The VBA equivalents are AscW and ChrW.

I was aware that the VBA IDE can be made to display "international" characters by adjusting Windows Regional Settings (like discussed here), but I didn't realize any setup would make them not render on the spreadsheet since for me, most Unicode characters display properly on a worksheet.

I assume the characters don't work for you in other applications besides Excel. If that's not the case, and this problem is specific to Excel (or if you can't otherwise get it figured out by adjusting Windows Regional Settings), then I'd suggest contacting Microsoft Tech Support.

More information is here or here or in this search might also be helpful.

Answer 2

hi i had same problem in latvian just uncheck utf - 8 beta in regional language settings for non unicode enter image description here