One of my Excel VBA programs has a worksheet name in Hungarian that 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 initializes variables. In doing so it refers to the name of that worksheet.
Until now this code executed but now it raises
run-time “Subscript out of Range.”
when that line of code is reached.
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 allows execution to proceed, but the 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 I want to keep.
A related 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 changed to a similar non-Hungarian letter. The targeted Hungarian text in the code is therefore not found.
I removed and reinstalled Windows 10 and Office.
I removed and reinstalled the Hungarian language in Windows settings, 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.
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