VBA not recognizing object references in the Hungarian language-CodePudding

This problem suddenly started occurring after years without any issues.

One of my Excel VBA programs has a worksheet name in Hungarian, and that name contains a letter not found in English or other Latin-character languages. Please note I am not referring to “Hungarian Notation” but to the language of Hungary. I’m using Windows 10 and Office 365.

When the workbook is opened, VBA code in the ThisWorkbook module executes to initialize some variables, and in doing so it refers to the name of that worksheet.

Until now this code was executed with no problem, but now it raises a run-time “Subscript out of Range.” error when that line of code is reached. Here is the line of code that errors out:

Set StartSheet = ThisWorkbook.Worksheets("Kezdőlap - Start")

(The website you are viewing may not show the offending letter as it is in Hungarian.)

Changing the first of these objects to an English name enables execution to proceed, but the same error then occurs on the next Hungarian object name encountered. The errors involve only Hungarian names having a non-Latin Hungarian character. The workbook and the program are loaded with Hungarian names, which of course I want to keep.

A related and strange phenomenon occurs when I use the Search/Replace tool in the VBA editor. When I type a Hungarian word into the Search dialogue, or paste it in from elsewhere, the unique Hungarian letter is automatically changed to a similar non-Hungarian letter. The targeted Hungarian text in the code is therefore not found.

I have tried removing and reinstalling Windows 10 and Office and have removed and reinstalled the Hungarian language in Windows settings, as well as well as through the Language tab in Excel under Options. Microsoft Support’s efforts also failed to fix this.

I can still type in Hungarian in Office apps by switching to Hungarian (using the language-selector in the task bar). Office also still performs spell checks on Hungarian text.

Thanks for any help.

CodePudding user response：

Nothing wrong with your installation. The VBA editor is very old and unfortunately, it was never updated so that it supports UTF-8 or UTF-16.
However, that doesn't mean a string cannot contain "special" characters, like in your case the sheet name. To get the information about which character is used, open the immediate window in VBA and type

for i=1 to len(activesheet.name): ? i & "=" & AscW(mid(activesheet.name, i, 1)) : next

The AscW-Function gives the numeric representation of a character. As you can see, the 5th character gives a value of 337 which is outside of the character set that the VBE can handle.

Possible workaround: Write the sheetname in VBA and use the ChrW-function for your special character. This function does the opposite of the AscW-function, it converts a number into the corresponding character:

Dim startsheetname As String
startsheetname = "Kezd" & ChrW(337) & "lap - Start"
Set StartSheet = ThisWorkbook.Sheets(startsheetname)

Alternatives:
Simply use the Worksheet Index:

Set StartSheet = ThisWorkbook.Sheets(1)

Use the codeName of a sheet. The code name is the name shown in the VBA editor. See here for a function that retrieves a sheet by its code name: https://stackoverflow.com/a/36642355/7599798