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


2

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.

Note:

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. ...

MORE FROM ALLEN

Easily Adding Blank Rows

Want to add a bunch of blank rows to a your data and have those rows interspersed among your existing rows? Here's a ...

Discover More

Pulling Formulas from a Worksheet

The formulas in your worksheet can be displayed (instead of formula results) by a simple configuration change. You can ...

Discover More

Disappearing Readability Statistics

Word provides some handy document analysis that can help improve your writing. One such tool is the readability ...

Discover More

Save Time and Supercharge Excel! Automate virtually any routine task and save yourself hours, days, maybe even weeks. Then, learn how to make Excel do things you thought were simply impossible! Mastering advanced Excel macros has never been easier. Check out Excel 2010 VBA and Macros today!

More ExcelTips (ribbon)

Determining the Current Directory

When you use a macro to do file operations, it works (by default) within the current directory. If you want to know which ...

Discover More

Expiration Date for Excel Programs

If you use Excel to create a macro-based application, you may want to make sure that your programs cease working after a ...

Discover More

Macro for Month Name

Need to know how to generate a full month name based on a date? It's easy to do, as discussed in this tip.

Discover More
Subscribe

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

View most recent newsletter.

Comments

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 one more than 7?

2023-08-21 05:24:04

DaveS

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
Subscribe

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.