VBA & Macro Problems

Define a Named Range for the Active Cell in Excel VBA

Tauseeq MagsiTauseeq Magsi Nov 18, 2026 868 views

Question details

Create a named range that refers to the cell selected when a VBA macro starts.

Define a Named Range for the Active Cell in Excel VBA
Product
Microsoft Excel
Device & OS
not provided
Scenario
A macro needs to assign a workbook-level name to the currently active cell using a valid external address.
Observed behavior
The name should refer to the selected cell, with a leading equals sign and a reference that identifies the workbook and worksheet.
Before you start

Open the workbook in desktop Excel and make sure the Developer tab is available. Save it as a macro-enabled workbook (.xlsm) and enable macros only if you trust the file.

Solution 1Recommended

Create a workbook-level name for the active cell

Use this macro when the name should be available throughout the active workbook.

1
Open the VBA editor

In Excel, select Developer > Visual Basic, then choose Insert > Module.

2
Add the macro

In the module, enter: Sub NameActiveCell() ActiveWorkbook.Names.Add Name:="horo4", RefersTo:="=" & ActiveCell.Address(External:=True) End Sub

3
Select the cell and run the macro

Return to Excel, select the cell you want to name, and choose Developer > Macros. Select NameActiveCell and click Run.

4
Check the name

Open Formulas > Name Manager and find horo4. Confirm that its scope is the workbook and that Refers To points to the selected cell.

Create a workbook-level name for the active cell
How the reference works: The leading equals sign makes RefersTo a formula reference. External:=True includes the workbook and worksheet in the cell address.
Free Microsoft Office alternative

Manage spreadsheet names with WPS Office

WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open compatible Excel files and work with named ranges while moving between Office suites; VBA macro support can vary by version and environment.

Work with named ranges in WPS Spreadsheets.Open and edit Microsoft Excel formats such as .xlsx and .xls.Use a familiar spreadsheet interface for an easier transition.Use a free, lightweight alternative for everyday spreadsheet work.
microsoft office alternative - wps office

Frequently Asked Questions

Why does the VBA reference start with an equals sign?

The RefersTo argument expects a formula-style reference. Prefixing the address with "=" supplies that formula syntax.

What does External:=True add to the active cell address?

ActiveCell.Address(External:=True) returns an address that includes the workbook and worksheet as well as the cell reference.

How can I check whether the name is workbook-level or worksheet-level?

In Excel, open Formulas > Name Manager and look at the Scope column for the name.

Can I use an R1C1 reference instead?

Yes. Use RefersToR1C1:="=" & ActiveCell.Address(ReferenceStyle:=xlR1C1, External:=True) in the Names.Add call.