Hi all,
As some of you may be aware, Excel 2007 has been exhibiting an obscure but very annoying bug. On some rare occasions, Range Names may disappear from your workbook after saving it.
I have never been able to detect any pattern in this behaviour, it just happens on and off.
I’m happy to say Microsoft created a hotfix for this bug.
###EDIT 2009-07-06 15:10 GMT: fixed a small mistake, you need SP1, NOT SP2###
Make sure you have Office 2007 updated to at least SP1; visit Microsoft Update to check if you need it.
After that, visit the hotfix page and request your hotfix download.
For more information about range names, read my series of articles on them here.
And if you use range names frequently (or plan to in future) do yourself a favour and download Name Manager.
Regards,
Jan Karel Pieterse
www.jkp-ads.com
Thanks for the heads up Jan Karel.
This has been a major PITA at one of my clients
This also has been a major problem for me. I have had to build code that resets my name ranges each time the spreadsheet loads.
Hi Jan,
Are you saying that it is a mistake to install this hotfix if you already have Office 2007 SP2 installed?
Thanks,
Alan.
No, the hotfix is dated later than SP2. I just wanted to say the hotfix requires SP1 minimum.
So I guess that means anyone should patch Excel who is pestered by this issue (and one of the others mentioned in the article).
Thanks Jan – much appreciated.
The issue with range names disappearing has affected one of my clients, so I checked using the Update service to see if I needed the hotfix – it reported that I was up to date. However, based on input from my client, I downloaded the hotfix and installed it anyway – there is a check at the beginning to see if the hotfix should be applied. Lo and behold, it installed the hotfix.
Not sure what the issue is there – was it already installed and the installation just reinstalled it, or was it not installed when it said my computer was up-to-date – hard to tell, but I really need this hotfix so I am happy I downloaded and installed it.
Hi,
Sorry to be asking two questions on the same topic, but is there any harm in installing this hotifx across all our installations (which are all on Office 2007 SP2)?
Thanks,
Alan.
As well as the range name problem the hotfix also claims to fix performance problems when you write to a spreadsheet range from VBA. I hadn’t had the range name problem, but I’d certainly noticed the slow writing problem, so I was hoping for a significant improvement.
Here’s the results for writing about 6000 values to cells, one at a time:
Excel 2000: 3.2 sec
Excel 2007: 23.4 sec
Unfortunately I didn’t do a benchmark before installing the hotfix, so it’s possible that 2007 was even worse before, but it seems they still have a long way to go before getting back to 2000 performance levels.
Using .Worksheetfunction calls are also still much slower than in previous versions, although to be fair they didn’t claim to have fixed that.
Does anyone else have any evidence of this hotfix working as claimed (or not) for improving VBA to worksheet performance?
Alan: I expect this hotfix will be included in an upcoming update, but it is impossible for me to tell when that might be. The hotfix installation checks if you need it, so I expect no trouble.
If in doubt, I’d set up a test user to try the hotfix first and have that user use Excel for a while to see if everything keeps working as expected.
Hi Jan,
Good advice – thanx!
I’ll install it on my machine only (I image at least once a week so no big deal to roll back if reqiured), and see how it goes.
Thanks,
Alan.