Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    Hi All,

    The 255 character limit in the Name Manager has been resolved. Now it is possible to edit names that have a refersto string longer than 255 characters.

    Download at:

    http://www.jkp-ads.com/NameManager.zip

    Description at:

    http://www.jkp-ads.com/Download.htm#NameManager
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    I don't know if this is an issue only with XL9, but this new version (build 460) still does not display the full refersto if larger than 255. I always thought this limit was due to VB/Excel getting the range, the range itself is fine however.

    I created a long refers to by creating a name and selecting different cells in the sheet so the refersto was between 200 -255. This works fine. If I change the sheet name (adding more characters) so the refersto has to become larger than 255, excel has no problem with this (I imagine the references are stored differently, perhaps not even using the "sheetname") the defined name is still valid.

    Using Insert- name define however, does not display the entire range, getting the refersto property in VB or even using the name manager, does not pull out the entire name, but the name is still valid (even though the refersto portion does not display it all).

    If/when the sheetname is shortened to again be less than 255 total, the refersto is correct: none of the info was truncated, it just seems "inaccessible" to us.

    it also does the same if you create a name linked to a workbook. When the workbook is open, the name does not create the whole path, but once it is created and the linked workbook is closed the whole path of the file is used and excel will only show a max of 255 chars. Your program seems to get the same "truncated" info

    Excel either can handle names larger than 255 or it stores them differently when composed of sheetnames or workbooks names, and the "translated" string is what is handled and gives the problems.

    Steve

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    OK, thanks Steve. I see this needs further testing.

    Any other comments/compliments <img src=/S/smile.gif border=0 alt=smile width=15 height=15> ?
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    To be honest, I haven't really tried to use it for much. I usually do not handle a lot of named ranges and haven't had the need to really play with it in my work.
    I find I don't use a lot of utilities.

    I only looked at this stage, since I was curious how you could read more than 255 chars, since I never had any luck with it. It seems you have a problem also (though I think it is most likely completely and excel/vb issue)

    Steve

  5. #5
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    It seems the 255 limit is one of those leftovers from the old days. There are more places in Excel that show them, like when copying a while sheet that contains cells with more than 255 characters.

    I'm afraid XL has beaten me this time, no workaround for this one.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    You shouldn't feel bad. Excel itself is beaten by this. It can't even display the names correctly!

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Name Manager: fixed 255 char limitation (97, 2000,xp,2003)

    I don't. It is just that up till now I have been able to work around a number of bugs in the Name object (see the manual of the Name Manager).

    One of the worst is that the ReferstoLocal does NOT accept the listseparators and decimal separators as set in control panel, even though reading this property does show them correctly. I had to build an entire parsing routine that checks the formula and replaces all localised separators with the default english ones, taking into account that within text strings nothing has to be done and that an Array of values has different (row and column) separators than the parameters of a function. doh.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •