Excel VBA入门(4)

Workbook工作簿的常用功能：

1. 新建工作簿

1. Dim wb As Workbook
2. Application.SheetsInNewWorkbook = 1 '设置初始工作簿中的工作表数
3. Set wb = Application.WorkBooks.Add
4. wb.Worksheets(1).name = '表1' '给第一个工作表设置名称
5. Application.SheetsInNewWorkbook = 3

2.用Excel对话框打开Excel文件

    Dim fileInfo As String
    fileInfo = Application.GetOpenFilename('Excel 工作簿(*.xlsx), *.xlsx')
    MsgBox fileInfo

如果用户选择了一个Excel文件，则fileInfo为该文件的全路径。若未选择，则fileInfo为False

3. 备份（另存为）Excel文件

1. Dim wb As Workbook
2. Set wb = Application.WorkBooks(1) '原文件名为vba.xlsx
3. wb.Activate
4. wb.SaveCopyAs wb.path & '\' & 'Bak_' & wb.name '备份文件名为Bak_vba.xlsx

4. 用Excel对话框保存Excel文件

    Dim fileInfo As String
    fileInfo = Application.GetSaveAsFilename('excelFile', 'Excel 工作簿(*.xlsm),*.xlsm')
    If Not fileInfo = 'False' Then
        ActiveWorkbook.SaveAs Filename:=fileInfo
    End If
        MsgBox '文件未保存'
    Else

5. 设置工作簿的大小（并非Excel窗口的大小）虽然使用的是Application的属性ActiveWindow，但该属性对应的却是一个Workbook窗口

1. Dim win As Window
2. Dim winState As Long
3. Dim winWidth As Double
4. Dim winHeight As Double
6. Set win = Application.ActiveWindow
7. win.Activate
8. With win
9. .WindowState = xlMinimized '最小化工作簿，但是Excel程序窗口没有最小化
10. End With
11. With win
12. winState = .WindowState
13. winWidth = .Width
14. winHeight = .Height
15. End With
17. Debug.Print 'WinState: ' & winState
18. Debug.Print 'WinWidth: ' & winWidth
19. Debug.Print 'WinHeight: ' & winHeight

6. 冻结窗格。和上述一样依然使用Application.ActiveWindow来操作工作簿

    Dim win As Window
    Set win = Application.ActiveWindow
    With win
        .Split = True
        .SplitColumn = 1         '若设置为0，表示不冻结列
        .SplitRow = 3
        .FreezePanes = True     '如果不设置FreezePanes将会在工作表中出现粗分割线
    End With

7. 修改工作簿文件名

1. Dim fileName As String
2. fileName = 'C:\book.xlsx' '文件不存在会报错
3. Name fileName As 'C:\book1.xlsx' '工作簿应处于关闭状态，而且路径应当一致
4. Debug.Print 'Renamed OK!'

Workbook主要事件：

Workbook事件比起之前的Application事件写起来方便些。我们直接使用ThisWorkbook对象的事件（也是一个Workbook对象）

如下图，双击ThisWorkbook，在右侧的代码区上方直接选择需要实现的事件，然后在自动生成的事件函数中编写代码

1.BeforeClose事件

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
        MsgBox '关不掉了'
        Cancel = True
    End Sub

该事件在关闭工作簿前触发

2.BeforeSave事件

1. Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
2. If Not SaveAsUI Then
3. MsgBox '保存不了了'
4. Cancel = True
5. End If
6. End Sub

在单击保存按钮或者Ctrl+S时触发

3.WindowActive事件

    Private Sub Workbook_WindowActivate(ByVal Wn As Window)
        MsgBox '激活工作簿'
        Wn.width = 100
    End Sub