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.
John Nurick says
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.
Jon Peltier says
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)?
John Nurick says
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.
Jon Peltier says
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.
John Nurick says
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
Bill McNeese says
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
Jon Peltier says
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.
Bob Flanagan says
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
Jon Peltier says
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.
John Nurick says
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…
Jon Peltier says
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.
John Munoz says
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
Jos says
Detect & Repair worked, thanx.
David Harding says
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.
batGnat says
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.
Jon Peltier says
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.
Nicklas says
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
Jon Peltier says
Hi Nicklas –
Another frustrating Microsoft Moment. Glad you figured it out in time to answer a flurry of frantic phone calls from your users.
Bob Flanagan says
Jon, just an update. I have seen a fair number of “out of memory” errors, with the error appearing randomly as the macros run. The primary cause appears to be:
1) COM add-ins. Adobe and BlueTooth sneak into Excel. Others are third party trial add-ins. As far as I can tell, any COM add-in is bad news.
2) Trying to run Office and windows 7 with just 2 gig of memory. 4 gig is marginal. 8 is nice.
3) Running 64 bit Excel and any other Office application at the same time. Three instances.
Guillaume BARRIO says
Okay,
I am no VBA expert but had this “painful” error occurring on one computer belonging to an user working in my company.
She needed to generate report from a macro workbook.
I could isolate the issue when I tried to run the modules one by one. I could pop this error message very easily when I tried to run an “UserForm” from the VBA project.
This UserForm is called when they click on a button in the spreadsheet after they filled the criteria they need. It launches a “ComboBox” in which are reported the criteria they previously written in the cells.
I followed this solution :
In the VBA editor, stop the VBA Project then open Tools>References and Browse for a *.ocx ==> C:\windows\system32\mscomct2.ocx
Then save the file.
The error message no longer popped to our sad faces…
Maybe this is irrelevant for you now but thought nice to let a comment on this.
Jon Peltier says
Guillaume –
Apparently the VBA developer used a special control which is not part of the Office VBA package but instead came from Visual Studio. This can be done, of course, but different installations may store the VS components in different places. So again, the problem is a dialog (UserForm) using a control that VBA can’t find or can’t implement.
Fortunately you were able to find the component and fix the problem.
Max says
Jon, thank you for the time you spent preparing this very informative post, and I’m particularly impressed by your attention to detail and willingness to address our comments.
I have been designing an Excel application that utilizes a very, very heavy userform, replete with 8 different tabs probably 100+ controls (many of which are added dynamically at runtime, depending on user input). The only controls i’m using at the moment are the default Microsoft Forms 2.0, the reason being that they are free (albeit a little antiquated). I’m developing in Excel 2003, but also testing simultaneously in 07 and 10.
I have faced the frustrating issue not with RefEdit but with ListBox instead, when setting the RowSource property. Unspecified error, cannot set rowsource property. It’s a similar problem, with the memory issue every now and then. I have NEVER been unable to solve an issue faced while coding VBA applications, usually because someone posts about it with a resolution. But for the longest time this one eluded me. My application was unable to set the rowsource with a named range even after clearing the list, and it returned this error without any explanation.
I have solved the problem for my particular case.
What I attempted is a risk, because I am altering files in the C:\Windows\System32 directory on which the Office programs depend for functioning. But it worked for me. I’m operating on Win XP Professional, using MS Office 2003 AND 2010 installed on the same system.
There are two files of interest in that directory. One is called “FM20.DLL” and the other “FM20ENU.DLL”. I would assume the latter file is an English-only version, and may not exist in others’ systems. My contention is that MS screwed up these files somehow when they published the newer versions of Office. Don’t ask me how, or why, or what exactly these files do. To my judgment, they are the setup/config files for all of the default userform controls used by VBA userforms. Probably set the memory allocation for them.
Anyway, I found that when I had Excel 07 and 10 installed on the system, even if I was using 2003, the application would fail when trying to set the row source property. I took the following steps to resolve:
1. I uninstalled all office versions. Rebooted.
2. I re-installed 2003 and took a look in the System32 folder, and noticed that these files were last modified in 2010, meaning that they remained left over from the Office 10 installation.
3. I copied these files to the desktop only for back-up purposes.
4. I deleted these two files. Excel should be closed.
5. I opened up MS Excel 2003, and it greeted me with an installation screen before fully opening up. It installed something successfully.
6. I opened up the C\Windows\System32 directory and looked for those two files. They had a date modified of 2007. Good news. Vintage.
7. I copied them to a separate folder in my desktop for back-up.
8. I installed Office 10 and looked again and saw that the two files had a date modified of 2010. I replaced these files with the backups of the 2007 files (which were installed by 2003).
I now have no issues with unspecified errors in my userforms. At your own risk, perhaps give that a try. Worked for me for a similar issue.
Jon Peltier says
Max –
Thanks for sharing your detailed trouble-shooting.
At one point I had done some heavy engineering of the C:WindowsSystem32 directory, with respect to the ref edit library. There were different versions of this file, with different dates and different file sizes. For a while if I replaced whatever was there with the version that came with 2003, all was cool. But then this became unreliable, and I was forced to remove the RefEdits from my dialogs.
I implemented an Alternative to Excel’s Flaky RefEdit Control which used text boxes and the InputBox function to get the user-selected range. But this is not as nice as the RefEdit, when the RefEdit works.
It seems that the RefEdit has stabilized after a number of 2007 and 2010 updates, so I have found a New RefEdit Approach that adds RefEdits on the fly when the dialog loads; if there’s an error, the RefEdit couldn’t load and wouldn’t work anyway, so I revert to the kludgey textbox/InputBox approach. This will roll out with my new utilities later this year.
Eric says
Jon, thanks for your informative post. I too had been struggling with this error for months while running a macro from a relatively simple userform. I tried your recommendations but for my particular case I kept getting the erros. After months of tweaking, the problem turned out to be listbox/rowsource related. The userform has a listbox with a named range as rowsource. The number of items in the range varies according to a macro that was set to run during UserForm_Initialize. The way I had set it up:
1) User clicks Userform
2) During Initialize there is code to clear a column of cells where the items for the named range will be placed
3) During Initialize a macro runs which determines the items for the named range and places the items in the column
4) When the userform pops up you see the listbox with the correct items inside. (The rowsource for the listbox is set in the properties window not through additional code in Initialize)
What fixed the error message for me was moving the macro for the named range items to run BEFORE loading the userform. This way the rowsource for the listbox is already fixed and set by the time the userform opens. Now when the user clicks the userform, before “Show Userform” I have a line running the dynamic range macro.
Hope this helps and thanks for all the great info!!
Jon Peltier says
I ran into massive problems some years back with RowSource, and now I always load worksheet data into arrays, manipulate the arrays, dump the arrays into the listboxes, then dump them back into the worksheet (if necessary). It sounds like a lot more work, but it isn’t. It’s a more reliable approach, and you have more control over what is going on.
Francis Logan says
Hi Jon
Have had problems with rowsource with named range and single option listboxes where following an error I would get unspecified error, and then I couldn’t delete the listbox, and if I tried to save would get the 80004005, and workbook seemed corrupted.
However found a post where setting the bound column to 0 on the listbox meant the rowsource worked fine, and saved and have no problem now. If I changed it back to 1 its fine when reopened.
I don’t know why this is though, which is just as frustrating as having the error in the first place.
Thanks
Francis
Kevin Beckham says
Hi Jon
Last week a particular workbook opened without problems, today it failed to open. In between, IT department had forced installation of “security” hot-fixes.
The solutuon may be as simple as re-registering the ocx file – which worked for me (Windows XP, Excel 2007)
Regards
Kevin
regsvr32 C:\Windows\System32\MSCOMCTL.Ocx
Dan Hall says
Jon, thank you for your post. I am impressed that after four years since you first wrote it, this post is still providing invaluable assistance to those of us who have flattened the front side of our head because of this horrible error code. I am also slightly depressed (but not surprised) that Microsoft has not completely eliminated it yet.
I have come across this error several times in the last 6 months. My situation had nothing to do with RefEdit or RowSource. Matter of fact, I could not duplicate the error on my development machine. I had to use a test system to simulate my end users’ configuration and duplicate the error (which occurred each time the file was opened).
After trying each idea above unsuccessfully, I actually called Bob Flanagan who graciously helped me to identify that the AcroPDF.dll on my user’s systems were not initializing. (I use AcroPDF.dll to give my users a handy reference to corporate documents, etc.) After updating their version of Adobe Reader, everything works fine. Again thanks, Bob and thank you Jon for this post.
I hope the folks at Microsoft come across this post and decide to give us a bit of control about what happens when code runs into these errors, even if it is a “resume next” solution.
Thanks,
Dan
Paul Gulliver says
Hi,
I’ve just come across a similar version of this problem. I’ve migrated an Excel VBA application from Office 2003 (which worked fine) on XP to Office 2010 on Win 7. The app includes a form which has a number of combo boxes, list boxes and command buttons on it but nothing much else (no RefEdit controls). After using the form for a bit the Unspecified Error message appears, but usually without any other message. From then on it pops up all the time, even sometimes in the VBA editor and when closing the file. Closing Excel and re-opening the file gets rid of it for a while but then it returns again.
I’ve tried exporting the code and re-importing it, I’ve deleted the .exd file, but nothing seems to work. I’ve reproduced the problem on another machine as well.
I’m tearing my hair out here and would be grateful for any suggestions. Unfortunately I don’t have Administrator access rights so it’s not easy to try some possible solutions involving the registry.
Macarius says
I tried to avoid using the Clipboard and copy-pasting a chart, so I used the ChartObjects Duplicate method, and it caused an “overflow” fault.
Excel Doctor you are, good sir.
neten says
I am still facing this issue right now and since everything has changed with the new office 365, the old fixes are not working. Anyone has any new suggestions?
Jon Peltier says
Neten –
I get Out of Memory errors all the time, so I end up restarting Excel more frequently then you would think is necessary.
Are you having the RefEdit errors? I don’t use RefEdits in my hard core Charts for Excel add-in, but I use them in less formal projects, and I haven’t seen problems with them in ages.
Dan says
I’ve had the RedEdit control enabled in References and in the Toolbox in work laptops at previous employers, but I’ve never had an occasion to use it. On my personal computers, I’ve never had an occasion to enable it in References, much less ever use it. That said, I have encountered these errors will some frequency, so I thought I would just set out when and how they to me, and what I do, in case of interest to you/someone or it might help the ‘penny drop’ in terms of finding a solution.
It can happen a brand new new workbook, or a workbook full of userforms and modules.TAn error will sometimes trigger (30-40% of the time?), when I’ve make a change to the design of a userform AND I then – within 5 seconds? – go to change the name property of the userform. It will be one of two errors – (1) Excel ‘cannot find that file’ error or (2) a system/catastrophic error. The XLSM then cannot be saved. The relvant userform cannot be run, instead trigger the system/catastrophic error message in succession an (as yet) undertermined number of times – sometimes the messages appears 5 times in a row (guestimate), sometimes it can 50+ times. I suspect that the number of times it appears tracks the number of class/standard/userform modules in the project. Why? i don’t know. But it’s excrutiating, because i literally cannot do anything with the entire Excel instance until those al the message boxes have been cleared. And if Autosave has been triggered, then the countdown begins again.
The userform cannot be exported, and the controls cannot be copied and pasted to somewhere else. The code, however, can be accessed.
This has significantly affected my entire workflow. I regularly save my workbooks in incrementing versions. In addition to that, I have routines to export all of the VBComponetns in the workbook to individual files. What I have recently discovered is that this export routine will run after having encountered this system/catastrophic error problem, and it will work for standard and class modules and SOME of the non-problematic Userforms. As above, the problematic userform cannot be exported. Curiously, some completely unrelated userforms will not be exported either, but some will. What’s the connection? No clue. It can affect userforms with no controls (for implementing dynamically generated controls etc) and userforms with standards controls.
It turns out, if I remove the userforms that cannot be exported. I can then save the workbook and everything is completely fine. This is in Excel – I’m starting to see if the same problem occurs in other Office products. I have Office 365, or whatever we’re calling it today.
I have seen some of the comments above re: deleting the temp files, etc., and they look interesting – I will look into that and report back. But I just thought I’d set out my personal experience/tragedy, given my non-connection to RefEdit, in case anyone is marginally interested. Apologies for the long post.
Jon Peltier says
Hi Dan –
Sounds nasty. I’ve had intermittent issues like some of these, but restarting Excel usually clears them up. My only suggestion to you is to repair Office, and if that doesn’t work, reinstall Office. Painful, but it might be all that will work.
Henry Young says
I had the exact same error message when attempting to use a 64-bit ODBC driver from 32-bit Excel (2010 on W7):
System Error &H80004005 (-2147467259). Unspecified error.
Looking this up online there’s such a wide range of circumstances and fixes reported.
Seems there is one error message to rule ’em all when it comes to binary component incompatibility issues in VBA.