Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
I don't seem to be able to Google a helpful answer on this, although I'm
sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
Doug,
Why would you ever want to create a name of 255 characters? That is not a recipe for easy maintainability. -- HTH RP (remove nothere from the email address if mailing direct) "Doug Glancy" wrote in message ... I don't seem to be able to Google a helpful answer on this, although I'm sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
Bob,
Sorry, I don't think I was clear. I'm naming a range e.g., "='master data sheet'!$B$1,'master data sheet'!$I$1,'master data sheet'!$L$1" ,etc. Entering it in the Insert Name dialog, it clears itself after I hit 255 characters. So instead, I created it in code: masterdatasheet.Names.Add Name:="averaging", RefersTo:="=$B$1,$I$1 ",etc. When I look at the name in the Insert Name "Refers To" box it looks like "='master data sheet'!$B$1,'master data sheet'!$I$1,'master data sheet'!$L$1" again and cuts off after 255 characters - actually I didn't count, but I think that's how many - but the range actually contains all the cells I coded. So, it seems to be working, and it's not the most critical of worksheets, but it did make me curious and a little nervous. Thanks again, Doug "Bob Phillips" wrote in message ... Doug, Why would you ever want to create a name of 255 characters? That is not a recipe for easy maintainability. -- HTH RP (remove nothere from the email address if mailing direct) "Doug Glancy" wrote in message ... I don't seem to be able to Google a helpful answer on this, although I'm sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
Oh yes, sorry it is in the OP, I read the subject more precisely.
I think the problem may be the automatic pre-pending of 'master data sheet'!. You could shorten the name. -- HTH RP (remove nothere from the email address if mailing direct) "Doug Glancy" wrote in message ... Bob, Sorry, I don't think I was clear. I'm naming a range e.g., "='master data sheet'!$B$1,'master data sheet'!$I$1,'master data sheet'!$L$1" ,etc. Entering it in the Insert Name dialog, it clears itself after I hit 255 characters. So instead, I created it in code: masterdatasheet.Names.Add Name:="averaging", RefersTo:="=$B$1,$I$1 ",etc. When I look at the name in the Insert Name "Refers To" box it looks like "='master data sheet'!$B$1,'master data sheet'!$I$1,'master data sheet'!$L$1" again and cuts off after 255 characters - actually I didn't count, but I think that's how many - but the range actually contains all the cells I coded. So, it seems to be working, and it's not the most critical of worksheets, but it did make me curious and a little nervous. Thanks again, Doug "Bob Phillips" wrote in message ... Doug, Why would you ever want to create a name of 255 characters? That is not a recipe for easy maintainability. -- HTH RP (remove nothere from the email address if mailing direct) "Doug Glancy" wrote in message ... I don't seem to be able to Google a helpful answer on this, although I'm sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
Hi Doug,
If you define a name manually, or in code with an address, the maximum string length is theoretically 255, in practice maybe a bit less. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Define in code as a range the way you have already successfully done. There is a maximum limit to the number of non-contiguous areas. It varies depending on combination of single/multicell areas, if exceeded will raise an error for which you can trap. However there is no limit to the overall address length. Also, as you have noticed, the named range will indeed continue to work the same way as any other named range. If you have defined such a range, there is no way to manually edit the address if it's over 255. A workaround both for creating and editing is to temporarily rename the sheetname to something short, but still subject to the overall 255. For such ranges, indeed anything to do with names, the NameManager addin by Jan Karel Pieterse and colleagues is a must have: http://www.jkp-ads.com/Download.htm FWIW, I work with very large multiarea named ranges, with address strings that way exceed 255, and find them very useful for certain purposes. Regards, Peter T "Doug Glancy" wrote in message ... I don't seem to be able to Google a helpful answer on this, although I'm sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
Thanks a lot Peter - very informative. I believe I've actually downloaded
Jan's NameManager before - I guess it's time to take a look at it. I use these types of ranges on unwieldy spreadsheets, for hiding and unhiding intersecting ranges. E.g., show only the columns that are in the intersection of the Funding Source A range and the Previous Month's Draws range, if that makes sense. Thanks again, Doug "Peter T" <peter_t@discussions wrote in message ... Hi Doug, If you define a name manually, or in code with an address, the maximum string length is theoretically 255, in practice maybe a bit less. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Define in code as a range the way you have already successfully done. There is a maximum limit to the number of non-contiguous areas. It varies depending on combination of single/multicell areas, if exceeded will raise an error for which you can trap. However there is no limit to the overall address length. Also, as you have noticed, the named range will indeed continue to work the same way as any other named range. If you have defined such a range, there is no way to manually edit the address if it's over 255. A workaround both for creating and editing is to temporarily rename the sheetname to something short, but still subject to the overall 255. For such ranges, indeed anything to do with names, the NameManager addin by Jan Karel Pieterse and colleagues is a must have: http://www.jkp-ads.com/Download.htm FWIW, I work with very large multiarea named ranges, with address strings that way exceed 255, and find them very useful for certain purposes. Regards, Peter T "Doug Glancy" wrote in message ... I don't seem to be able to Google a helpful answer on this, although I'm sure it's been discussed a bunch. If I try to define a name in the Insert Name dialog, it clears the "refers to" box after (I'm guessing) 255 characters and starts over. But if I define the name through code, the range contains all the cells in the code even though they don't all show in the Insert Name "refers to" box. So, what's the best way to create a named range that's longer than 255 characters? Will the range continue to include all the cells? It seems to. Thanks, Doug Glancy |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Named range longer than 255 chars
I believe I've actually downloaded Jan's NameManager before - I guess it's time to take a look at it. If you downloaded it a while ago, make sure you have the current version if you need to edit names with address strings in excess of 255. Regards, Peter T |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to truncate list of meaningful words greater than 15 chars tomeaningful words of 8 chars. | Excel Worksheet Functions | |||
How can I get underlying range when Series Formula is longer than 255characters? | Charts and Charting in Excel | |||
How do I delete the name for a named range i no longer require? | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
inserting a named range into new cells based on a named cell | Excel Discussion (Misc queries) |