工作簿和工作表

1. 工作表

1.1 引用工作表

1.1.1 使用工作表名称

WorkSheets("sheetname")
Sheets("sheetname")

1.1.2 使用工作表索引号

Sheets(index)
Worksheets(index)
'例如使用序号为1的sheet'
Sheets(1)

Worksheet对象的Index属性返回工作表的索引号

Worksheets("Sheet1").Index

最推荐的方式为WorkSheets("SheetName")的方法

1.1.3 工作表的代码名称

使用工作表代码名称引用工作表的好处是，即便工作表名称被修改，代码仍然能够正常的运行。

修改工作表代码名称有两种方式：

1. 使用代码进行修改
2. 通过属性窗口进行修改

Sheet1.Name = "newName"
或者
Worksheets("Sheet1").Name = "newName"

获取工作表代码名称的方式

WorkSheets("Sheet1").CodeName

1.1.4 使用ActiveSheet引用活动工作表

Sub printSheetMsg()
With ActiveSheet
    Debug.Print "Name:" & .Name & ",CodeName" & .CodeName _
    & ",Index:" & .Index
End With
End Sub

备注：debug.Print方法为VBA的调试方法

1.2 添加新工作表

使用WorkSheet集合或sheets集合的Add方法

Sub addSheet()
    Dim wh As Worksheet
    Set wh = Worksheets.Add(before:=Worksheets(1))
    wh.Name = "新建立的工作表"
End Sub

1.3 防止更改工作表名称

以下代码可以防止更改工作表名称。

它是写在worksheet对象下的。

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Me.Name <> "Sheet1" Then Me.Name = "Sheet1"
End Sub

代码解析：事件是指当用户执行某个特定的操作后，即可触发某个事件程序并使其自动运行。Worksheet_SelectionChange是指选中的单元格区域发生改变时，触发该事件程序

Target为Range类型，表示选定的单元格区域

工作表的其他一些事件

工作表事件说明	触发条件
DeActive	工作表由激活状态转为非激活状态
FollowHyperlink	点击工作表上的超级链接时
PivotTableUpdate	在工作簿中的透视表更新后
SelectionChange	选中的单元格区域发生改变时

1.4 判断工作簿中是否存在指定名称的工作表

'判断的主体函数'
Function blnSheetExist(ByVal strshtName As String) As Boolean
    Dim wksSht As Worksheet
    On Error Resume Next
    Set wksSht = Worksheets(strshtName)
    Debug.Print (Error(Err.Number))
	'如果没有报错，则Err.Number = 0，则说明存在实参的Sheet'
    If Err.Number = 0 Then blnSheetExist = True
    Set wksSht = Nothing
End Function
'测试函数'
Sub test()
	Dim A As Boolean
	blnSheetExist ("B")
End Sub

辅助知识点：

• Excel自定义公式：

  • Excel自定义函数的语法结构

    Function 函数名([参数]) as 数据结构	'数据结构是函数返回值的数据结构'
    	[函数名 = 返回值]					'函数体可以没有返回值'
    end Function
    

  • 传参：ByVal为传参，ByRef为传址

• 错误捕获

  • On Error Resume Next：及时发生错误，也可以继续运行

  • Err.Number：返回错误信息的编号

  • Error(Err.Number)：返回错误信息的内容

    如上面代码，如果将On Error Resume Next注释掉，就会报以下的错误。原因是worksheets中没有B这个sheetname，而Err.Number返回的是9,Error(Err.Number)返回的是下标越界

1.5 按名称排序工作表

1.5.1 WorkSheet.Move的方法

Move(Before,After)

示例：依据常规文本进行排序

Sub SortByShtName()
    Dim intSheetCount As Integer
    Dim i As Byte, j As Byte
    '冒泡排序法的临时容器'
    Dim strName As String
    '用来放置sheetname的数组'
    Dim astSheets() As String
    ' Application.ScreenUpdating = False
    intSheetCount = ThisWorkbook.Worksheets.Count
    ReDim astSheets(1 To intSheetCount)
    For i = 1 To intSheetCount
        astSheets(i) = ThisWorkbook.Worksheets(i).Name
    Next i
    
    For i = 1 To intSheetCount - 1
        For j = i + 1 To intSheetCount
            If astSheets(i) < astSheets(j) Then
                strName = astSheets(i)
                astSheets(i) = astSheets(j)
                astSheets(j) = strName
            End If
        Next j
    Next i
    For i = 1 To intSheetCount
        Worksheets(astSheets(i)).Move before:=Worksheets(1)
    Next i
End Sub

示例2：按照数字部分排序

Sub SortByShtName()
    Dim i As Byte, intSheetCount As Byte
    Dim aIntArr1() As Byte, aIntArr2() As Byte
    Application.ScreenUpdating = False
    intSheetCount = ThisWorkbook.Worksheets.Count
    ReDim aIntArr1(1 To intSheetCount)
    ReDim aIntArr2(1 To intSheetCount)
    For i = 1 To intSheetCount
        aIntArr1(i) = Val(Worksheets(i).Name)
    Next i
    
    For i = 1 To intSheetCount
        aIntArr2(i) = Application.WorksheetFunction.Large(aIntArr1, i)
    Next i
    On Error Resume Next
    For i = 1 To intSheetCount
        ThisWorkbook.Worksheets(aIntArr2(i) & "M").Move before:=Worksheets(1)
    Next i
    Application.ScreenUpdating = True
End Sub

