VBA: Referencing a named range

[Guraknugen]

Let's say I have a named range like this:
Name: "MyRange"
Range address: "A2:C101"
Sheet name: "MySheet"

This is the only way so far that I could reference that name:

    Dim oRange As Range
    Set oRange = Worksheets("MySheet").Range("MyRange")

So it works, but since Excel already knows that "MyRange" is actually "MySheet!A2:C101", Worksheets("MySheet") seems a little bit redundant.
I suppose there's a better way, but so far the few things I tried has failed, such as:

Set oRange = Range("MyRange") ' Yields a runtime error no. 1004.
Set oRange = ThisWorkbook.Names("MyRange").RefersToRange ' 1004 again.

For reference, this is very easy in LibreOffice Calc:
vba]oRange = ThisComponent.NamedRanges.getByName("MyRange").getReferredCells()
No need to tell it what sheet it's on, it's already in the range name definition, just like in Excel.

 

[JGordon11]

maybe

Set oRange = [MyRange]

or just use [MyRange] where you are using oRange

 

[Alex Blakenburg]

This should work but it's risky. If you have the same name set at both a workbook variable and a worksheet variable, you will get a different result depending on which sheet is active.
The name scoped to the activesheet will take precedence over the workbook name.

    Set oRange = Range(ActiveWorkbook.Names("MyRange").RefersTo)

Note: If you make your range a Table then the name can only be used once and is scoped to the workbook, so Range("MyTableName") or Range("MyTableName").ListObject works

 

[Guraknugen]

maybe

Set oRange = [MyRange]

or just use [MyRange] where you are using oRange

Oh, that 's a new one to me, and it seems to work.
Thanks!

 

[rlv01]

So it works, but since Excel already knows that "MyRange" is actually "MySheet!A2:C101", Worksheets("MySheet") seems a little bit redundant.
I suppose there's a better way, but so far the few things I tried has failed, such as:

Set oRange = Range("MyRange") ' Yields a runtime error no. 1004.
Set oRange = ThisWorkbook.Names("MyRange").RefersToRange ' 1004 again.

There are two kinds of names, workbook level names , and worksheet level names. For a workbook level name, the above code should work no problem. The above error usually occurs because "MyRange" was defined as a worksheet level name, and the code was executed when another worksheet was the active sheet. If you added code to make the correct worksheet active

    Worksheets("MySheet").Activate
    Set oRange = Range("MyRange") ' Yields a runtime error no. 1004.
    Set oRange = ThisWorkbook.Names("MyRange").RefersToRange ' 1004 again.

or qualified your references to the correct worksheet.

    Set oRange = Worksheets("MySheet").Range("MyRange") ' Yields a runtime error no. 1004.
    Set oRange = ThisWorkbook.Worksheets("MySheet").Names("MyRange").RefersToRange ' 1004 again.

Then it should work w/o error.