VBA手把手教程

一、基础

使用VBA处理数据之前，首先配置好环境，这篇教程使用的是EXCEL2021版本
excel主选项卡有【开发工具】的可以跳过以下操作：
打开excel，点击“文件”，然后在更多中选中“选项”

在弹窗中寻找【自定义功能区】，在如图所在位置勾选【开发工具】

excel的主选项卡就会出现【开发工具】

1. 录制宏

打开【开发工具】，点击【录制宏】。根据右侧格式要求，录制一个宏：

录制宏教程

注意：一定要保存为.xlsm的后缀，才可以执行录制好的宏

在新的sheet页选择之前录制好的宏，点击【执行】，格式会自动设置完成

2. 绝对引用和相对引用

绝对引用和相对引用区别将体现在初始位置和过程位置的记录方式上。
相同点：针对初始位置，两者的初始位置是相同的。Excel 只记录一个「选中的区域」的参数，不会记录选中的单元格地址信息。
不同点：两者对过程位置的记录方式不同。
绝对引用：录制宏过程中，记录选中的单元格的绝对地址信息。例如，从初始位置 A1，再选中 A4，绝对引用只记录 A4。
相对引用：录制宏过程中，记录初始位置单元格和最新选中单元格离初始位置的偏移量。例如，从初始位置 A1，再选中 A4，相对引用记录 A1 和偏移量 [3, 0]。
绝对引用：

VBA绝对引用和相对引用区别之绝对引用

相对引用：

VBA相对引用和绝对引用之相对引用

3. vba初识

事实上，我们录制宏实质上就是excel把人的每一个动作记录下来，翻译成VBA代码保存为一个宏供查看和多次执行

找到宏之后，点击【编辑】按钮找到VBA代码

上图，红色部分代表工作簿中的sheet页，模块中的vba可以用在任意sheet页中；绿色部分就是vba代码，'sub指的是 宏的开始；绝对引用就是宏名，End Sub指的是宏结束，中间部分则是具体实现的代码*（后续再讲解）*

Sub 绝对引用() 'sub: 宏的开始；绝对引用就是宏名
'
' 绝对引用 宏
'

