How to View Named Range Scope in Excel with VBA
Question details
The user wants to identify whether Excel named ranges are scoped to the entire workbook or limited to a specific worksheet, utilizing both manual methods and VBA macros.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Reviewing, auditing, or managing defined names in a spreadsheet for automated reporting or formula writing.
- Observed behavior
- Users need a reliable way to differentiate between local (worksheet-level) and global (workbook-level) named ranges to prevent reference errors and naming conflicts.
Before inspecting named ranges with VBA, ensure you have enabled the Developer tab in Excel and saved your file as an Excel Macro-Enabled Workbook (.xlsm).
View Named Range Scope Using the Name Manager
The built-in Name Manager is the quickest way to visually review all defined names and their respective scopes without writing any code.
The Name Manager provides a comprehensive list of all named ranges in your file. It explicitly distinguishes between global range names (Workbook scope) and local range names (Worksheet scope).
Press the keyboard shortcut Ctrl + F3, or navigate to the Formulas tab on the Excel ribbon and click on 'Name Manager'.
In the Name Manager dialog box, locate the 'Scope' column. If it says 'Workbook', the name is global. If a specific worksheet name is listed, the name is local to that sheet.
Look at the 'Refers To' column at the bottom to verify the exact cells or formulas the named range is pointing to.
Use VBA to Identify and Report Named Range Scope
For workbooks containing a massive number of named ranges, a VBA macro can automate the inspection process by checking the properties of each Name object.
Paste a List of Named Ranges into a Worksheet
You can generate a physical list of all named ranges and their references directly onto your spreadsheet for easy auditing or printing.
View and Manage Named Ranges with WPS Spreadsheet
WPS Spreadsheet provides a highly compatible and intuitive Name Manager, fully supporting VBA macros to automate your named range auditing tasks. Enjoy a powerful spreadsheet experience that perfectly handles complex data structures.
- 1. Open your Workbook: Launch WPS Spreadsheet and open your existing Excel workbook containing the named ranges.
- 2. Access the Name Manager: Navigate to the Formulas tab on the top ribbon and click on 'Name Manager'.
- 3. Inspect Scopes: Use the dialog box to view the 'Scope' column for a clear breakdown of workbook-level and worksheet-level names.
- 4. Run VBA Macros: Go to the Developer tab, open the VBA Editor, and run your named range inspection macros exactly as you would in Microsoft Excel.
Frequently Asked Questions
What is the difference between workbook-level and worksheet-level scope?
A workbook-level named range can be referenced directly from any sheet within the entire file. A worksheet-level (local) name is restricted to its specific sheet; referencing it from another sheet requires adding the specific sheet name as a qualifier in the formula.
Can I change the scope of an existing named range?
No, Excel does not allow you to directly edit or change the scope of a named range once it has been created. To change the scope, you must delete the existing name in the Name Manager and recreate it with the desired scope.
Why are some named ranges missing from the Name Manager?
Some named ranges might be hidden by VBA scripts (using the Visible = False property). You will need to use a VBA macro to change their visibility status back to True before they will appear in the standard Name Manager window.
