This Excel VBA tutorial explains some common Properties of Name Object (Named Range), and demonstrate how to replace named Range with formula.
You may also want to read:
Excel find all external links and broken links in workbook
Excel VBA Named Range Properties
In Excel spreadsheet, we can define Named Range under Name Manager (Formulas > Name Manager)
In the below screenshot, I have defined a Named Range called testNameRng, which refers to Cell A1 in Worksheet1. Currently A1 contains value Text in A1.
In Excel VBA, each defined Named Range is a Name Object, which has about 30 Properties. In this post I will demonstrate several Properties that I think are useful.
VBA Named Range Properties
| Property | Explanation | Example | Result |
| Name | Return Name string of the Named Range | Names(“testNameRng”).Name | testNameRng |
| RefersTo | Return the formula that the Named Range refers to | Names(“testNameRng”).RefersTo | =Sheet1$A$1 |
| Value | Return the the formula string that the Named Range refers to | Names(“testNameRng”).Value | =Sheet1$A$1 |
| RefersToRange | Return the Range object that the Named Range refers to | Names(“testNameRng”).RefersToRange | Text in A1 |
| Comment | Return the Comment of Named Range | Names(“testNameRng”).Comment | This Named Range refers to Cell A1 |
| Delete | Delete Named Range | Names(“testNameRng”).Delete |
Loop through Named Range in VBA
Similar to looping through a Range Object, for Named Range, we loop through Name Object.
The below Procedure loop through all Named Range in the Workbook and display each name in MsgBox.
For Each nameRng In Names MsgBox (nameRng.Name) Next
Replace Named Range with Formula
This Macro loops through all used Range with formula and then look for text that matches the Named Range’s name, and then replace the Named Range’s name with actual formula.
Public Sub replaceFormula() For Each ws In ActiveWorkbook.Worksheets For Each Rng In ws.UsedRange If Rng.HasFormula Then For Each namerng In Names If InStr(CStr(Rng.Formula), namerng.Name) > 0 Then Rng.Formula = Replace(Rng.Formula, namerng.Name, Replace(namerng.RefersTo, "=", "")) Exit For End If Next namerng End If Next Rng Next ws End Sub
Using testNameRng as an example. If Cell A2 contains formula
=testNameRng & " testing"
After I run the Macro, A2 would become
=Sheet1!$A$1&" testing"
Note that this Macro has a limitation, you cannot give Named Range similar names.
For example, you cannot create a Named Range called testNameRng and then name another called testNameRng2, because testNameRng is contained in testNameRng2, you have to give a different name.
