To use VBA to close a workbook, use the Workbook.Close method, and to exit Excel, use the Application.Quit method.
Here are some code examples: Close the active workbook and prompt whether to save if there are changes in the workbook:
Copy the code code as follows:
Sub CloseWorkbook()
ActiveWorkbook.Close
End Sub
If you want to avoid prompts, you can add the "SaveChanges" parameter, such as directly saving and closing the workbook:
Copy the code code as follows:
Sub ClostAndSaveWorkbook()
ActiveWorkbook.Close Savechanges:=True
End Sub
Change "True" in the above code to "False" to close the workbook directly without saving.
Close all open workbooks and prompt whether to save:
Copy the code code as follows:
Sub CloseAllWorkbooks()
On Error Resume Next
Workbooks.Close
End Sub
Exit Excel and be prompted to save the changed workbook:
Copy the code code as follows:
SubQuitExcel()
Application.Quit
End Sub
Save the active workbook and quit Excel:
Copy the code code as follows:
Sub SaveActiveWorkAndQuit()
ActiveWorkbook.Save
Application.Quit
End Sub
The following code will not prompt whether to save the workbook when exiting Excel.
Save all open workbooks and quit Excel:
Copy the code code as follows:
Sub SaveAllAndQuit()
For Each wbk In Workbooks
wbk.Save
Next
Application.Quit
End Sub
Exit Excel without saving any changes:
Copy the code code as follows:
SubQutiAndNoAlerts()
Application.DisplayAlerts = False
Application.Quit
End Sub
Here are some common operations in VBA:
Open a new workbook
Copy the code code as follows:
Workbooks.Add
Get the name of the first workbook
Copy the code code as follows:
Workbooks(1).Name
Get the number of open workbooks
Copy the code code as follows:
Workbooks.Count
Activate the second open workbook
Copy the code code as follows:
Workbooks(2).Activate
Activate workbook Chap02.xls
Copy the code code as follows:
Workbooks("Chap02.xls").Activate
The currently active workbook is saved as NewChap.xls
Copy the code code as follows:
ActiveWorkbook.SaveAs Filename:="NewChap.xls"
Close the first workbook
Copy the code code as follows:
Workbooks(1).Close
Close the currently active workbook without saving changes
Copy the code code as follows:
ActiveWorkbook.Close SaveChanges:=False
Close all open workbooks
Copy the code code as follows:
Workbooks.Close
If you ran the last example, all of your workbooks are now closed.
Be sure to open a new workbook before you use it on a worksheet.
When you add a single worksheet, you must know how to add a new worksheet to the workbook, know how to select a worksheet or a group of worksheets, and know how to name, copy, move, and delete worksheets.
In VB, each task requires a specialized method or property.
Add a new worksheet
Copy the code code as follows:
Worksheets.Add
Get the name of the first worksheet
Copy the code code as follows:
Worksheets(1).Name
Select the worksheet named "Sheet3"
Copy the code code as follows:
Worksheets(3).Select
Select the first, third and fourth worksheets
Copy the code as follows: Worksheets(Array(1,3,4)).Select
Activate the worksheet named "Sheet1"
Copy the code code as follows:
Worksheets("Sheet1").Activate
Move sheet "Sheet2" before sheet "Sheet1"
Copy the code code as follows:
Worksheets("Sheet2").Move Before:=Worksheets("Sheet1")
Rename worksheet "Sheet2" to "Expenses"
Copy the code code as follows:
Worksheets("Sheet2").Name = "Expenses"
Get the number of worksheets in the current workbook
Copy the code code as follows:
Worksheets.Count
Delete the worksheet "Expenses" in the current workbook
Copy the code code as follows:
Worksheets("Expenses").Delete