• 辅助知识点：
  • Val：
    1. 获取字符串中前面的数字，当有非数字（不包含空格）出现时，提取取消。例如val(1 23)返回123
    2. 可以进行进制转换，将非十进制转换为十进制

1.6 限制工作表滚动区域

语法

WorkSheet.scrollArea = range范围

Sub setScrollArea()
With ActiveSheet
    .ScrollArea = ActiveWindow.VisibleRange.Address
End With
End Sub

注意：ScrollArea = ""是取消限制滚动条范围的限制。

1.7 操作受保护的工作表

添加工作表对象的保护是worksheet.protect,取消保护是worksheet.unprotect

'设置保护密码'
Sub changValue()
    With ActiveSheet
        .Unprotect ("123")
        .[A1] = "hello world"
        .Protect Password:="123"
    End With
End Sub

'或者也可以不设保护密码'
Sub changValue()
    With ActiveSheet
        .Unprotect 
        .[A1] = "hello world"
        .Protect
    End With
End Sub

• UserInterfaceOnly参数

  作用：用户不能通过界面对工作表进行修改，但可以通过VBA代码修改工作表。注意的是参数UserInterfaceOnly的设置仅在当前会话中有效。

  它仅在当前会话中有效，如果再次打开受保护的工作簿时，整张工作表会再次被保护，而不只是保护用户界面。

  下面时写在workbook_open事件中的相应代码

  Private Sub Workbook_Open()
      Sheet1.Protect UserInterfactonly:=True
  End Sub
  

1.8 在指定单元格区域中禁止显示右键菜单

正常情况，在单元格右键是有菜单的

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
    Dim bl As Boolean
    Dim obj
    Set obj = Application.Intersect(Target, Range("A1:A10"))
    Debug.Print (obj.Value)
    If Not obj Is Nothing Then Cancel = True
End Sub

代码解析：

• Application.Intersect（range1,range2[, ... ...])

  指判断参数中的所有range是否有交集，它返回的是交集的单元格对象。使用.value属性直接获得区域值

1.9 选中所有工作表

工作表的选定使用select方法

select 后面的参数为boolean，是可选参数，默认为True,就是指定对象替代当前选定对象（单选），False就是将指定对象添加到选定的对象集合中（多选）

Sub selectAllSheets()
Dim sh As Worksheet
For Each sh In ThisWorkbook.Worksheets
    sh.Select False
Next sh
End Sub

1.10 在VBA中使用工作表函数

以下是三种在VBA中使用工作表函数的方法，其中Evaluate是直接获得括号中函数表达式的返回值

Sub GetSum()
    Range("D2") = "=SUM(B1:B10)"
End Sub

Sub EvaluateSum()
    Range("D3") = Evaluate("=SUM(B1:B10)")
End Sub

Sub AppSum()
    Range("D4").Application.WorksheetFunction.Sum (Range("B1:B10"))
End Sub

2 工作簿

2.1 判断是否存在指定名称的工作簿

2.1.1 使用循环遍历进行判定

Function blnWorkbookExist(ByVal strWbName As String) As Boolean
	'blnWorkbookExist 默认情况下是False'
	debug.print(blnWorkbookExist)
    Dim wkbName As Workbook
    'Workbooks 返回所有已打开的工作簿文件'
    For Each wkbName In Workbooks
        If wkbName.Name = strWbName Then
            blnWorkbookExist = True
            Exit For
        End If
    Next wkbName
    Set wkbName = Nothing
End Function

2.1.2 使用Err.Number方式进行判断

以下代码是判断打开的工作簿中是否有【工作簿1】

Function blnWorkbookExist(ByVal strWbName As String) As Boolean
    Dim wkbName As Workbook
    On Error Resume Next
    Set wkbName = Workbooks(strWbName)
    If Err.Number = 0 Then blnWorkbookExist = True
End Function

Sub test()
Debug.Print (blnWorkbookExist("工作簿1"))
End Sub

2.2 引用工作簿

2.2.1 使用ThisWorkbook属性

Sub getBookPath()
	Debug.Print ("FullName:" & ThisWorkbook.FullName)
	Debug.Print ("Name:" & ThisWorkbook.Name)
	Debug.Print ("Path:" & ThisWorkbook.Path)
End Sub

FullName:C:\Users\YOGA\OneDrive\桌面\工作簿1.xlsm
Name:工作簿1.xlsm
Path:C:\Users\YOGA\OneDrive\桌面

2.2.2 使用ActiveWorkbook属性

2.2.3 使用工作簿名称引用

Workbooks("工作簿1.xlsx")

2.2.4 使用工作簿索引号

2.3 新建工作簿

使用工作簿集合的.Add方法可以新建一个工作簿，语法格式如下：

Workbooks.Add

2.4 导入文本文件中的数据（后期学习）

2.5 保存工作簿

2.6 使用Save方法

thisWorkbook.Save

如果仅希望将某个工作簿标记为已保存，则仅需要将工作簿的saved属性设置为True。在关闭工作簿的Saved属性值为True，则工作簿可以直接关闭，不会有是否需要保存的对话框提示。

ThisWorkbook.Saved = True

2.6.1 使用SaveAs方法

Sub SaveAsWorkbook()
'filename为可选参数，如果不指定路径，则将新文件保存到当前文件夹中'
'Password指定文件的保护密码'
ThisWorkbook.SaveAs Filename:=ThisWorkbook.Path & "工作簿1.xlsm", _
                    Password:="123123"
End Sub

2.6.2 使用SaveCopyAs方法

2.7 禁止工作簿文件另存

2.8 关闭工作簿不显示保存对话框

2.9 限制工作簿只能通过代码关闭