Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    lost names (Excel2007)

    Has anybody else had any problems with named ranges in Excel2007?

    I have a number of Excel2007 xlsm files which make significant use of named ranges.
    Every now and then, when opening a file that has previously worked OK, problems have occurred which have been identified as due to expected named ranges no longer being 'defined' i.e. are missing from the file. Gone. Lost without a trace. Apparently.

    It doesn't seem to be consistent.
    Closing and Opening the same file - the 'missing' name is now back.
    It happens with different named ranges i.e. not always the same one.

    I've also noticed some problems with when I've tried to redefine the specific address range assigned to a name using Excel2007's name manager.
    The updated range assignment appears in the name manager's list, but when I select that named range from the name dropdown next to the formula bar it selects the previous assigned range not the new one listed in the name manager.

    My life is going to be much more tedious if I can't rely on named ranges working properly in Excel2007.

    It's not specific to my own PC - similar issues occurred when the same files were loaded on a different PC.
    (Same latest version of Excel2007 on both)

    Has anyone else had issues like this????

    zeddy






    It's as if the file load into Excel2007

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lost names (Excel2007)

    If you search Google Groups for "excel 2007" defined names disappear you'll find that this problem occurs elsewhere too, but there doesn't appear to be a satisfactory explanation or solution.

  3. #3
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: lost names (Excel2007)

    Thanks for the tip Hans.

    I did a quick search (using your "excel 2007" defined names disappear rather than my "probelms with Excel2007 named ranges") so I found some others reporting similar issues.
    I am not alone!

    It's a bit nasty not knowing what is causing this.

    It seems as if the bit that loads in the named range definitions doesn't quite finish it's job.

    Restarting sometimes helps.
    Occasionally, after loading the file and enabling macros, I wait a bit before doing anything to let Excel2007 'catch it's breath".
    Perhaps it's a bit like the 'VBA baggage' problem that code cleaner fixes.

    Or maybe it's just that the Excel2007 name manager isn't such a good name manager after all.
    I preferred having a name manager as a separate add in with Excel2003 - didn't have any of these issues with names in 2003.

    I'm thinking of adding a 'missing name detector' to gather more info on the problem:
    I shall add a sheet for documenting the range names used in a file.
    2 buttons that will list the names in two sections of the sheet.
    I'll click the first button to get my start list of names when everything is working fine.
    Then, when I get a debug message when VBA encounters an expected range name that's now gone AWOL, I'll do a run reset and then click the second button to list the 'current' names alongside. I can then use cross-checking formulas to determine which of the original names can no longer be 'found'.
    What do you think????

    zeddy

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

    Re: lost names (Excel2007)

    I haven't heard of problems like these before.

    Would you be willing to send me the workbook in question? I'd like to take a look at it.

    NB: You can still use my Name Manager addin, even though Excel 2007 has one doesn't mean mine does not work <img src=/S/smile.gif border=0 alt=smile width=15 height=15>. I have an Excel 2007 version available here:
    http://www.jkp-ads.com/officemarketplacenm-en.asp
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: lost names (Excel2007)

    Hi Jan

    And the answer of course is that the workbook contains extremely sensitive client commercial information.

    But a simple example of what has happened is:
    I have a small 'toggle columns' graphic image which simply hides and shows a specified range of columns.
    A trivial show/hide facility.
    The routine checks to see what the current visibilty is of a specific column within the required range.
    It uses a named cell in a column heading for this purpose.
    It then changes the visibilty setting of the required (named) range to the opposite setting.
    Works a treat.
    Except when it doesn't.


    The reported problem could easily be fixed in this particular case by referring to explicit cell addresses rather than named ranges.
    But we like named ranges because they allow us to alter the spreadsheet layout structure without having to constantly update or revise the VBA code.

    Of course, with Excel2007 you have to be careful with your naming.
    As you know, you can use names like TAX2009 in Excel2003, but this can now refer to a specific cell in Excel2007.
    So the problem isn't related to poor choice of name.

    It really is a bit of concern.
    Because it seems unpredictable and not necessarily repeatable.

    But I shall use your add-in.
    I have more confidence in yours than Microsoft's.

    zeddy

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: lost names (Excel2007)

    Could be very useful...

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

    Re: lost names (Excel2007)

    Can't you remove the confidential stuff? I really would like to be able to troubleshoot this stuff and potentially report it to MSFT.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: lost names (Excel2007)

    Hi Jan

    I will try and blank out all of the confidential stuff.
    It may take a while.
    I really do appreciate your interest and will try and get you a sample as soon as possible.

    zeddy

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

    Re: lost names (Excel2007)

    Thanks!
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  10. #10
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: lost names (Excel2007)

    Possible breakthrough...

    zeddy, were using the VBE Tools add-in by any chance? It occurred to me that the problem only started after I loaded that add-in. I've unloaded it now, and so far it hasn't happened again. But it's early days yet...

    The add-in I'm talking about is Stephen Bullen's (I think). In any event, it was on the CD that came with the (very excellent) book, "Professional Excel Development". And to be fair to the author(s), the book predates Excel 2007. I just got used to the nudge utility where you could move controls on a userform(!).

    I'll post back if the problem recurs for me.

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

    Re: lost names (Excel2007)

    You might want to tell the authors of this. Did you disable any other addins? I can't imagine how that addin would affect range names.
    Good to know though, I've had some magic disappeaing of range names myself lately and I use the same addin.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  12. #12
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: lost names (Excel2007)

    Hi Colin

    No, I wasn't using that add-in.

    I have a laptop with a 'pure' Excel2007 running under Vista.
    I have another laptop running Excel2007 using MojoPac.
    The office have desktops with Excel2007 - no add-ins to my knowledge.

    I'm still looking out.

    zeddy

  13. #13
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    NJ, USA
    Posts
    239
    Thanks
    2
    Thanked 1 Time in 1 Post

    Re: lost names (Excel2007)

    I had just found at the end of last week that it can't be the add-in after all. It is continuing to happen even after I removed the add-in.

    My new theory is that it's somehow related to which format files are saved in, and/or whether you're in Compatability mode or not. For me the file where it's occurring changes frequently, but I've only started to have the problem relatively recently. I switched to Excel 2007 about 4 months ago, and for the first 3 months never had a problem. Recently, though, I have started to save the file in both formats (the xlsm version is less than half the size and so opens quicker).

    Does that tie in with anything you're seeing? My workbook has a total of 41 global range names and about 120 worksheet level names. And to date it's only the global range names that are going AWOL (or at least they are the only ones I'm aware of...!). Curiously it's the same range names that go missing - but I can't see any rhyme or reason as to what makes them special. Some are single cells, some are not.

    For now I'm going back to just saving in xls format. I'll post back if I find out anything.

  14. #14
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts

    Re: lost names (Excel2007)

    Hi Colin

    It may well be related somhow to the file format and saving history.
    Apart from running these files directly in Excel2007, I seem to recall that one xlsm file may have been loaded into Excel2003 (using the 2007 compatibility mode add-in), edited and saved and then passed back to the Excel2007 environment.

    I'm still investigating.

    zeddy

  15. #15
    WS Lounge VIP
    Join Date
    Mar 2002
    Location
    Newcazzle, UK
    Posts
    2,832
    Thanks
    136
    Thanked 484 Times in 461 Posts
    [quote name='zeddy' post='758075' date='09-Feb-2009 21:54']Hi Colin

    It may well be related somhow to the file format and saving history.
    Apart from running these files directly in Excel2007, I seem to recall that one xlsm file may have been loaded into Excel2003 (using the 2007 compatibility mode add-in), edited and saved and then passed back to the Excel2007 environment.

    I'm still investigating.

    zeddy[/quote]


    Hi All

    If you have been following the history of the "lost range names" issue in Excel 2007 you may be interested in the following:

    http://support.microsoft.com/kb/968863/en-us

    Article ID: 968863 - Last Review: June 10, 2009 - Revision: 3.0
    Description of the Excel 2007 hotfix package (Excel.msp, Xlconv.msp, Graph.msp): April 30, 2009
    Hotfix Download Available

    SUMMARY
    This article describes the Microsoft Office Excel 2007 issues that are fixed in the hotfix package that is dated April 30, 2009.

    You have an Excel workbook that contains defined names. The names may be silently removed during save.

    '--------------
    Well, thank you Microsoft.
    That explains a lot.

    The hotfix is 18.9MB download.
    But you need to have SP2 first. That's a 290MB download.

    I thought I'd share this with you.

    zeddy

Page 1 of 2 12 LastLast

Posting Permissions

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