To close a workbook with VBA, call its Close method: use Workbooks("Report.xlsx").Close SaveChanges:=True for a named workbook, or ThisWorkbook.Close SaveChanges:=False for the workbook containing the macro. Choose whether to save deliberately: True saves changes, while False discards them.
What Closing a Workbook Does
In Excel VBA, a workbook is an object, and closing it means calling that workbook’s Close method. The method removes the workbook from the open workbooks in the current Excel session. It does not, by itself, quit Excel or close other workbooks. To exit Excel, VBA has a separate application-level method, Application.Quit; use that only when you intend to close the entire Excel application.
The method accepts optional arguments. The one most people need is SaveChanges. Providing it makes the macro’s intention clear and avoids leaving Excel to ask what to do with edits. If you omit the argument, Excel may display a save prompt when the workbook has unsaved changes. A prompt can interrupt an automated process or make it seem as if the macro has stopped.
Closing a workbook is not the same as deleting its file. A saved workbook remains on disk after it closes. If you close it with unsaved changes, those changes are either saved or discarded according to the option you choose.
Choose the Workbook You Actually Mean
The safest reference depends on which workbook should close. Three common references are:
ThisWorkbookmeans the workbook that contains the running VBA code. It remains the same even if the user activates another workbook.ActiveWorkbookmeans the workbook currently active in Excel. It can change when the user selects another workbook or when a macro opens or activates one.Workbooks("Report.xlsx")refers to an open workbook by its name in Excel’sWorkbookscollection. Include the extension when it is part of the displayed workbook name.
Prefer ThisWorkbook when a macro stored in a workbook should close its own file. Use a named reference when the target is known and should not depend on which window happens to be active. Use ActiveWorkbook only when closing whichever workbook is currently selected is genuinely the intended behavior. A common mistake is to use it in a macro that runs from a personal macro workbook: the active workbook may be the user’s report, not the file containing the macro.
Basic VBA Examples
These examples show the main patterns. Save a separate copy of important files before running a macro: VBA actions cannot be undone with Ctrl+Z. Run code only when you understand which workbook it targets and whether it will save or discard edits.
To close a named workbook and save its changes:
Sub CloseNamedWorkbookAndSave()
Dim wb As Workbook
' Use the exact name of an open workbook.
Set wb = Workbooks("Report.xlsx")
wb.Close SaveChanges:=True
End Sub
To close the workbook containing the macro without saving its changes:
Sub CloseMacroWorkbookWithoutSaving()
Dim wb As Workbook
' ThisWorkbook is the file that stores this VBA code.
Set wb = ThisWorkbook
wb.Close SaveChanges:=False
End Sub
To close the active workbook and let Excel ask about unsaved changes, omit SaveChanges:
Sub CloseActiveWorkbookWithPrompt()
Dim wb As Workbook
' The active workbook may differ from the macro's workbook.
Set wb = ActiveWorkbook
If Not wb Is Nothing Then wb.Close
End Sub
That last pattern is useful when a human should make the save decision, but it is less suitable for unattended automation. The condition also avoids attempting to close a workbook if no workbook is active.
Decide Whether to Save Before Closing
Choose the save behavior based on what the macro is meant to accomplish, not simply on whether a prompt is inconvenient:
- Use
SaveChanges:=Truewhen the edits should be retained and the workbook already has a usable file path. - Use
SaveChanges:=Falsewhen the changes are intentionally temporary and should be discarded. - Leave the argument out when a user should review Excel’s save prompt and decide interactively.
If the workbook has never been saved, or needs a new file name or location, explicitly save it before closing. That makes the destination clear and separates saving from closing. For example, with a workbook object named wb, a macro can call its SaveAs method with a full file path, then close it. Confirm the intended file format and extension as well; a macro-enabled workbook generally needs the .xlsm format to retain VBA code.
SaveChanges:=True is not a way to bypass file permissions. A read-only workbook, a location where the user lacks write access, or another save problem can prevent changes from being stored or require a different destination. Check whether the workbook is read-only and handle save errors where a process must be reliable. If preserving the edits is essential, do not suppress the error and assume the save succeeded.
Close Several Workbooks Safely
When a macro must close multiple open workbooks, iterate backward through the collection. Closing an item changes the collection, so counting upward can skip a workbook or refer to a different item after each close. This example closes every open workbook except the one containing the code, discarding unsaved changes in the others:
Sub CloseOtherWorkbooksWithoutSaving()
Dim i As Long
Dim wb As Workbook
' Save a copy first; this discards unsaved edits in other workbooks.
For i = Application.Workbooks.Count To 1 Step -1
Set wb = Application.Workbooks(i)
If Not wb Is ThisWorkbook Then
wb.Close SaveChanges:=False
End If
Next i
End Sub
Review the target rule before using a bulk-close macro. If the code is stored in PERSONAL.XLSB or an add-in, ThisWorkbook refers to that file, not to the report you may want to keep. A safer production macro often identifies a specific workbook by name or asks the user to confirm the list of files to close. For a save-everything workflow, save each intended workbook explicitly and check for errors instead of silently discarding changes.
Prompts, Errors, and Safety
A frequent shortcut is turning off Excel alerts with Application.DisplayAlerts = False before closing. This suppresses some confirmation dialogs, but it does not mean “save everything.” For an unsaved workbook, Excel’s default response to the close confirmation is to discard changes. An alert-suppression setting can therefore cause data loss if the macro closes the wrong workbook or if the save decision was not made first.
If alerts must be disabled for a specific operation, restore the original setting even when an error occurs. Avoid leaving Excel in a changed application state. Better still, save or discard deliberately with the workbook’s Close argument and avoid suppressing dialogs unless the automation has a clear reason to do so.
Common problems include:
- “Subscript out of range”: The workbook name does not match an open workbook. Check its exact name, including the extension, or verify that it is open before referring to it.
- A save prompt still appears: The macro may have omitted
SaveChanges, or it may be closing a different workbook than expected. - The macro stops after closing its own file: Closing the workbook also removes the VBA project that contains the running code. Put the close operation at the end and do not rely on later code in that workbook to run.
- Changes disappear: The code may use
SaveChanges:=False, or alerts may have been suppressed. Check the target and save policy before rerunning it.
Macros can make changes that Ctrl+Z cannot reverse. Keep a backup, use a test copy when developing a bulk-close routine, and enable macros only for files you trust.
Excel Desktop, Mac, and the Web
VBA workbook-closing code is for Excel’s desktop application. Excel for the web does not run VBA macros; open the workbook in the desktop Excel app to run or edit its VBA code. Microsoft 365 for Windows is the assumed environment here. Excel for Mac also supports VBA, although some operating-system features and file paths differ. A Windows path such as C:\Reports\Report.xlsx should not be assumed to work unchanged on a Mac.
The workbook references and basic Close method shown above are standard VBA patterns, but a macro that depends on Windows-only automation may need changes for Mac. If a close routine is launched from a button or event, also consider what should happen when the workbook is closed: code stored in that workbook cannot serve as a reliable place for actions that must continue afterward.
Frequently Asked Questions
How do I close the workbook that contains the macro?
Use ThisWorkbook.Close, and specify SaveChanges:=True or SaveChanges:=False to choose what happens to edits. Put the close call at the end of the procedure because the workbook also contains the running VBA project.
How do I close a workbook without saving?
Call its Close method with SaveChanges:=False. This discards unsaved changes, so confirm the workbook reference and keep a backup if the edits might matter.
How can I close a workbook without a save prompt?
Specify the save decision in the close call. Use True to save or False to discard. Do not rely on disabling alerts as a substitute for choosing the right behavior.
Does closing a workbook close Excel?
No. Closing a workbook removes that file from the open workbooks but leaves Excel running. Application.Quit exits Excel and can affect every workbook open in that Excel instance.
Can VBA close a workbook in Excel for the web?
No. Excel for the web does not execute VBA macros. Use the desktop Excel application to run a macro that closes a workbook.
Related
- Jira Merge Table Cells: Steps and Limitations
- Conditional Formatting: Highlight Duplicates in Excel
- Data Validation Testing: Methods and Examples
Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.
More in VBA & macros
- Excel VBA Find Value in Range: Examples and TipsTo find a value in an Excel range with VBA, use the range’s Find method, then check whether…
- Excel VBA InputBox Types and When to Use EachExcel VBA has two commonly used input boxes: the VBA InputBox function, which returns text, and Application.InputBox, which…
- VBA Array Cells: Read and Write Ranges EfficientlyIn Excel VBA, you can read a block of worksheet cells into a Variant array with one assignment,…
- VBA Pivot Table References: Objects, Fields, and ExamplesIn Excel VBA, reference a PivotTable through its worksheet, using Worksheets("SheetName").PivotTables("PivotTableName"); then use that PivotTable object to access…
- Excel VBA: Keep a UserForm on TopTo keep an Excel VBA UserForm above other windows on Windows, call the Windows API function SetWindowPos with…
- Google Sheets Macros: How They Work and What They Can DoYes. Google Sheets has macros that can record repeated actions and replay them later; unlike Excel macros, Sheets…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.