Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Programmatically determine if a named range is scoped to a workbook

Tags:

vba

excel-2007

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?

like image 870
Hari Seldon Avatar asked Dec 28 '11 13:12

Hari Seldon


People also ask

Can you reference named ranges in VBA?

WorkSHEET Specific Named Range The benefit is that you can use VBA code to generate new sheets with the same names for the same ranges within those sheets without getting an error saying that the name is already taken.

How do you reference a named range in another workbook?

Select the cell or cells where you want to create the external reference. Type = (equal sign). Switch to the source workbook, and then click the worksheet that contains the cells that you want to link. Press F3, select the name that you want to link to and press Enter.

How do you reference a named range in Excel?

Just select the name of interest in the Excel Name Manager, and type a new reference directly in the Refers to box, or click the button at the right and select the desired range on the sheet. After you click the Close button, Excel will ask if you want to save the changes, and you click Yes. Tip.


1 Answers

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
like image 94
Charles Williams Avatar answered Oct 18 '22 21:10

Charles Williams