Excel Crashes when Running Macros

Written by Allen Wyatt (last updated August 19, 2023)
This tip applies to Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021


Don notes that he and his colleagues have been experiencing Excel crashing when running various macros. To fix the situation, all they have to do is enter the Visual Basic Editor, save, and the macro works correctly again. Don is wondering if anyone might know why this is happening.

The only way to determine what is causing a macro to crash, in this manner, is to test and debug. (Sorry; there is no way around it.) The first step is to determine which "various macros" are experiencing the problem. Once this is determined, you'll want to figure out what the macros have in common. Do they access the same workbooks? Do they utilize a particular worksheet? How about library references? What about common functions used by the macros?

The list of things to check could go on and on, but you get the idea—look for commonalities among the problem macros. Once you determine this, then it will help you determine where the problem is likely to be rooted.

The next thing to do is to test the macros under various conditions. How you exactly do this will depend on the work done by the macros. For instance, if the macros all use the InputBox function, what happens with various information entered? What happens if no information is entered? What happens if different buttons are clicked in the InputBox? What happens if totally stupid information ("Well, nobody would ever enter something like that!) is entered?

Closely related to this testing is figuring out what you expect to be returned by the VBA (and worksheet) functions you may use in your macros. For instance, your macro may need to determine the name of the current workbook. This is done (obviously) with the .Name property, but under some circumstances, the name may not be what you would expect—like when the workbook has yet to be saved. You'll need to think through what you expect from the functions and then question whether your expectations will, under all circumstances, be met.

Finally, you should strongly consider adding some error checking code to your macros. This code could not only gracefully recover from an error, but it could be written to display an error code or write the error code to a text file. This would help identify the problem causing the crash and, thereby, determine how to recover.

If all of this sounds like a lot of work, it can be. Going through the analyzing, testing, and debugging can help make your macros stronger, however.


If you would like to know how to use the macros described on this page (or on any other page on the ExcelTips sites), I've prepared a special page that includes helpful information. Click here to open that special page in a new browser tab.

ExcelTips is your source for cost-effective Microsoft Excel training. This tip (13907) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, Excel in Microsoft 365, and 2021.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...


Inserting Dashes between Letters in Words

Sometimes typing isn't straight typing. Sometimes you need to perform special tasks, such as putting dashes between ...

Discover More

Sorting Data Containing Merged Cells

When formatting the layout of your worksheet, Excel allows you to easily merge adjacent cells together. This can cause ...

Discover More

Getting User Input in a Dialog Box

Want to get some input from the users of your workbooks? You can do it by using the InputBox function in a macro.

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Converting Text to Numbers

Import information from a program external to Excel, and your numbers may be treated as text because of the way that the ...

Discover More

Using SUM In a Macro

Want to use a worksheet function (such as SUM) from within a macro? Here's how easy it is to accomplish the task.

Discover More

Aborting a Macro and Retaining Control

If you need to exit a macro before it is finished running, you can do it using a brute force method, or you can build in ...

Discover More

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.


If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] (all 7 characters, in the sequence shown) in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 9 + 4?

2023-08-21 05:24:04


Remember, when it comes to resolving seemingly baffling crashes, the internet is your friend. When faced with a seemingly incomprehensible problem with Excel (or Access) VBA, there's usually someone out there who has had a similar experience and figured out a solution.

2023-08-19 15:20:58

J. Woolley

The Tip mentions debugging. For more on that subject,
see https://excelribbon.tips.net/T007732_Debugging_a_Macro.html
and https://www.myonlinetraininghub.com/debugging-vba-code
The Tip also mentions the .Name property. In particular, a macro that references ActiveWorkbook.Path, ActiveWorkbook.Name, or ActiveWorkbook.FullName might not work as expected if the active workbook is new and unsaved. In this case ActiveWorkbook.Path is blank, which is one way to test the workbook's status.

This Site

Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.

Newest Tips

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.