How to Fix Non-Contiguous Named Range VBA Errors in German Excel
Question details
The user needs to resolve an issue where Excel VBA crashes when trying to reference scattered, non-contiguous named ranges in locales that use semicolons as list separators.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Running a VBA macro that uses ThisWorkbook.Names("name").RefersToRange on a non-contiguous range in a German regional setting.
- Observed behavior
- The VBA code fails or triggers an error because the RefersToRange property cannot correctly parse non-contiguous ranges when the system locale uses semicolons for list separators instead of commas.
Before modifying your VBA code, check your Windows regional settings to confirm if your list separator is set to a semicolon instead of a comma, which is the root cause of this localization parsing issue.
Use Application.Range with the RefersTo Property
Bypass the localized RefersToRange bug by parsing the range string directly without the leading equals sign.
Because the RefersToRange property has an underlying bug with localized list separators, you can extract the raw reference string using the RefersTo property and feed it into Application.Range instead.
Open the VBA Editor by pressing Alt + F11 and find the line of code using ThisWorkbook.Names("name").RefersToRange that is causing the crash.
Instead of using RefersToRange, retrieve the named range string using N.RefersTo. This returns the reference formula starting with an equals sign.
Use the Mid function to remove the leading equals sign from the string. For example: Mid(N.RefersTo, 2).
Feed the cleaned string directly into the Application.Range method. Update your code to look like this: Set Dest = Application.Range(Mid(N.RefersTo, 2)).

Try WPS Office for Seamless Macro and Spreadsheet Management
Since this non-contiguous named range bug stems from Microsoft Excel's handling of regional settings, consider switching to WPS Office. WPS Spreadsheet offers excellent compatibility with Excel files, robust macro support, and a highly stable environment for all your data tasks without expensive subscriptions.
- 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
- 2. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the named ranges.
- 3. Run your macros: Enable macro execution when prompted to safely run your scripts in a stable environment.

Frequently Asked Questions
Why does this VBA error only occur with non-contiguous ranges?
Contiguous ranges defined by a single block (like A1:C3) do not use list separators. Non-contiguous or scattered ranges (like A1, C1) rely on list separators, which clash with locales like German that use semicolons instead of commas.
Does this workaround apply to other non-English regional settings?
Yes. Any Windows regional locale that uses a comma as a decimal separator and a semicolon as a list separator (such as French, Spanish, or Italian) can encounter this exact RefersToRange parsing bug.
How do I change the default list separator in Windows to fix this globally?
You can change the list separator by navigating to Windows Control Panel > Region > Additional settings. Under the 'Numbers' tab, locate the 'List separator' field and change it from a semicolon (;) to a standard comma (,).




