Programmatically determine if a named range is scoped to a workbook
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
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