VBA笔记10：技巧与案例

1.创建新的工作簿

知识点：Workbook、Worksheet、SaveAs

Sub WbAdd()
    '创建1个名为"员工花名册"的工作簿，并保存到本工作簿所在的文件夹中
    Dim Wb As Workbook, sht As Worksheet '定义一个Workbook对象和一个Worksheet对象
    Set Wb = Workbooks.Add               '新建一个工作簿
    Set sht = Wb.Worksheets(1)
    With sht
        .Name = "花名册"        '修改第1个工作簿的标签名
        '设置表头
        .Range("A1:F1") = Array("序号", "姓名", "性别", "出生年月", "参加工作时间", "备注")
    End With
    Wb.SaveAs ThisWorkbook.Path & "\员工花名册.xlsx"  '保存到当前工作簿所在路径
    ActiveWorkbook.Close
End Sub

2.工作簿是否已打开

知识点：For循环、Exit Sub、Workbooks.Count

Sub IsOpen()
    Dim i As Integer
    For i = 1 To Workbooks.Count
        If Workbooks(i).Name = "VBA练习_Range.xlsm" Then
            MsgBox "文件已打开"
            Exit Sub
        End If
    Next
    MsgBox "文件没打开"
End Sub

3.工作表是否存在、创建&移动

知识点：For Each循环、Move

Sub IsOpen3()
    Dim sht As Worksheet
    For Each sht In Worksheets
        If sht.Name = "测试工作表" Then
            MsgBox "Yes"
            Worksheets("测试工作表").Move before:=Worksheets(1)
            Exit Sub
        End If
    Next
    MsgBox "None"
    Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "测试工作表"
End Sub

另一种写法，知识点：On Error Resume Next、Is Nothing
注意：Is Nothing配合If语句肯定会报错（下标越界），所以才加了On Error Resume Next

Sub IsOpen4()
    On Error Resume Next '当出现报错的时候，继续执行程序
    If Worksheets("测试工作表") Is Nothing Then
        Worksheets.Add(after:=Worksheets(Worksheets.Count)).Name = "测试工作表"
    Else
        Worksheets("测试工作表").Move before:=Worksheets(1)
    End If
End Sub

4.向未打开的工作簿里录入数据

知识点：With语句、CurrentRegion、Resize、:=

Sub WbInput()
    Dim wb As String, xrow As Integer, arr
    wb = ThisWorkbook.Path & "\员工花名册.xlsx"
    Workbooks.Open (wb)
    With ActiveWorkbook.Worksheets(1)
        xrow = .Range("A1").CurrentRegion.Rows.Count + 1
        arr = Array(xrow - 1, "张敏", "女", #7/8/1987#, #9/1/2010#, "10年新招")
        .Cells(xrow, 1).Resize(1, 6) = arr
    End With
    ActiveWorkbook.Close savechanges:=True
End Sub

CurrentRegion

以单元格为原点，返回一个边缘是任意空行和空列组合成的范围，配合Rows.Count(行数)、Columns.Count(列数)、Cells.Count(单元格数)，可以实现索引、定位的作用

需要再深入挖一下这属性的用法

运算符" := "

:=和=在VBA里，都是赋值符，前者是用来给方法内部的参数赋值用，后者是用来给变量赋值的

5.表格数据分类

表格数据如下

image.png

需求：根据C列的数据，创建对应名称的独立工作表（排重）

知识点：Do While语句

Sub ShtAdd()
    Dim i As Integer, sht As Worksheet
    i = 2
    Set sht = Worksheets("花名册")
    Do While sht.Cells(i, "C").Value <> ""
        On Error Resume Next
        If Worksheets(sht.Cells(i, "C").Value) Is Nothing Then
            Worksheets.Add after:=Worksheets(Worksheets.Count)
            ActiveSheet.Name = sht.Cells(i, "C").Value
        End If
        i = i + 1
    Loop
End Sub

需求：将对应性别的数据，存放到对应的工作表里