VBA笔记10：技巧与案例

阿白mvo3hep7cv 2020-07-21

展开全文

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.CloseEnd 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 IfEnd 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:=TrueEnd 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    LoopEnd Sub