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?

Dagger

Thanks for the very usable solutions guys... I am still curious as to the behavior of the WorkbookParameter and ValidWorkbookParameter properties of the Name object in VBA. In my mind, those properties aren't working as advertised. – Dagger

Not sure exactly what WorkbookParameter is but suspect its for Excel Services in Sharepoint and not for client Excel – Ronnieronny

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

Ronnieronny

Thanks for the solution... I had not thought of using the Parent property. I marked this one as the answer since it most directly deals with whether the named range belongs to the workbook or a sheet. – Dagger

Answer 2

I didn't manage to make the WorkbookParameter work but I found a workaround:

For i = 1 To ThisWorkbook.Names.Count
    If UBound(Split(ThisWorkbook.Names(i).Name, "!")) > 0 Then Debug.Print ThisWorkbook.Names(i).Name
Next i

The name of the local Named Range (i.e. scope to a sheet only) is formatted this way: Sheet1!NamedRange whereas the name of the global Named Range is formatted: NamedRange.

Thus, you can split on the ! and check the length of the array.

Titbit

I like the creative solution using Split – Dagger