VBA Learning Notes Workbook

Common features of the workbook workbook:

1. Create a new workbook

1. Dim WB as Workbook
2. Application.sheetsinnewworkbook = 1 ' Sets the number of worksheets in the initial workbook
3. Set WB = Application.WorkBooks.Add
4. Wb. Worksheets (1). Name = "Table 1" to set the name of the first sheet
5. Application.sheetsinnewworkbook = 3

2. Open an Excel file with the Excel dialog box

1. Dim fileInfo as String
2. FileInfo = Application.getopenfilename ("Excel Workbook (*.xlsx), *.xlsx")
3. MsgBox FileInfo

If the user selects an Excel file, FileInfo is the full path to the file. If not selected, the FileInfo is False

3. Backup (Save As) Excel file

 

1. Dim WB as Workbook
2. Set WB = application.workbooks (1) ' original file name is Vba.xlsx
3. Wb. Activate
4. Wb. SaveCopyAs Wb.path & "\" & "Bak_" & wb.name ' backup file named Bak_vba.xlsx

4. Save the Excel file with the Excel dialog box

1. dim fileinfo  as string  
2. fileinfo = application.getsaveasfilename ( "Excelfile",  "excel  Workbook (*.xlsm), *.xlsm")   
3. if not fileinfo =  "False"  then  
4.      activeworkbook.saveas filename:=fileinfo  
5. End < span class= "keyword" >if  
6.     MsgBox 
7. else  

5. Set the size of the workbook (not the size of the Excel window) although using the Application Property ActiveWindow, the property corresponds to a workbook window

2. Dim win as Window
4. Dim winstate as Long
6. Dim winwidth as Double
8. Dim winheight as Double
12. Set win = Application.ActiveWindow
14. Win. Activate
16. With win
18. . WindowState = xlminimized ' minimized workbook, but Excel program window is not minimized
20. End with
22. With win
24. Winstate =. WindowState
26. Winwidth =. Width
28. Winheight =. Height
30. End with
34. Debug.Print "Winstate:" & Winstate
36. Debug.Print "Winwidth:" & Winwidth
38. Debug.Print "Winheight:" & Winheight

6. Freeze Panes. Same as above still using Application.ActiveWindow to manipulate workbooks

1. dim win as  window  
2. set win =  application.activewindow  
3. with win   
4.     . Split = true  
5. &NBSP;&NBSP;&NBSP;&NBSP;. Splitcolumn = 1         
6.     . splitrow = 3  
7. &NBSP;&NBSP;&NBSP;&NBSP;. Freezepanes = true      If you do not set FreezePanes, a thick split line will appear on the worksheet   
8. end  with  

7. Modify Workbook file name

1. Dim fileName as String
2. filename = "C:\book.xlsx" file does not exist will error
3. Name fileName as "C:\book1.xlsx" Workbook should be turned off, and the path should be consistent
4. Debug.Print "Renamed ok!"

Workbook Main Events:

Workbook events are easier to write than previous application events. We directly use the ThisWorkbook object's event (also a Workbook object)

For example, double-click ThisWorkbook, select the event you want to implement directly above the code area on the right, and then write code in the auto-generated event function

1.BeforeClose Events

1. Private Sub workbook_beforeclose (Cancel as Boolean)
2. MsgBox "Can't shut it down."
3. Cancel = True
4. End Sub

This event is triggered before the workbook is closed

2.BeforeSave Events

2. Private Sub workbook_beforesave (ByVal SaveAsUi As Boolean, Cancel as boolean)
4. If not saveasui Then
6. MsgBox "can't be saved."
8. Cancel = True
10. End If
12. End Sub

triggered when the Save button or Ctrl+s is clicked

3.WindowActive Events

1. Private Sub workbook_windowactivate (ByVal Wn as Window)
2. MsgBox "Activating Workbook"
3. Wn.width = 100
4. End Sub

VBA Learning Notes Workbook