How to Crash Excel

This week, I’ve been working in an Excel 2007 file that has several named Excel Tables. After adding a column in one table, I copied the entire worksheet column.

ExcelTableCrash01

Next, I tried to paste it into another worksheet, where there was a similar table.

ExcelTableCrash02

That didn’t go too well. After a few minutes of staring at the hourglass, I gave up, and closed Excel in the Task Manager.

ExcelTableCrash03

Today, I tried to repeat the column copy and paste, forgetting about the previous problem.

Sure enough, Excel crashed again. Well, technically, I guess it’s a hang, rather than a crash, but it’s still annoying.

A Smaller Named Excel Table

In a smaller workbook, with smaller tables, the copy eventually completed, but with strange results. There was a strange message in the Status Bar.

ExcelTableCrash04

Eventually, the copy completed, but instead of the ten rows from the original table, the paste filled the entire column, so the named Excel Table ended in the last row.

ExcelTableCrash05

Successful Copy and Paste

Instead of copying and pasting the entire column, you can copy and paste the named Excel table column.

  • To select the table column, click, the top of the table heading cell, instead of the column heading button.
  • ExcelTableCrash07
  • Then, to paste into the other table, right-click the heading cell, and paste.
  • ExcelTAbleCrash08

Or you can copy the cells, and paste them, instead of copying and pasting the column.

How Do You Crash Excel?

Enough about my problems! What’s your favourite way to crash/hang Excel?
________________