'
    Range("A1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorLight1
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A3").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .ThemeColor = xlThemeColorDark2
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("A3").Select
End Sub    '宏结束

4. 变量和常量

常量与变量的区别：
1）常量必须要有初始值，而且值不允许被修改
2）变量可以无初始值，且可以多次赋值。
注意：有变量时，最好强制声明Option Explicit，避免报错

变量的使用规则：
1）尽量使用有意义的名称
2）英文字母、中文（不推荐）、数字、下划线；但是不能以数字开头
3）不能与系统保留名重名（如：sub end for）
4）对大小写不敏感

附一段求圆面积的代码：

Sub 计算圆的面积()
'先选中半径对应的单元格
'行：2  ；列：从2到29
Dim x As Integer  '定义的变量
Const pi = 3.14159  '定义的常量
For x = 2 To 29
    Cells(x, 2) = Cells(x, 1) * Cells(x, 1) * pi
    Next
End Sub

点击【Visual Basic】写代码


运行结果如下：

5. 多列动态合并

循环结构：
for 变量 = 开始值 to 结束值 step 步长
循环体
next

//基础知识
Sub 预热()
    '选中某个单元格
    Range("c2").Select
    '将某个范围的数据清除掉
    Range("e2:e4").Clear
    '弹出e列最后一行有值的行数
    MsgBox Range("e2").End(xlDown).Row  '或
    MsgBox Range("e10000").End(xlUp).Row

//多列动态合并
Sub 多列动态合并()
    '有三列或者更多列，因此设置x代表第几列
    '设置y为j列的行号
    '设置j列合并
    Const j = 5
    Dim x, y As Integer
    y = 2
    For x = 1 To 3
        Cells(2, x).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.Copy
        Cells(y, j).Select
        ActiveSheet.Paste
        y = Cells(y, j).End(xlDown).Row + 1
        Next
End Sub

实现效果如图：将A、B、C列合并到e列（目前j=5，也可以等于其他想填充数据的合并列）

6. IF判断

完成以下小练习，学会vba代码的if判断

• 单条件判断
  if 条件 then
  执行的语句
  end if

Sub 删除离职所在整行()
    '删除整行
    'Range("c2").EntireRow.Delete
    '设置变量，x:遍历数据的行数
    Dim x As Integer
    For x = 2 To 62
        If Range("D" & x) = "离职" Then
        Range("D" & x).EntireRow.Delete
        End If
    Next
End Sub

• 双条件判断
  if 条件 then
  执行语句1
  else
  执行语句1
  end if

Sub 补贴()
    Dim x As Integer
    For x = 2 To 61
        If Range("c" & x) = "男" Then
        Range("f" & x) = 100
        Else
        Range("c" & x) = "女"
        Range("f" & x) = 120
        End If
    Next
End Sub

• 多条件判断
  if 条件 then
  执行语句1
  elseif 条件 then
  执行语句2…
  else
  执行语句3
  end if

Sub 等级()
    Dim x As Integer
    For x = 2 To 61
        If Range("e" & x) > 4000 Then
            Range("g" & x) = "优秀"
        ElseIf Range("e" & x) > 3800 Then
            Range("g" & x) = "良好"
        ElseIf Range("e" & x) > 3600 Then
            Range("g" & x) = "及格"
        Else
        Range("g" & x) = "不达标"
        End If
    Next
    
End Sub

7. 工作表操作

题目：将超市销售记录表模板复制12份，分别对应12个月的销售情况

Sub 批量复制()
    Dim x As Integer
    For x = 1 To 12
        Sheets("超市销售记录表模板").Copy after:=ActiveSheet
        ActiveSheet.Name = x & "月"
    Next
        
End Sub

二、VBA办公自动化

1. 获取工作表数量

Sub 工作表数量()
    'MsgBox Sheets.Count '弹窗
    Range("a1") = Sheets.Count
End Sub

2. 选择工作表的三种方式

Sub 选择工作表()
    'Sheet3.Select '根据默认名称选择
    'Sheets("测试").Select  '根据工作表名称选中
    Sheets(3).Select  '根据工作表的位置索引选中
End Sub

3. 获取所有工作表名称

Sub 获取所有工作表名称()
    Range("a1") = "工作表名称"
    Dim x As Integer
    For x = 1 To Sheets.Count
        Range("a" & x + 1) = Sheets(x).Name
    Next
End Sub

4. 获取所有工作表名称

Sub 获取所有工作表名称()
    Range("a1") = "工作表名称"
    Dim x As Integer
    For x = 1 To Sheets.Count
        Range("a" & x + 1) = Sheets(x).Name
    Next
End Sub

5. 更改工作表名称

Sub 更改工作表名称()
    Sheet1.Name = "今天"
End Sub

6. 隐藏工作表

Sub 隐藏工作表()
    Dim x As Integer
    For x = 1 To Sheets.Count
        If Sheets(x).Name <> "今天" Then
            Sheets(x).Visible = False
        End If
    Next
End Sub

7. 显示所有隐藏的工作表

Sub 显示所有隐藏的工作表()
'    Dim x As Integer
'    For x = 1 To Sheets.Count
'        If Sheets(x).Name <> "今天" Then
'            Sheets(x).Visible = True
'        End If
'    Next
    
    Dim x As Integer
    For x = 1 To Sheets.Count
        If Sheets(x).Visible = False Then
            Sheets(x).Visible = True
        End If
    Next
End Sub

8. 批量生成空表

Sub 批量生成空表()
    Dim x As Integer
    For x = 1 To 3
        Sheets.Add after:=ActiveSheet
        ActiveSheet.Name = "猴子" & x
    Next
End Sub

9. 批量删除空表

Sub 批量删除空表()
    'Application.DisplayAlerts = False ' 关闭弹窗提示
    ' 选中并删除表
    'Sheets("猴子").Select
    'ActiveWindow.SelectedSheets.Delete
    Dim x As Integer
    For x = 1 To 3
        Sheets("猴子" & x).Select
    ActiveWindow.SelectedSheets.Delete
    Next
    'Application.DisplayAlerts = True  '代码执行完再打开
End Sub

删除表时，跟正常手动删除工作表一样，会弹窗提示，如下：

若不希望执行过程中弹出此弹窗，可在代码开头设置参数：Application.DisplayAlerts = False（关闭弹窗提示）
代码执行完之后再将此参数恢复正常：Application.DisplayAlerts = True

10. 批量删除空表

Sub 批量删除空表()
'    Application.DisplayAlerts = False ' 关闭弹窗提示
    ' 选中并删除表
    'Sheets("猴子").Select
    'ActiveWindow.SelectedSheets.Delete
'    Dim x As Integer
'    For x = 1 To 3
'        Sheets("猴子" & x).Select
'    ActiveWindow.SelectedSheets.Delete
'    Next
'    Application.DisplayAlerts = True  '代码执行完再打开

' 工作表不需要选中，然后删除
    Dim sht As Worksheet '定义sht为工作表对象
    '循环工作簿中的每张工作表
    For Each sht In Sheets
        If sht.Name <> "今天" And sht.Name <> "测试" Then
            sht.Delete
        End If
    Next
End Sub

11. 复制工作表

Sub 复制工作表()
   Dim x As Integer
   For x = 1 To 12
    Sheet3.Copy after:=ActiveSheet
    ActiveSheet.Name = x & "月"
   Next
End Sub

12. 批量标注

Sub 批量标注()
    Dim x As Integer
    For x = 1 To 12
        Sheets(x & "月").Select
        Range("a1") = "操作人：yifeng"
    Next
End Sub

13. 打开与关闭工作簿

Sub 打开与关闭工作簿()
    Workbooks.Open "E:\资料\EXCEL资料\VBA\批量操作.xlsx"
    Sheets.Add
    Sheet4.Name = "第4张表"
    ActiveWorkbook.Close savechanges:=True '关闭工作簿时不弹窗
End Sub

14. 新建与另存工作簿

Sub 新建与另存工作簿()
    Dim zx As Workbook
    Set zx = Workbooks.Add
    '新建工作簿赋给zx，因为工作簿是一个对象，给对象赋值
    '对象名称前要写set，用来区别于跟变量进行赋值
    Dim lj As String
    lj = ThisWorkbook.Path '用lj将本工作簿路径装起来
    zx.SaveAs lj & "\yifeng.xlsx"
    zx.Close
End Sub

15. 批量另存为工作簿

Sub 批量另存为工作簿()
Application.ScreenUpdating = False '闪屏关闭
Dim x As Integer
For x = 1 To 12
    Sheets(x & "月").Copy
    ActiveWorkbook.SaveAs "路径"
    ActiveWorkbook.Close
Application.ScreenUpdating = False '闪屏打开
End Sub

16. 批量修改工作簿名称

Sub 批量修改工作簿名称()
    'name 原工作簿名称 as 新文件名称
    Dim x As Integer
    For x = 1 To 12
        Name "原路径+名称" & Sheet3.Range("a" & x + 1) & ".xlsx" As "新..."
        
    Next
End Sub

17. 聚光灯

在代码编写展示的工作表列表这里，选中一个工作表，双击这个工作表

弹出如下弹窗：在这里写代码即可

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Cells.Interior.Pattern = xlNone '光标移开背景也要清除
    Target.EntireColumn.Interior.Color = vbCyan
    Target.EntireRow.Interior.Color = vbCyan
End Sub

18. 输入密码打开对应表格

Option Explicit

Private Sub workbook_open()
    Dim pw As String
    pw = InputBox("请输入密码")
    Dim sht As Worksheet
    For Each sht In Sheets
        If sht.Name <> "目录" Then
            sht.Visible = xlSheetVeryHidden
        End If
    Next
    If pw = "猴" Then
        Sheet4.Visible = xlSheetVisible
    ElseIf pw = "人" Then
        Sheet3.Visible = xlSheetVisible
    ElseIf pw = "熊" Then
        Sheet2.Visible = xlSheetVisible
    End If
End Sub