Excel VBA Named Range Properties

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.

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist
- Microsoft Specialist
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *