Named range longer than 255 chars

[Doug Glancy]

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

 

[Bob Phillips]

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]

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]

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)

 

[Peter T]

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]

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]

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