How to Close Excel using VBA

There are a number of ways to close Excel using VBA, each designed for different scenarios. This post will guide you through these methods, helping you understand when and why to use each in your VBA code.

Closing Excel with VBA

Excel offers several VBA options for closing down. It’s essential to know your goal because each option leads to slightly different outcomes.

For instance, when you close a workbook using VBA, it usually means just the workbook closes while the Excel application stays open. However, if you close Excel itself through VBA, it will shut down the entire application. By default, this action also closes any open workbook.

There are times when you might need to close a specific workbook. For example, you could be working with a data file from which you’ve extracted data. In such cases, your aim might be to close only this particular data file, leaving the main report workbook open. This specificity should be reflected in your VBA code.

Close Everything: All Workbooks and Excel Application

Application.Quit

This command is the all-encompassing solution in VBA to close every open workbook and the Excel application itself.

It is crucial to ensure that any changes you want to keep are saved before this code executes. Excel will prompt you to save any unsaved work, unless you’ve disabled alerts.

This is a straightforward, yet powerful command that leaves no open files or instances of Excel running.

Close a Specific Workbook

Workbooks("Name of Workbook").Close

To target a specific workbook for closure, this script is your go-to option.

It operates based on the workbook’s name, eliminating the need for a file path, which if specified incorrectly, can lead to a ‘run-time error ‘9’’ (subscript out of range). Excel intuitively understands which open workbook you’re referring to by its name.

In professional VBA practices, it’s advisable to control the save behaviour explicitly when closing a workbook. If alerts are off, Excel will close the workbook without saving changes. To be more precise in your coding, you can specify the save settings:

Workbooks("Name of Workbook").Close SaveChanges:=True

or

Workbooks("Name of Workbook").Close SaveChanges:=False

These additions give you complete control over whether changes to the workbook are saved or discarded.

Close All Workbooks, But Keep Excel Open

Workbooks.Close

This script is designed for scenarios where you need to close all open workbooks but want to keep the Excel application running. Similar to the other commands, Excel will prompt you to save any changes unless you’ve disabled alerts or have already saved the files.

That’s all the ways to close Excel using VBA.

Summary

In conclusion, understanding how to close Excel using VBA is an invaluable skill that enhances efficiency and precision in your work.

We’ve covered the essential commands: Application.Quit to close everything, specific commands like Workbooks(“Name of Workbook”).Close to close individual workbooks, and Workbooks.Close to shut all workbooks while keeping Excel open.

Each method serves a unique purpose and provides you with the flexibility to manage your Excel environment according to your specific needs.

Remember, incorporating these VBA scripts into your routine not only saves time but also allows for a more organized and controlled way of managing workbooks. Whether you’re a beginner or an experienced Excel user, these skills are fundamental in making the most out of Excel’s powerful capabilities.

Keep Excelling,

There’s more to Excel than you realise. For your next reading learn how to create a drop down list in Excel and start building some amazing Excel dashboards today!