Excel Errors and Other Messages
Today is Spreadsheet Day, which commemorates the day when Visicalc, the first electronic spreadsheet program for personal computers, was released in 1979. My friend and colleague Debra Dalgleish, who seems to have a blog for everything, started Spreadsheet Day in 2010, and it gets bigger every year. For some of us, of course, every day is Spreadsheet Day. But in honor of the holiday, I thought I’d share some of the numerous Excel error messages I’ve encountered over the years. I don’t exactly know why I’ve saved them, although sometimes they have helped with debugging. They’re not all error messages, either, some just tell you what’s happening.
I’m not showing these messages to criticize Excel, but to poke a little fun. When you use Excel as much as I do, 24-7-365 (or so it seems), you will see many error messages.
These messages were captured while using numerous versions of Excel and Windows, on several computers, so there is no real consistency in their appearance. Some messages had to be reduced in size to fit the margins of this blog, but you can still read them if you squint.
Various Spreadsheet Error Messages
Formula Errors
Excel is full of formulas, so the likelihood of formula errors is high.
The above error message has been reworded: now Excel takes credit for finding the problem.
And now “We” have found the problem, I guess referring to the little green men inside Excel that make everything work.
This is a more detailed message telling you where to find the problem. You can tell they didn’t use PowerPoint, because the bullet text isn’t quite aligned.
Memory and Resources
This message tells you that your formula has blown up Excel.
This next Excel message usually happens when more than formulas are involved. Charts, Pivot Tables, who knows.
Despite what that last sentence implies, I have encountered this error while using both 32- and 64-bit Excel.
Clipboard
I get this Excel message sometimes when I select whole or partial rows or columns, and drag them around the worksheet. There’s nothing really wrong, as far as I can see and as far as anyone at Microsoft can tell me (I’ve asked); the selected range goes where I want and everything works fine afterwards. But it’s annoying, because I have to stop what I’m doing and click OK. This message will start appearing for no apparent reason, bother me for a few hours, then go away for a few days.
This is an old message that I used to see a lot in Excel 97 through 2003, when copying a large chart or range as a picture. I used to remember how many pixels would set off this one, but in 2007 and later I have not seen this message…
…except for once very recently, when I snapped this screenshot.
Every Message Tells a Story
It’s nice when a series of messages, when strung together, tell a story.
Saving Files
This is a story about a certain file, on which I’d worked for hours, then saved. Or tried to save. But “errors were detected.”
Errors were encountered, but Excel “was able to minimally save” my file.
“Damage to the file was so extensive” that I went into mourning.
The above message usually means that Excel saved the values in the spreadsheet, but any formulas, formatting, pivot tables, objects (shapes and charts), and VBA are lost.
This whole series of messages was common when Excel 2016 was first released, but fortunately I haven’t seen them for many months.
Not Even Minimally Saved
Sometimes when saving, though, there is a really unhappy ending.
Like the previous sequence, I have not seen this message for a long time.
Updating
When Office 365 and click to run became a thing, some messages got their own appearance, with a lot of red Office coloring.
Here we’re updating Office, something we all love to do.
Usually you can keep working while updates are in progress, at least until the downloads are finished, but not always.
And sometimes you need to wait while Office fixes itself.
Updating Again
Here is a case when I was allowed to keep using Office during the downloads.
But unfortunately, something went wrong.
In recent months and years, the Office update and repair operations seem to have become more reliable and less prone to something going wrong.
Mac Interapplication Incommunication
While working on a VBA project to export Excel charts into PowerPoint (which still doesn’t work, grrrr), I got stuck in an interminable loop, where this Excel dialog…
…would appear, followed by this PowerPoint dialog…
…followed again by the Excel dialog, repeating the cycle until I force quit PowerPoint or Excel.
Something’s Wrong
Excel Has Stopped Working
Sometimes, Excel just gets tired and needs to take a break.
Windows checks for a solution (above) and may have to collect more information (below).
The Mac used to be rather polite about it, but a crash is still a crash.
Trifecta
One time after an unsuccessful Office update, I saw this perfect storm of dialogs.
Fortunately the repair was successful.
Repair
One time I got this sequence of messages. Again, something went wrong.
Excel wouldn’t start (above) so I tried the repair (below).
And the repair commenced.
Phew! Done with the repair.
But after the repair, I still couldn’t start Excel.
As I recall, applying the Full Repair (not the Quick Repair above) fixed the issue.
Although I’ve shown a lot of repair dialogs, I really haven’t had to perform too many repairs.
Unhelpful Messages
Descriptions but not Descriptive
When you encounter an error in your VBA code, it typically has a number and description. For example, run-time error 9, Subscript out of Range, means you’re trying to reference the tenth element of a nine-element array, or a worksheet with a name that doesn’t exist.
If the description doesn’t make sense to you, at least you can Google it, and probably find hundreds of search results to help track it down. Helpful hint: if you’re describing an error to your coder, don’t give them the error number, read them the description, or send a screen shot. We can’t remember all the numbers, and sometimes errors with different descriptions will share a number.
I think half of all VBA errors produce this identical message, with unhelpful error number (1004) and description (application-defined or object-defined error), as if VBA is saying, “your guess is as good as mine.”
And there are numerous other unhelpful messages, like this “internal error”. How do I fix it without a crowbar?
“Automation Error” literally means the code failed.
I just covered “automation error”, but “unspecified error”? That error number (80004005) is another popular one: Google returns about 444,000 results.
“Unexpected Error,” as opposed to all of those expected errors, eh?
Not Even Descriptions
This is a truly incomprehensible message. What is Object ‘~’, and what is its Method ‘~’? In fairness to Excel, the caption reads “VBA Code Cleaner”, so I suspect a VBA programmer wrote some code that called this dialog, probably in a routine called SaveBackup, and forgot to pass in the object and method names.
I’ve seen this Excel message in enough places to think it’s not just a wayward VBA programmer.
And there’s no explanation for this non-message.
Edit
Got a new one today (25 October 2018), well, not a new one, but one I haven’t seen for a while and thought I’d add it.
I was doing some VBA programming, and getting a bunch of compile errors and so forth. But I got the code working, then saved and closed the workbook. Then back in the VB Editor, I noticed that the workbook appeared in the Project Explorer, even though I had closed the workbook. This phantom project phenomenon used to happen a lot, but I rarely see it anymore.
I decided to close the phantom workbook by typing ThisWorkbook.Close False in the Immediate Window. When I clicked Enter, I got this message:
I wasn’t explicitly using ActiveX, but I suspect there’s a lot behind the scenes of the VB Editor, and some ActiveX element couldn’t create an object that referenced the phantom project. Whatever, it was a good excuse to restart Excel.
Bonus
You’ve read this far, so you deserve a bonus. Comment below with a link to your favorite error message on imgur or other file sharing service. I’ll include it in your comment.
The best message received before Halloween (31 October 2018) will earn a free license to Peltier Tech Charts for Excel 3.0B.
Debra Dalgleish says
Great collection of errors, Jon! Here’s one of my favourites (not for your contest though), which they tried to fit on one line, so the message is about 2 feet wide:
“The PivotTable field name is not valid. To create a PivotTable report, you must use data that is organized as a list with labeled columns. If you are changing the name of a PivotTable field, you must type a new name for the field.”
There’s a screen shot on my pivot table blog:
Jon’s Reply:
The linked picture is as tiny as the thumbnail. Was that by design?
john vanlandingham says
I feel so much better now… I have only encountered a very few of these error messages!
Jeremy says
Thanks for the laughs! My personal favorite, while not really an error message, is Excel’s post-spellcheck message of “You’re good to go!” I’m so proud of how hip the software has become…
https://drive.google.com/open?id=1enlW9xXq46BSdkoS0TqK6tQVDQ66TRJ7
Jon’s Reply:
That’s a polite message, quite unlike some of the rude errors I posted.
Bob says
I look at this list as a challenge. How messed up do I need to get my spreadsheet to invoke these messages?
Like a decathlon of Excel experience.
Cheers,
Bob
Jon Peltier says
Bob –
I’ll admit that I abuse many of my spreadsheets. But in general, they are small and don’t have complicated formulas. So things just kind of crash when they crash.
Sometimes I’ll compare Excel to a cranky toddler, who gets fussy after being awake too long. After even a quick nap, everyone’s in a better mood.
Nic says
Recently found this gem:
“The last time you opened ‘file.csv’, it caused a serious error. Do you still want to open it?”
Since when does Excel care about causing errors? Plow on through!
Jon’s Reply:
I’ve seen this a few times, usually with workbooks containing a lot of dense code. Apparently sometimes Excel remembers what files were open when it crashed. Though I can’t imagine what kind of “serious error” was caused by a csv file.
Ian says
This one was rather puzzling the first time I encountered it:
– “Can’t push objects off the sheet”. For example: Hide all columns from D onwards. Insert a Note in B1. Attempt to hide column C.
The new Treaded Comment feature behaves a bit differently; the error occurs only if the Comment is in row 1.
Jon’s Reply:
This is particularly puzzling, since you can’t see the comment unless you hover over the cell to display it.
George B says
One classic:
When I mistakenly enter commas instead of semicolons (for international versions of excel)
Jon’s Reply:
I see that one often enough. It often happens when I lose count of parentheses or mess up in other common ways.
For future reference, PNG is a better format for screen shots than JPG: files are smaller and have fewer compression artifacts.
Johnny C says
My favourite isn’t really an error.
If you use ‘Inspect document’ (useful for finding those pesky links that can’t be updated but are buried in data validation or similar) there’s a section that tells you how many ‘invisible objects’ there are. These are ‘objects that are not visible because they have been formatted as invisible’
You can find invisible objects on a worksheet with VBA. But, without recourse to VBA, how does your average user find an invisible object to make it visible again?
Claude Van horn says
I have actually seen many of the error messages, but my favorites are when I am using the 64 bit version of Excel 2016. Sometimes Excel 64 bit does not load all the References that allow VBA code to work, and there is NO error message. When you try to open the workbook, Excel just closes all the workbooks you were working on without saving anything. The only way to fix it is to have someone with a 32 bit version of Excel open the file and re-save it. Then you can open it again and everything works for a month or two, until it happens again.
This is mostly due to a hunk of VBA Event coding that does something when you open the sheet. I took all those routines out and mostly everything works OK now.
64 bit Excel also sometimes does not load some of the references that allow named ranges and other bits of normal formulas. Clicking OK in the error box will generally load the appropriate reference (but not always).
Jon Peltier says
Johnny –
Go to the Home tab, click Find & Select near the end of the ribbon, and choose Selection Pane. A task pane opens with a list of all objects, visible and invisible. You can easily change their visibility from this pane, without needing VBA.
Jon Peltier says
Claude –
I’ve been using 64-bit on several of my laptops for a while, and I have not encountered this kind of problem with it. If you are using 32-bit DLLs, then sure, you will have problems. But just using formulas and Names should not be a problem. You may have to repair your installation of Office. I suggest that reluctantly, since a few of the messages in my article revolved around the repair process. But usually I’ve had good luck with repairs.
Igor J says
By far this one:
In Soviet Russia, Excel quits you!
Claude Van horn says
Thanks! Actually, I have “repaired” it several times. I get the errors very rarely now, but one had popped up the morning I read your blog?
Mark Hughes says
Thanks for the funny post. I have a somewhat similar issue: I have an Excel “program” that started development back in 2001 and has been expanded and updated over the years. It’s a tool our salespeople use and is up to around 15 megs and extensive VBA routines. We’re using Office 365 Pro Plus and I keep getting reports back from users of VB errors that inevitably turn out to be erroneous, in the sense that the errors can’t be faults. For example, one user may get a message that the operation of unlocking a sheet failed. And yet it’s worked for him/her many times before, works afterward, and others don’t get that error. Yesterday, the workbook couldn’t identify the path to OneDrive–until I stepped through the routine–and then it was more than happy with it.
I have exported all the modules and userforms, and imported them back in, as I’d heard that will correct corruption. It seemed to help, but it hasn’t solved it all, by any means. Have you seen anything like this?
Johnny C says
I have been with XL 2016. VBA bombs out opening a new instance of Word or PowerPoint. Go to Debug and hit F8 and it works. 95% of the time it works fine. Not sure if it helps but I’ve been liberally adding DoEvents in case it’s a threading issue
Jon Peltier says
Johnny –
What is the code that crashes? I’ve made some changes to how I reference new instances of Word and PowerPoint.
Johnny C says
I can’t remember off the top of my head but I think it was late binding. I’ll have a look tomorrow
Jon Peltier says
Sometimes timing errors don’t respond to DoEvents. What I’ve done is separate certain things out into separate functions that return the object I want. For example, I’ll replace this:
with this:
that calls this:
The function waits until the new instance is created before it returns execution to the calling code, whereas without the function, the calling code may proceed before the instance is ready.
Mark Hughes says
Jon –
As a followup to my message above, here are four VB stumbles that a user reported:
1. Error message: Object variable or With block variable not set
Offending code: stFile = ActiveWorkbook.Name (stFile is dimensioned above as String)
2. Error message: Same as #1 above, routine is Auto_Open
Offending code: .DisplayHorizontalScrollBar = True (part of a With)
3. Error message: Same as #1 above, routine locks spreadsheet and individual sheets
Offending code: ActiveWorkbook.Unprotect Password:=”xxxxx” (password removed here, but is correct for workbook
4. Error message: Same as #1, this is the Auto_Close routine
Offending code: ActiveWorkbook.Saved = True
I just launched this same spreadsheet and experienced none of these errors. Ideas?
Jon Peltier says
Mark –
These are symptoms of having the workbook open in protected mode. Why else would a workbook be open but ActiveWorkbook (the object variable) not be set. A workbook will open on another user’s computer because it recognizes that it was last saved on a different computer (yours).
I haven’t done much with this yet, but you can test for
Application.ProtectedViewWindows.Count, thenApplication.ProtectedViewWindows(1).Workbook.Editto open it in a regular window (not protected view).Mark Hughes says
Hi back, Jon –
I think you’re saying that they are trying to run the program in Protected mode rather than Edit mode, right? Yes, I have to admit I hadn’t thought of that, as I expected that they’d know to allow editing. Hmm.
Thanks for the dope slap on this. Now, as to your suggestion:
I haven’t done much with this yet, but you can test for Application.ProtectedViewWindows.Count, then Application.ProtectedViewWindows(1).Workbook.Edit to open it in a regular window (not protected view).
To check my understanding, the if the first line comes back with something greater than zero, then the workbook is open in Protected mode. The second line will reopen (?) the workbook, or at least enable editing. Correct?
Jon Peltier says
Mark –
Yes, that’s the idea.
Mark Hughes says
Jon – I tested this, and was reminded that macros can’t be enabled until after Allow Editing has been chosen. So, it’s something else that’s causing this particular user’s Excel to kick out these errors. The hunt continues…
Jon Peltier says
All of those errors occurred when trying to access ActiveWorkbook. The code is working on the workbook that contains the code, right? What if it tried to access ThisWorkbook?
Arpeet says
What is the cause for below error,
Errors were encountered, but Excel “was able to minimally save” my file.
Jon Peltier says
Arpeet –
I’ve seen that error, but I have no idea what causes it. Excel seems to find some kind of corruption in the file, and the result after being “minimally saved” is a minimally useful file that has lost formatting, formulas, and maybe data.