creating a named range and referring to its cells

[twd000]

here is the snippet of code I'm using

    'add a named range ("output_response_sheet_dv_list") to the worksheet
    ActiveWorkbook.Sheets("hidden_named_ranges").Names.Add Name:="output_response_sheet_dv_list", RefersTo:="=OFFSET(hidden_named_ranges!$A$1,1,0,num_design_sheets,1)"
    'fill "output_response_sheet_dv_list" with allowed sheet names
    For i = 1 To num_design_sheets
        ActiveWorkbook.Sheets("hidden_named_ranges").Range("output_response_sheet_dv_list").Cells(i) = allowed_sheet_name(i)
    Next i

it fails on the red line

as you can see, according to Excel, the named range exists in the proper sheet. However I'm not able to access (populate) the cells in that range

I've attached the workbook it it helps

[shg]

Looks like num_design_sheets is undefined.

[twd000]

at the time the error is raised, num_design_sheets is defined as type Long, value=2 (it is under the tally_design_sheets breakout in the Locals window)

[shg]

A defined range can't use a VBA variable as part of its definition. It has no visibility.

[twd000]

ok, that's what I'm seeing. If I hard-code the #2, instead of num_design_sheets, it works.

How do I workaround this limitation? It needs to be a variable so that the named range will be redefined each time based on the length num_design_sheets

[shg]

Create a named constant on the same sheet.

BTW, if a named range is (correctly) defined, you'll see marching ants around it when you select its 'refers to' in the define name dialog.

Edit: You realize you only need a named range if formulas refer it it; it can just be a VBA range variable otherwise, right?

Also, you can index outside a range in VBA.

[twd000]

having trouble getting data validation to work in Excel. I create a named range in one sheet using VBA code, then I want to define a data validation on another sheet that refers to that named range as the List in the Data Validation dialog box.

The problem is with the named range definition

when I manually define the range it works fine. It also does not specify which sheet contains this named range.

but when I use this VBA code to define the range it doesn't work

ActiveWorkbook.Sheets("hidden_named_ranges").Names.Add Name:="output_response_sheet_dv_list", RefersTo:="=OFFSET(hidden_named_ranges!$C$1,2,0,hidden_named_ranges!$C$2,1)"

as you can see I fully qualified the named range by declaring that it appears in the sheet "hidden_named_ranges"

what is the significance of the second column in the dialog box "Define Name"? I thought it meant that the name appears in more than one worksheet, which is not true in my case.

In order to reference the data validation list to the named range "output_response_sheet_dv_list" which appears on another sheet "hidden_named_ranges", do I need a workbook-level or worksheet-level named range? How do I create that in VBA code?