Unspecified but Painfully Frustrating Error
by Jon Peltier
Tuesday, June 23rd, 2009
Peltier Technical Services, Inc., Copyright © 2010.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
In recent months I have encountered a particularly troublesome error. It occurs in Excel 2003 and 2007 (and perhaps in earlier versions, but I don’t recall any such cases). The error is heralded by an unusually unhelpful error message, even my Microsoft’s standards: Microsoft Visual Basic – System Error &H80004005 (-2147467259). Unspecified error.
Pardon my French, but WTF?
To rub salt into the wound, this is followed up with an Out of Memory error:
Sometimes these occur once, sometimes twice, when Excel is started, when a workbook with code is opened, or when an add-in is opened.
Association with RefEdit Controls
Closer examination sheds some light on the problem. It seems to be related to our old friend, the RefEdit control. If a workbook or add-in has a UserForm with a RefEdit, this error may occur. When the error occurs, it may be impossible to view the UserForm in the VB Editor, or if the UserForm can be viewed, any RefEdits have been stripped off the form. UserForms without RefEdit controls can be viewed with no problems.
On a computer that experiences the problem, the RefEdit library (refedit.dll) is not available in the references under the VB Editor’s Tools menu,and sometimes it turns up as a MISSING reference in this list. The RefEdit library is also not found in the list of Additional Controls which can be added to the VBE’s Controls Toolbox. If the RefEdit control had been present on the Controls Toolbox, its place may be taken by a gray square with a tooltip of “Unknown”.
Interestingly enough, the RefEdit library is present on the machine, in the Microsoft Office program directory. But the library is apparently not accessible.
There have been some recent changes in the RefEdit control, I believe on more than one occasion, but I have never documented the changes. There are no outwardly visible differences in the control, but the file size has changed, the file created or modified date has changed, and the file version has changed.
History
This famous Unspecified Error, its hex code 80004005 burned into many a programmer’s retinas, has been around for a long time, in Visual Basic 6 and probably earlier. It has been blamed on many factors, many of which seem like wild guesses, and most of which are irrelevant to our situation here. There are a lot of instances in which this error occurred in projects which dealt with retrieval of data from data controls or databases (Access, Oracle, SQL Server, and others). This error has also been associated with ODBC, ADO, IMAP, ASP and IIS, and DLLs from Microsoft (INET and HTML controls) and third parties. I suspect that this is a catch-all error message when there is an internal error in VB, when there is no specific information but the error is somehow associated with various controls or libraries.
At least one Microsoft Knowledge Base article discusses this error in the context of Excel. OFF2000: “System Error &H80004005 (-2147467259)” Running Macro with UserForm attributes the error to opening a file with a userform that contains a Microsoft DirectAnimation Sprite control (daxctle.ocx), whatever that is. Apparently that was the Office 2000 version of the problem, and in Office 2003/2007 it’s kicked off by a RefEdit control.
Google finds innumerable links to this error in web sites and newsgroups, related to Visual Basic, Office, and recently, Excel. I have spent dozens of hours poring through search results looking unsuccessfully for relief.
What makes this problem so much harder to document and troubleshoot is that it does not occur on all computers which have had nominally the same upgrades. One user will be hobbled by this error, while a user in the adjoining cubical will show no signs of it. I have had substantially identical VMs on the same computer, some with the problem, some immune.
Remedies
This problem has surfaced among users of my charting utilities, but usually I’ve been able to fix it. Also, one of my clients has a large Excel add-in package which has experienced this error. So far I think we’ve successfully treated all infections. (And no, I’m not the Excel Doctor.)
I have tried a wide range of remedies. A few involve chicken’s blood at full moon, but most involve various Windows and Office activities. Some remedies work some of the time, but what works on one computer may not work on another.
Because this problem was sporadic when I first encountered it, I didn’t document the exact steps taken to diagnose and remedy the issue. I’ve generally followed a brute force approach until recently.
In mild cases, deleting all files in the user’s temp directory and in the C:\Documents and Settings\User Name\Application Data\Microsoft\Forms directory will clear up the problem. This is all that my main machine needed, but generally it’s like trying to boil the ocean.
Reregistering the refedit.dll does not seem to help. I have tried replacing an older version of this library with a newer version from another computer, but I don’t recall this ever having helped.
Lately it seems that almost all cases in Excel 2003 are fixed using the Help menu’s Detect and Repair command, so this is my first step in treating the malady. Excel 2007 has a similar command deep under the Office Orb (Excel Options > Resources > Run Microsoft Office Diagnostics > Diagnose). I’ve used this command, but it has never given me the same warm fuzzy feeling of a Detect and Repair operation. However, just today an end user ran the diagnostics, and the problem was cleared up.
There are a couple preventative measures which seem to make add-ins resistant to the unspecified error. Open the add-in in Excel 2000, uncheck the reference to the RefEdit library, compile the code (ignoring compile errors related to new Excel 2002-2003-2007 syntax), and save and close the add-in. The compile step may not be necessary. I’ve also removed RefEdit controls and replaced them in 2000, but this seems to be unnecessary (as well as tedious).
I’ve seen two or three cases in which these last few steps did not seem to work. But in at least one of these cases, I’m not sure the end user followed instructions and actually ran the Detect and Repair or Diagnostic command.
Update 23 June 2009
Bob Flanagan has shared a link to his detailed protocols for fixing this and other problems with Office installations at What to do if Excel add-ins do not run. Thanks, Bob.
Other Experiences
Have you encountered this problem? Have you documented it? Have you successfully treated it?
If so, I want to hear about it. Please leave a comment with as many details as you can muster.
Related Posts:
- Using RefEdit Controls in Excel Dialogs
- Build an Excel Add-In 4 – Create the Dialog
- Alternative to Excel’s Flaky RefEdit Control
- Installing an Excel Add-In
- Pardon the Interruption
- Forms Controls and ActiveX Controls in Excel
- Build an Excel Add-In 6 – Interface for 2003
- How To: Assign a Macro to an ActiveX Control
- Charting Utility Upgrades
- Blue Ribbon Interface
Posted: Tuesday, June 23rd, 2009 under VBA.
Comments: 18
Comments
Comment from John Nurick
Time: Tuesday, June 23, 2009, 8:01 am
Hi Jon,
I’ve been having something equally frustrating, different symptoms but involving RefEdit.dll. I posted this on Excel-L and Rob Bruce suggested I come here.
The dreaded Compile Error in Hidden Module: This Workbook
I’ve started getting this error with the latest (test) version of a long-established .xla addin – but on only one of the three computers I routinely use.
The error appears when the add-in loads. (The VBA project is password-protected.)
If I go into the VBE, clear IsAddIn and remove the password, then Save or Save As, I get a messagebox saying “Document Not Saved” and the file is not saved.
In the VBE, if I select Debug|Compile I get a messagebox “Compile error:
Object library invalid or contains references to object definitions that could not be found”
When I check the references, “Ref Edit Control” is MISSING but the others are all there. RefEdit.DLL is present on the machine, but deleting the “missing” reference and browsing to the DLL doesn’t help.
In the Immediate pane, simple expressions such as
? 1 + 2
evaluate correctly, but anything that uses a function or object doesn’t (e.g.
? Sin(3)
? Application.Name
brings up the “Compile error: Object library invalid…” error as above.
The computer that gives problems is a Citrix Presentation Server session (Windows Server 2003 SP2, Office 2003 SP2). Everything works normally the two physical machines I’m using. These are an elderly Dell laptop running Win 2000 and Office 2003 SP3 and a two-year-old generic Intel desktop (WinXP fully patched and Office 2003 SP3). I’m hoping someone can make a suggestion about what may be going on that I can pass on to the company helpdesk.
The new code in the latest version of the XLA doesn’t do anything ambitious. After running Rob Bovey’s code cleaner the file is about 650KB.
The Windows 2000 machine has Acrobat Professional 8.1 loaded, and the PDFMaker add-in (which I’ve heard can cause this error) works fine. The problematic machine just has Acrobat Reader.
Comment from Jon Peltier
Time: Tuesday, June 23, 2009, 8:53 am
If I go into the VBE, clear IsAddIn and remove the password, then Save or Save As, I get a messagebox saying “Document Not Saved” and the file is not saved.
What if you do this on a machine on which the add-in is behaving? What if you clear the reference to RefEdit on one of these machines?
What happens if you perform a Detect and Repair?
Do you have access to an earlier version of Excel (e.g., 2000)?
Comment from John Nurick
Time: Tuesday, June 23, 2009, 9:35 am
Thanks for the questions.
>What if you do this on a machine on which the add-in is behaving? What if you
>clear the reference to RefEdit on one of these machines?
Everything works as normal: I’ve just opened, unlocked, cleared IsAddIn and removed and restored the reference to RefEdit.dll, and then done a Save As.
Am about to try the unlocked non-Addin on Citrix…
…it opens but brings up the VBE with the message “Compile error: Object library invalid or contains references to object definitions that could not be found”. The VBE has opened the “This Workbook” module and highlighted the first occurrence of
vbCrLF
in the module-level constant declarations – i.e. the first token that (I think) refers to a library and not to the dark VB heart like Option Explicit and Private Const.
In Tools|References, Ref Edit is MISSING as before. I’ve unticked that reference, used REGSVR32.exe to re-register RefEdit.dll, and set the VBE reference again. Still won’t compile.
>What happens if you perform a Detect and Repair?
Does this mean selecting “Open and Repair” in the Excel File Open dialog? If so, when I do it to the addin in a Citrix session I just get “compile error in hidden module”.
Same thing if I try “Open and Repair” with the unlocked non-addin version.
>Do you have access to an earlier version of Excel (e.g., 2000)?
Not easily. I’d have to look for an old MSDN CD and then set up a VM. No time for that until next week.
Uggh.
Comment from Jon Peltier
Time: Tuesday, June 23, 2009, 11:23 am
Does this mean selecting “Open and Repair” in the Excel File Open dialog?
No: Help menu > Detect and Repair. I don’t know how this will work on a Citrix thing. If you can’t run it, you need to cajole the IT powers that be to do it for you.
Comment from John Nurick
Time: Tuesday, June 23, 2009, 11:39 am
Stupid of me. Have never needed to use it before. But have now learnt that it’s not available on a Citrix thing anyway. Presumably because the VM is loaded from a pristine (or “pristine”) image every time.
I’ve sent the add-in to the helpdesk with a request to try it on a couple of real or virtual computers, and it’s been referred to a guy I know who looks after the Citrix setup. Next thing is to wait and see if he discovers anything.
Thanks for the help. I’ll post back here if there’s anything worth saying…
john
Comment from Bill McNeese
Time: Tuesday, June 23, 2009, 1:00 pm
Hi Jon,
Very good summary of the issues related to this error. I hope someone out there has a magical fix. But I am afraid not.
Also, twice in the past, I have had the RefEdits in an add-in just suddenly disappear. No reason for it that I could see.
Bill
Comment from Jon Peltier
Time: Tuesday, June 23, 2009, 2:02 pm
Bill -
Short of voodoo or alchemy, I doubt there’s a magical fix. The Detect and Repair and Office Diagnostics may be as close as we get, since they’ve been relatively effective.
Comment from Bob Flanagan
Time: Tuesday, June 23, 2009, 9:04 pm
Jon, I’ve seen the error you have described many times. Too many times.
My first line of defense is to uncheck the reference to the refedit control in Tools, References before I release an add-in or macro. This fixes it about 95% of the time. However, I get a few users who still have the problem with vba macros not running. I’ve been 99.99% successful in solving for them by:
1) First, having them run the free Temp File Deleter found at http://www.add-ins.com/temp_file_deleter.htm. I have them delete all temp files and all the EXD files. Both are critical steps. Especially the deletion of the EXD files.
2) Reboot and then install all service packes starting with sp1 to spX for the user’s version of Excel. Technically one should only need to run the latest. But for some users, running all three is the only cure.
3) Re-run the Temp File Deleter after installing the service packs – very important as it removes the EXD files so the next step is done cleanly.
4) And the last step: Run a dectect and repair (or Excel 2007 diagnose) after installing the service packs.
I’ve outline all the steps at http://www.add-ins.com/how_to_repair_office.htm by excel version.
Again, success rate is 99.99%.
Bob Flanagan
Comment from Jon Peltier
Time: Tuesday, June 23, 2009, 9:27 pm
Bob -
Thanks for your remediation protocol. It’s not too different from what I’ve done, but it’s more systematic, and I believe your success rate is good. I go through and wipe out the EXD files from time to time, just for target practice, but it seems to reduce funny business that occurs from time to time. It’s nice that you have a utility to clean up the temp and EXD files, I’ll have to give it a try.
Comment from John Nurick
Time: Tuesday, July 7, 2009, 6:55 am
Our Citrix expert came back to me just as I was packing up for a few days’ holiday. He thought it would turn out to be a permissions issue and said he’d try publishing the add-in as a Citrix application.
Now I’m back at work and trying it. So far it seems to work. I can’t imagine why – though it does suggest that whatever is the source of the painfully frustrating error it isn’t actually RefEdit…
Comment from Jon Peltier
Time: Tuesday, July 7, 2009, 7:21 am
John – I imagine this might be a problem that has multiple causes. It might be a particular combination of controls, which might somehow be at odds with updated libraries for these controls. We sometimes say it’s corruption when it oculd be incompatibilities between libraries.
Comment from John Munoz
Time: Thursday, November 12, 2009, 11:37 pm
Hi All,
I recently “upgraded” to Excel 2007 and was greeted with a battle of the Micros, Microcharts v. Microsoft. I kept getting the error, “Compile error in hidden module: modInit” as soon as I opened Excel, and Microcharts wouldn’t run.
After sinking about 5 hours into trying to fix the problem, I came across Bob Flanagan’s post above, used his temp deleting program, repaired Office 2007, and voila, I’m back in business. Thank you Mr. Flanagan for documenting the fix and sharing it with everyone. And thanks to you Dr. Peltier for hosting this terrific site!
John C. Munoz
http://www.bzintelguru.com
Comment from Jos
Time: Wednesday, December 2, 2009, 10:43 am
Detect & Repair worked, thanx.
Comment from David Harding
Time: Friday, February 5, 2010, 12:33 pm
We upgraded to Excel 2007. A few days later I tried to install a charting add-in. Somehow I got into a state where a Ctrl-C would generate the &H80004005 error. It took anywhere from one to four dozen clicks on OK before the error box cleared. Sometimes a memory complaint followed. I uninstalled the add-in to no avail.
Deleting the temp and forms files did not help. When I ran the diagnostics, Excel reported finding and fixing it a problem during the 20 checks it makes in the setup area, but wouldn’t tell me any more details. The Ctrl-C problem seems top be cured, but I haven’t tried reinstalling the add-in.
Thanks for the pointer.
Comment from batGnat
Time: Thursday, March 18, 2010, 1:57 pm
I believe in some cases it is a refedit.dll version conflict.
I write a lot of excel sheets for work. I work for a company contracted to look after a warehouse. We run their computers, with their network and get no say in what versions of software we run.
I (being a nerd) have upgraded office 2003 to SP3 everyone else is < SP3. My sheets will not run ref edit error on pre SP3.
Until that is I copy refedit.dll from my SP3 machine to theirs, and re-register it with the system.
No More Errors.
Comment from Jon Peltier
Time: Thursday, March 18, 2010, 4:19 pm
BatGnat -
Your solution works fine in a closed system. As soon as your files are deployed to a different location, you lose access to users’ computers.
The other alternative is to clean out temp directories, encourage upgrades to Windows and Office, then open your workbook in Excel 2000, running a code cleaning utility, and saving them.
Yet another alternative, which I have not written about yet, involves using a textbox with a drop arrow and Application.InputBox to get the range from the user. No RefEdit, so no errors.
Comment from Nicklas
Time: Friday, July 9, 2010, 6:18 am
Yesterday I got the missing dll message and I was unable to run any vba code, I was also unable to edit my forms. As far as I can remember I do not use the refedit control at all. The unchecking and compile did not work. The Office 2007 diagnostics showed no errors.
I read and followed Bob’s instructions. It occured to me that my Office installation was not updated. So I used Microsoft Update and updated EVERYTHING, about 45 different packages including the MSO 2007 SP2.
Reboot and then Bob’s temp cleaner again. Then the MSO diagnostics tool. It found 1 error in the installation packages that it fixed.
Then I started excel again and the refedit reference is now working. So now I will try to clean up my code and remove this referece. My problem is still that this file is distributed and used in over 400 copies with different content. Luckily enough I as the developer was the first to encounter this problem.
Previous version: Microsoft Office Excel 2007 1.2.0.4518.1014
New version: Microsoft Office Excel 2007 1.2.0.6425.1000
Thanks for the only good information on this subject!
//Nicklas in Sweden
Comment from Jon Peltier
Time: Friday, July 9, 2010, 9:30 am
Hi Nicklas -
Another frustrating Microsoft Moment. Glad you figured it out in time to answer a flurry of frantic phone calls from your users.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.