Excel Crashes when Running Macros

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.

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.