Actuarial Outpost
 
Go Back   Actuarial Outpost > Actuarial Discussion Forum > Software & Technology
FlashChat Actuarial Discussion Preliminary Exams CAS/SOA Exams Cyberchat Around the World Suggestions

CANADIAN ACTUARIAL JOBS

Reply
 
Thread Tools Display Modes
  #1  
Old 03-10-2011, 10:57 AM
actuwannabemtl actuwannabemtl is online now
Member
 
Join Date: Jan 2008
Posts: 3,329
Default VBA/excel : name.referstoRange , offset

Hi everyone,

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).

Anyone knows of a workaround for this ?

Thanks,

Last edited by actuwannabemtl; 03-10-2011 at 11:08 AM..
Reply With Quote
  #2  
Old 03-10-2011, 12:34 PM
dumples dumples is offline
Member
 
Join Date: Sep 2003
Posts: 1,159
Default

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...)

Last edited by dumples; 03-10-2011 at 12:56 PM..
Reply With Quote
  #3  
Old 03-10-2011, 01:02 PM
actuwannabemtl actuwannabemtl is online now
Member
 
Join Date: Jan 2008
Posts: 3,329
Default

Quote:
Originally Posted by dumples View Post
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! ?

Thanks,

Last edited by actuwannabemtl; 03-10-2011 at 01:07 PM..
Reply With Quote
  #4  
Old 03-10-2011, 01:30 PM
dumples dumples is offline
Member
 
Join Date: Sep 2003
Posts: 1,159
Default

First, kudos to you for scoping your names correctly.

Tell me what happens when you run this code:
Code:
Sub Test()
    Dim wb As Workbook
    Set wb = Workbooks.Add
    wb.Names.Add Name:="OffsetName", RefersToR1C1:="=OFFSET(Sheet1!R1C1,0,0,55,1)"
    msgbox wb.Names("offsetName").RefersToRange.Address
End Sub
Reply With Quote
  #5  
Old 03-10-2011, 01:33 PM
dumples dumples is offline
Member
 
Join Date: Sep 2003
Posts: 1,159
Default

In your original example you said:
colOf100Cells = offset($a$1 , 0 ,0 , 100 , 1 )

Is that the real formula or did you just type it as short hand here on the AO? Is there no worksheet reference in the sheetname?
Reply With Quote
  #6  
Old 03-10-2011, 02:30 PM
SpaceActuary's Avatar
SpaceActuary SpaceActuary is offline
Member
 
Join Date: Apr 2010
College: Drake U
Posts: 552
Default

Did you try this?
Code:
set somevar = wksht.names("colOf100Cells").referstorange
I think that may have been what you indicated as another suggestion...
__________________
And all this science, I don't understand; It's just my job five days a week...a rocket man, a rocket man...

"I'm from Iowa. I only work in space." - Adm. James T. Kirk
Reply With Quote
  #7  
Old 03-11-2011, 12:09 PM
actuwannabemtl actuwannabemtl is online now
Member
 
Join Date: Jan 2008
Posts: 3,329
Default

Sorry for the delay,
I will try a few tests to see if this all has to do with the scope of the named
range and not really offset. Will keep you posted !
Reply With Quote
  #8  
Old 03-17-2011, 11:14 AM
actuwannabemtl actuwannabemtl is online now
Member
 
Join Date: Jan 2008
Posts: 3,329
Default

Hi,
I tried this out in the immediate window. Where I got a runtime error I wrote ERROR. Can anyone understand why everything works except trying to actually access the returned range ?

Code:
 

? activeworkbook.names(5).refersto
=OFFSET( Sheet1!$J$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1, 1)
? activeworkbook.names(5).name
Sheet1!rev
? activeworkbook.names(5).referstorange.Rows.Count
ERROR
?activesheet.name
Sheet1
?activesheet.names(5).name
Sheet1!rev
?activesheet.names(5).refersto
=OFFSET( Sheet1!$J$1, 1, 0, COUNTA(Sheet1!$A:$A) - 1, 1)
?activesheet.names(5).referstorange.rows.count
ERROR
Reply With Quote
  #9  
Old 03-17-2011, 02:57 PM
SpaceActuary's Avatar
SpaceActuary SpaceActuary is offline
Member
 
Join Date: Apr 2010
College: Drake U
Posts: 552
Default

What do you get when you execute these statements in the immediate window?

Code:
?application.worksheetfunction.counta(Sheets("Sheet1").Cells.Columns(1))

?activesheet.names(5).referstorange.address
__________________
And all this science, I don't understand; It's just my job five days a week...a rocket man, a rocket man...

"I'm from Iowa. I only work in space." - Adm. James T. Kirk
Reply With Quote
  #10  
Old 03-18-2011, 11:37 AM
actuwannabemtl actuwannabemtl is online now
Member
 
Join Date: Jan 2008
Posts: 3,329
Default

I changed sheet1 to DATA in the meantime .
Here is what I get, using the same name :

Code:
?activesheet.names(5).refersto
=OFFSET( DATA!$J$1, 1, 0, COUNTA(DATA!$A:$A) - 1, 1)
?application.worksheetfunction.counta(Sheets("DATA").Cells.Columns(1))
 946 
?activesheet.names(5).referstorange.address
RUN TIME ERROR 1004
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off


All times are GMT -4. The time now is 11:22 AM.


Powered by vBulletin®
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
*PLEASE NOTE: Posts are not checked for accuracy, and do not
represent the views of the Actuarial Outpost or its sponsors.
Page generated in 0.27254 seconds with 7 queries