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?

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. — Hari Seldon
– Hari Seldon, Commented Dec 28, 2011 at 15:32
Not sure exactly what WorkbookParameter is but suspect its for Excel Services in Sharepoint and not for client Excel — Charles Williams
– Charles Williams, Commented Dec 29, 2011 at 0:21

Charles Williams · Accepted Answer · 2011-12-28 15:01:44Z

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

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.

JMax · Accepted Answer · 2011-12-28 15:04:26Z

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.

Collectives™ on Stack Overflow

Programmatically determine if a named range is scoped to a workbook

2 Answers 2

1 Comment

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Linked

Related