I'm a little bit confused with the referstoRange property of names.
According to msdn documentation :

Office 2003
Returns the Range object referred to by a Name object. Read-only

I have a few names whose reference formula uses offset like :

colOf100Cells = offset($a$1 , 0 ,0 , 100 , 1 ).

Whenever I try to do something like :

Code:

set somevar = wbk.names("colOf100Cells").referstorange

I get an a runtime error 1004. Somevar is of type range.

This so far happens only when the definition of the name includes offset.
I don't understand why since it's very clear what colof100cells refers or points to. If I use colOf100Cells in the worksheet, it works wherever a range is required ( e.g : in a vlookup).

I think this works for me. (though I'm in office 2010)
Are you sure that the particular name that you are referencing is a workbook level name? (in office 2003, it was difficult to make worksheet-level names inadvertantly without making copies of worksheets, so this is probably not a very helpful question...)

I think this works for me. (though I'm in office 2010)
Are you sure that the particular name that you are referencing is a workbook level name? (in office 2003, it was difficult to make worksheet-level names inadvertantly without making copies of worksheets, so this is probably not a very helpful question...)

some are workbook level names, some are worksheet level ones.
I know that, for example, when making a list you have to use a wbk level name.
In general I try to keep the names worksheet lavel. Tidier.
What difference do you think it makes ? If it' s worksheet only I specify that when using the name so i could refer
the name from wbk or wsht and it should make no difference if I use the "full name" of the name.

What I mean is that I'd use the name thus :

Code:

set somevar = wbk.names("'sheetname'!colOf100Cells").referstorange

or

Code:

set somevar = wksht.names("'sheetname'!colOf100Cells").referstorange

Maybe it has to do with that 'sheetname'! vs sheetname! ? Or when using wsht.names I should not use sheetname! ?