vba - Programmatically determine if a named range is scoped to a workbook

Question

I am attempting what I thought would be a fairly simple vba statement to test whether a named range is scoped to a workbook or a specific sheet.

---

As a test, I have created a new Excel document and added in 6 named ranges. Here is how they are layed out in the Name Manager:

 Name         |   Refers To          |   Scope
 -------------+----------------------+-----------
 rng_Local01  |   =Sheet1!$A$2:$A$16 |   Sheet1
 rng_Local02  |   =Sheet1!$C$2:$C$16 |   Sheet1
 rng_Local03  |   =Sheet1!$E$2:$E$16 |   Sheet1
 rng_Global01 |   =Sheet1!$B$2:$B$16 |   Workbook
 rng_Global02 |   =Sheet1!$D$2:$D$16 |   Workbook
 rng_Global03 |   =Sheet1!$F$2:$F$16 |   Workbook

---

I would expect that running:

For i = 1 To ThisWorkbook.Names.Count
    If ThisWorkbook.Names(i).WorkbookParameter Then Debug.Print ThisWorkbook.Names(i).Name
Next i

would result in the three Workbook scoped named ranges to be logged, however, nothing happens. There is no error. The .Names(i).WorkbookParameter evaluates to False on ALL of the named ranges and I am not sure why.

---

Looking through the Name object in the VBA help I came across ValidWorkbookParameter which looks like the ReadOnly cousin of WorkbookParameter, however using that method does NOT make any difference.

I have also tried explicitly setting ThisWorkbook.Names(i).WorkbookParameter = True, however this results in an error:

"Invalid procedure call or argument"

Despite the fact that WorkbookParameter is listed as being Read/Write

---

Can anyone shed any light onto why this isn't working as I'm expecting it too? Have I misunderstood how Name.WorkbookParameter is supposed to work? Is anyone able to get this to run successfully?

Answer 1

You can use the Parent property:

Sub Global_Local_names()
    Dim oNm As Name
    For Each oNm In Names
        If TypeOf oNm.Parent Is Worksheet Then
            Debug.Print oNm.Name & " is local to " & oNm.Parent.Name
        Else
            Debug.Print oNm.Name & " is global to " & oNm.Parent.Name
        End If
    Next
End Sub