24 thoughts on “How to Crash Excel”

  1. I had a good chuckle at the title of this entry. All I could think was “I don’t need to know how to crash Excel, I’m quite adept at it.” 🙂

    Not sure if it’s technically a crash but it always results in a restart of Excel and often in a reboot of my machine. I am an avid keyboard user and I know all my frequently used shortcut keys or menu key sequences to get to my desired whatever (needless to say, I STILL don’t like the Ribbon). Sometimes I will accidentally hit some combination of keys that basically randomizes or nullifies my keystrokes and their results.

  2. Tanya, thanks for sharing your technique for crashing Excel — it’s great to hear from an expert. 😉

    And I know what you mean about those shortcut keys! Today I selected a group of cells, and pressed Ctrl+; to enter the date. But, I accidentally pressed Ctrl+L, and created a tiny table, that messed up the worksheet. And there was no Undo available!

  3. I have one worksheet that if not saved when prompted at closing, will crash excel every time. I have no idea why it does this, but can reproduce it 100% of the time. It never fails!

  4. I have found that the surest way to crash Excel is to have an impatient QA waiting for a printout of a test report. And if it’s a QA *manager*, it might even hang the entire computer…

  5. Let’s see, my most successful excel hanging would be a ranking formula. Use arrays and subtotal for dynamic ranking. Three ranks per row, ~80k rows. I let it run over the weekend, came back on Monday and scrapped the whole thing.

  6. I find the surest way to crash Excel (send it into an infinite unbreakable loop) is to forget to save a piece of code I am writing before running it. Early on in development, I usually forget a trap to stop this and end up clicking the Run button a micro-second before I realize I didn’t save the code… BAM!… I’m in that damn infinite unbreakable loop which means, of course, that the code is completely lost.

  7. I’ve recently created a right-click menu customizer. The trickiest part is getting a list of all the controls, even ones not currently on any toolbar. I tried this code, but it crashes on 5957 (and then on later ones). It brings up the “Excel has stopped working” with it’s generous offer to save and restart. This is in Excel 2010, can’t remember if it’s the same in 2003. The On Error Resume Next skips over all the non-existent controls (and I think some that won’t work on a shortcut bar) but still crashes at 5957:

    Sub ListAllControls()

    Dim i As Long
    Dim j As Long
    Dim cbar As Office.CommandBar
    Dim ctl As Office.CommandBarControl

    Set cbar = Application.CommandBars.Add(temporary:=True)
    Sheet1.Cells.Clear
    For i = 1 To 6000
    On Error Resume Next
    Set ctl = cbar.Controls.Add(ID:=i)
    If Err.Number = 0 Then
    j = j + 1
    Sheet1.Cells(j, 2) = ctl.Caption
    Sheet1.Cells(j, 1) = i
    End If
    On Error GoTo 0
    Next i

    End Sub

  8. Just wanted to show an obscure way to crash Excel too:

    A convoluted way to crash Excel (2007/2010) is by having a userform which is tied to a collection of instances of a class module. Make sure you forget to set all instances of the class to nothing when closing the userform. Closing Excel then causes a crash.

  9. @ Jan;
    Can you expand on what you mean by “userform which is tied to a collection of instances of a class module”? I have a spreadsheet that I added an userform to so that the user could jump between sheets, modify the views, etc, and ever since it crashes not when I close, but after a couple of saves. Without the userform, it seems fine, saves properly, and macros run fine. I am using XL2007, with xlsm extensions. This is my first 2007 userform, so not very familiar with the changes.

  10. Slightly off-topic, but the absolute worst is when a workbook or sheet within a workbook corrupts, for no reason. This happens rarely but inexorably. Excel 2007 thank goodness is much better at saving/repairing corrupted files, but even then sometimes you get files that are beyond help.

    If you are lucky you can spot it coming, but even then you have to go through a tedious process of copying and pasting (via VBA tools mostly) all the formulae and formatting into a non-corrupted workbook in a way that doesnt take the bad ju-ju with it….

    It doesn’t get much worse than this, when it strikes.

    Corrupt undeleteable styles and range names are a pain in the a** too!

  11. @Jan

    Also having the same issue as Robert.

    the workbook works fine in .xlsm and .xlsx but crashes if try to save in .xls after the form has run. could you clarify what that issue is and how to solve it please?

  12. Create a database on excel (bad idea # 1, but try convincing you Job Manager to think otherwise outlining details of various assets: location, value, etc etc around 30 columns and 10,000 rows; attempt a classification methodology using VLOOKUP, feed it into a pivot table, add a drop down menu to select various scenarios and this is the clincher, try automating the whole thing to work at the click of a button using VBA and watch excel crash

  13. I find I can consistently crash Excel 2011 when I fail to select a cell or set of cell before invoking the “Create table from Excel” option in the Table tab. If I click on (for example) A1, then it is easy to create a table. If A1 is highlighted but not specifically selected, the program goes away and never comes back.

    I tried to record a macro to do this because I have several sheets that I turn into tables and found that the recorded macro cannot run successfully even over the same sheet and data.

    The only workaround I’ve found is to do it over.

  14. Validation “Gotcha”
    When using VBA to create validation, we can put values in the validation object that we shouldn’t – or – leave out an important piece.
    Setup: Using VBA, add VALIDATION.TYPE to 3 (xlValidateList) to a cell and either neglect to add a value to FORMULA1 or add a value that is not a comma separated list or a reference to a single column range. Save and close the workbook. When the workbook opens and the worksheet with the improper validation is displayed the workbook will crash. I have replicated this on a few different versions of XL and diffent PCs.
    Counter Measure: Assuming you can get the workbook to open, goto VBA and either delete the offending validation:
    .VALIDATION.DELETE-or- correct it:
    .VALIDATION.MODIFY xlValidateList, Formula1:="1,2"

  15. I want to know about the excel file.
    My doubt is, Is it possible to crash or corrupt automatically excel file.
    When we open the same file second time in future.
    If this will possible please tell me the procedure.
    I want to share a excel file with a colleague but for single time use.
    Avis

  16. Excel just started crashing when I click on the red X of the running userform. It has happened in the past but not for a few months. I’m wondering what change I could have made to start causing this. I just added two multipage controls to the form and moved all my other controls onto them. Could it be that? I just added a treeview control which seems to be working fine. I’m puzzled.

  17. I’ve got an Access database I use to run a weekly report. My VBA coding exports a couple of queries to an Excel workbook, performs a lot of formatting, outputs the file to pdf, then uploads the Excel and pdf files to a SharePoint-linked folder. This has been running fine for years, but yesterday it started throwing an ‘Automation error–the server threw an exception’ message. I figured out that the error was due to Excel crashing at the point the coding attempted to save the final .xlsx file. I put a break point in the code to try and manually save the Excel file, but it still crashed. I tried everything I could think of (removing add-ins, opening the recovered file as an administrator, etc.); I thought I was going to have to file a ticket in the great black hole of IT. I finally realized the problem was due to the presence of a number of saved name ranges in the file. I deleted the ranges (both manually and then as part of the coding), and everything worked fine. Never mind that the past reports have all had named ranges (every week for the last 5-6 years). This week it just broke everything.

    1. Glad you were able to get it working again, Kristen, and without IT’s help! It’s so frustrating when a long-serving process suddenly stops working, and usually at the most inconvenient time.

Leave a Reply to Anonymous Cancel reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.