lost names (Excel2007)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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 . I have an Excel 2007 version available here:
http://www.jkp-ads.com/officemarketplacenm-en.asp%5B/url%5D

Reply | Quote

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

Reply | Quote

Can’t you remove the confidential stuff? I really would like to be able to troubleshoot this stuff and potentially report it to MSFT.

Reply | Quote

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

Reply | Quote

Thanks!

Reply | Quote

Could be very useful…

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

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

Reply | Quote

Thanks!!!

Reply | Quote

Zeddy: The mskb article about the hotfix says you need SP1, not SP2.

Reply | Quote

Hi Folks

Has this issue progressed at all? I’ve been developing a model in Excel 2007 that makes extensive use of Defined Names to store repetitive formulas and/or as means of keeping functions in cells reasonably easy to read. I’ve used this approach extensively in the past without any issues, albeit in versions of Excel prior to 2007.

While I am working in Excel 2007, because of the client I am working with I need to deliver my model in Excel 1997-2003 compatible format and I think this is the root cause of these issues.

The situation I now have is that doing a File Save As (Into an Excel 1997-2003 format file) will now routinely remove the same 4 Defined Names. i.e. They are there but have are set to “=#VALUE!”. The problem does not occur if I File Save As into a 2007 format version of the file.

Other observations. The 1997-2003 format file is about 2Mb, the 2007 version is considerably smaller (as you would expect) at 271Kb. OK. The working 1997-2003 format of the file is 2,313 KB. The crocked version of this file, created via File Save As, is 2,218 KB. clearly there is something “missing” from the crocked version(s).

I’ve ported the working 1997-2003 format version (2,313Kb) over to another PC running Office 2003, opened the file without issue. All Defined Names are present and correct. However, on this PC this file shows as being 951Kb in size. i.e. Different to what is reported on Excel 2007? N.B. Just done a test on the Office 2003 based PC by opening an archived copy of a previous version of the Excel 2007 created file (in 97-2003 format) and done a File Save As. The file changes from circa 1.9Mb to 704Kb when saving via Excel 2003 rather than Excel 2007. Is any of this of significance?

Also tested porting an Excel 2007 version of the file to the Excel 2003 based PC. I have the necessary Microsoft conversion software to open Excel 2007 files in Excel 2003. The same four Defined Names are missing when I open this Excel 2007 file in Excel 2003. However, the Defined Names are there and working if I open the original file in Excel 2007.

Any of the above help, shed any light? Oh yes.. I’m fully patched and I’ve downloaded the hotfix mentioned earlier in this thread.

Cheers
Peter

Reply | Quote

Could you perhaps list the 4 offending range names and their formulas?

Reply | Quote

Could you perhaps list the 4 offending range names and their formulas?

cal1stPrep =HEX2DEC(MID(INDEX(tblTestProgressLogs,calPrimaryTestLog,COLUMN()-inpColumnOffsetPrep),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))

cal2ndPrep =HEX2DEC(MID(INDEX(tblTestProgressLogs,calSecondTestLog,COLUMN()-inpColumnOffsetPrep),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))

cal1stTest =HEX2DEC(MID(INDEX(tblTestProgressLogs,calPrimaryTestLog,COLUMN()-(inpColumnOffsetTest+IF(COLUMN()>=inpColumnFudge,1,0))),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))

cal2ndTest =HEX2DEC(MID(INDEX(tblTestProgressLogs,calSecondTestLog,COLUMN()-(inpColumnOffsetTest+IF(COLUMN()>=inpColumnFudge,1,0))),INDEX(lstIndexInLogData,ROW()-inpRowOffset),CH))

Cheers
Peter

Reply | Quote

The names have in common that they use the HEX2DEC function. In Excel 2003 and before, this is a function which is part of the analysis toolpak addin and hence technically a “User defined function”.
In Excel 2007, these functions are part of Excel itself.

As soon as you export an Excel 2007 file to 2003 fileformat, defined names using these ATP functions are “mistreated” obviously.

The only way I’d know how to solve this is to avoid usage of former ATP functions. Maybe they do work if they are just in cells directly iso range names.

Reply | Quote

The names have in common that they use the HEX2DEC function. In Excel 2003 and before, this is a function which is part of the analysis toolpak addin and hence technically a “User defined function”.
In Excel 2007, these functions are part of Excel itself.

As soon as you export an Excel 2007 file to 2003 fileformat, defined names using these ATP functions are “mistreated” obviously.

The only way I’d know how to solve this is to avoid usage of former ATP functions. Maybe they do work if they are just in cells directly iso range names.

Thanks Jan Karel – Great spot!

Moved the HEX2DEC() out of the Defined Name and into the worksheet itself. I left the rest of the formula in the Defined Name(s). Works exactly as I need in both Excel 2007 and File > Save As > Excel 97-2003. File size also now reduced to 1Mb from it’s 2Mb in Excel 97-2003, though perhaps that;s not related.

Anyway. It works. Thanks again for you insight.

Cheers
Peter

Reply | Quote