Define a Named Range for the Active Cell in Excel VBA
Question details
Create a named range that refers to the cell selected when a VBA macro starts.

- 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.
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.
Create a workbook-level name for the active cell
Use this macro when the name should be available throughout the active workbook.
In Excel, select Developer > Visual Basic, then choose Insert > Module.
In the module, enter: Sub NameActiveCell() ActiveWorkbook.Names.Add Name:="horo4", RefersTo:="=" & ActiveCell.Address(External:=True) End Sub
Return to Excel, select the cell you want to name, and choose Developer > Macros. Select NameActiveCell and click Run.
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 worksheet-level name for the active cell
Use a worksheet-level name when its scope should be limited to the active cell's worksheet.
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.

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.




