asked on

How do you access named ranges via VB when you don't know what sheet they are?

Experts,

I have a worksheet with a bunch of named ranges in it and am having trouble selecting these ranges via:

For i = 1 To activeworkbook.Names.Count
range(activeworkbook.Names(i).refersto).select
next i

The only way this works is if the sheet that the range refers to is active. This defeats the purpose of the named range! Otherwise I get "Run time error '1004' - Select method of range failed".

This is killing me because I can see the sheet name in the 'refersto', and could parse it out and activate it, but this seems wrong. I have played with using .value, .refersto, .name, but none work. But I would like to access these ranges regadless of what sheet is currentlty active.

Thanks,
Brian

R_Rajesh

Hi BrianEKramer,

you can't select or activate a range unless it happens to be in the active sheet
but you can access it no matter which sheet is active

For Each c In Range(ActiveWorkbook.Names(1).RefersTo)
MsgBox c
Next c

Range(ActiveWorkbook.Names(1).RefersTo).Interior.ColorIndex = 5

Rajesh

BrianEKramer

ASKER

Then is there an easy way to activate the sheet? For example, is there an easy way to get the sheet name where the range resides other than parsing it out of the 'refersto' and manually activating it?

Robberbaron (robr)

If the named range is unique to the Workbook it can be used globally.

ie range("test1").columns.count

if the same range name is used on multiple sheets, then each must be referred to with a sheet reference.
ie range("Sheet1!test1").columns.count

Why do you need to 'select' ? this just highlights the range.

Or do you really want the RefersToRange property, which returns a range associated with a name.

dim rngp as Range
For i = 1 To activeworkbook.Names.Count
rngp=ActiveWorkbook.Names(i).RefersToRange

rngp.select ' or whatever you want.
next i

BrianEKramer

ASKER

This doesn't work. I get 'select method of range class failed'. My code:

Set curbk = ActiveWorkbook
Dim rr As Range
For i = 1 To curbk.Names.Count
curbk.Activate
Set rr = curbk.Names(i).RefersToRange
rr.Select
Next i

SOLUTION

Robberbaron (robr)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Robberbaron (robr)

so long as you dont want to 'select' the range, you can access the data and formatting ok.

Are you sure you need to 'select' the range for your app ?
otherwise, you can access the range via the rr object above.

ASKER CERTIFIED SOLUTION

byundt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

colje

If all you want to do is access them then you can:-

'Get first cell in the range
strValue = Application.Range("myNamedRange").cells(1,1).value

'Write to a cell in the range
Application.Range("myNamedRange").cells(1,1).value = "A String"

or

'Assign contents to 2d array
Dim arrValues as Variant
arrValues = Application.Range("myNamedRange").value

BrianEKramer

ASKER

Here's some code I glommed together that seems to work. It's convoluted (see curbk.Sheets(curbk.Names(i).RefersToRange.Parent.name).Activate), and I have to think there is a better way, but it works.

Thanks for all your help. Everyone had a piece, so I'm splitting the points.
Brian

==========
Sub list_named_ranges()

Set curbk = ActiveWorkbook
curbk.Activate

For i = 1 To curbk.Names.Count
curbk.Activate
curbk.Sheets(curbk.Names(i).RefersToRange.Parent.name).Activate
curbk.Names(i).RefersToRange.Select
MsgBox curbk.Names(i).name & " " & curbk.Names(i) 'print range name and address
Next i

End If

End Sub