VBA & Macro Problems

How to Fix Non-Contiguous Named Range VBA Errors in German Excel

Muhammad TalhaMuhammad Talha Nov 2, 2026 868 views

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.

How to Fix Non-Contiguous Named Range VBA Errors in German Excel
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 you start

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.

Solution 1Recommended

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.

1
Locate the failing VBA code

Open the VBA Editor by pressing Alt + F11 and find the line of code using ThisWorkbook.Names("name").RefersToRange that is causing the crash.

2
Extract the RefersTo string

Instead of using RefersToRange, retrieve the named range string using N.RefersTo. This returns the reference formula starting with an equals sign.

3
Strip the equals sign

Use the Mid function to remove the leading equals sign from the string. For example: Mid(N.RefersTo, 2).

4
Pass to Application.Range

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)).

Use Application.Range with the RefersTo Property
Workaround Limitations: While this method reliably selects the scattered ranges and allows your macro to continue running, the underlying localization bug with the RefersToRange property in Microsoft Excel remains unresolved.
Free Microsoft Office alternative

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. 1. Download WPS Office: Visit the official WPS Office website and download the free installation package.
  2. 2. Open your macro-enabled workbook: Launch WPS Spreadsheet and open your existing .xlsm file containing the named ranges.
  3. 3. Run your macros: Enable macro execution when prompted to safely run your scripts in a stable environment.
Highly compatible with Microsoft Excel (.xlsx, .xlsm) formatsReliable execution of standard VBA macros and named rangesFree and lightweight, loading large spreadsheets instantlyFamiliar user interface requiring zero learning curve
microsoft office alternative - wps office

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 (,).