Excel VBA笔记

保存 VBA 代码

文件-> 另存为-> 文件类型选择：启用宏的工作簿（.xlsm）

变量类型

准确使用定义变量，合理利用内存，可以提高代码运行效率

常用对象

Workbooks(“工作簿名”)
ActiveWorkbook 活动工作簿
ActiveSheet 活动工作表
ThisWordBook 代码所在工作簿
Sheets(n)第 n 个工作表 // 按工作表的顺序
Sheetn 第 n 个工作表 // 按系统工作表名
Range(“单元格地址”) // 一个单元格，一行，一列，一个区域
Cells(行,列) // 不写行、列，就返回整个表
[A1]单元格简写
Activecell 活动单元格

'复制单元格内容到新的工作簿
Sub CopySheet()
    Dim wb As Workbook
    Set wb = Workbooks.Open("C:\excel\工作簿1.xlsx")
    ThisWorkbook.Sheets("Sheet2").Range("A3:D4").Copy wb.Sheets("Sheet1").Range("a6")
End Sub

End 动态数据区域

· End 获取数据边界
End(xlUp) 上
End(xlDown) 下
End(xlToLeft) 左
End(xlToRight) 右

基准单元格.end(xl).属性或方法

注意：如果遇到范围内有空单元格，就无法正确的定位到真实的边界

· 配合 row 和 column
row 行号
column 列号

1、row 和 rows 的区别

row 返回单元格所在的行号，如果是区域，就返回这个区域的首行的行号
rows 代表行的集合，返回 range 对象

2、获取工作表所有的行数和列数

rows.count 获取最大行号
columns.count 获取最大列号

UsedRange

它是 worksheet 的一个属性

代表指定工作表上的所用区域

编写格式

工作表.UsedRange 方法或者属性

注意：所有被使用的单元格都会被选中，比如如果数据表中存在一些备注，说明等

CurrentRegion

它是单元格的一个属性

代表指定单元格所在的区域

编写格式

单元格.CurrentRegion.方法或者属性

注意：如果所选择的单元格周围没有内容，就只会选中当前单元格

for to …next

step 步长，可以正向，也可以写负值

for each …next

循环对象集合
workbooks
worksheets

Sub demo()
  Dim ss As Range
  For Each ss In Sheet1.Range("b2", Sheet1.Cells(Rows.Count, 2).End(xlUp))
    If ss.Value = "田雨" Then
      Worksheets.Add(after:=Sheets(Sheets.Count)).Name = ss.offset(0,-1)
    End If
  Next ss
End Sub

offset 偏移

以一个单元格为基准，进行偏移，返回的是单元格

编写格式

单元格.offset(偏移行,偏移列) //当前单元格行、列各+1 开始
单元格(偏移行,偏移列) //从当前单元格开始

上负，下正
左负，右正

Sub demo()
    Range("a1").Offset(8, 4).Select
    Range("a1")(8, 4).Select
End Sub

Resize 属性

调整指定区域的大小，返回 range 对象

该对象表示重新定义的区域

编写格式

单元格.resize(新区域行数，新区域列数) // 从当前单元格开始，如果是区域就是区域的左上角第一个单元格开始

Sub demo()
  Dim ss As Range
  For Each ss In Range("b2", Cells(Rows.Count, 2).End(xlUp))
    If ss.Value = "田雨" Then
        ss.Offset(0, -1).Resize(1, 5).Interior.ColorIndex = 3
    End If
  Next ss
End Sub

错误处理语句

On Error Resume Next ’当代码运行错误时忽略，继续向下运行

Err.Number 不等于 0 时，表示出错了

If Err.Number <> 0 Then MsgBox "出错了！" : GoTo 100

100:
Err.clear

工作表函数

WorksheetFunction.AverageIf //平均值
WorksheetFunction.CountIfs //统计满足条件的数量
WorksheetFunction.Index(区域，行，列) //选中指定行列的区域

Sub test()
 [g1] = WorksheetFunction.AverageIf([b:b], "田雨", [c:c])
 [g1] = WorksheetFunction.Index(arr,0,1)
End Sub

随机数函数 Rnd

Rnd 函数返回一个小于 1 但大于或等于 0 的值

整数区间随机数公式

Int((最大值 - 最小值 + 1) * Rnd + 最小值)

Range.Find 查询

• 在区域中查找特定信息
• 找到了返回一个 Range 对象，它代表在其中找到该信息的第一个单元格
• 没找到，返回 Nothing

FindNext 查找下一个

Range(‘区域’).FindNext(什么单元格之后)

筛选

Range.AutoFilter 方法

拆分工作簿

’过滤重复值
Range("b:b").RemoveDuplicates Columns:=1, Header:=xlYes

Union 并集

不仅可以并单元格，也可以并入区域

Union([a1], [a3]).Select

Intersect 交集

Intersect(range1,range2)

必须至少指定两个 Range 对象

SpecialCells 条件定位

相当于在工作表中使用 Ctrl+G 定位条件

Range.SpecialCells(Type,Value) // Type 必填

Type:

枚举值	值	描述
xlCellTypeAllFormatConditions	-4172	任意格式的单元格。
xlCellTypeAllValidation	-4174	含有验证条件的单元格。
xlCellTypeBlanks	4	空单元格。
xlCellTypeComments	-4144	含有注释的单元格。
xlCellTypeConstants	2	含有常量的单元格。
xlCellTypeFormulas	-4123	含有公式的单元格。
xlCellTypeLastCell	11	所用区域中的最后一个单元格。
xlCellTypeSameFormatConditions	-4173	格式相同的单元格。
xlCellTypeSameValidation	-4175	验证条件相同的单元格。
xlCellTypeVisible	12	所有可见单元格。

Sub test()
    Dim rg As Range
    Dim ss As Range
    Set rg = Range("c1:e10").SpecialCells(xlCellTypeBlanks)
    For Each ss In rg
        'ss.ClearComments
        ss.AddComment "缺考"
        ss.Comment.Shape.TextFrame.AutoSize = True
        ss.Comment.Visible = True
    Next ss
End Sub

AutoFill 自动填充

对指定区域中的单元格执行自动填充

Range.AutoFill(Destination,Type)

Destination 必选 要填充的单元格。目标区域必须包括源区域

Replace 替换

Range.Replace
返回 boolean，它表示指定区域内单元格中的字符。使用此方法不会更改选中范围或活动单元格

with 语句

对单个对象或用户定义类型执行一系列语句

With Range("a1:e7")
    .Borders(xlInsideVertical).LineStyle = xlContinuous
    .Borders(xlInsideHorizontal).LineStyle = xlContinusout
End With

Dir 函数

遍历指定文件下的文件名称，返回一个 String

Dir(“C:\excel”)

Sub test()
    Dim path As String
    path = Dir("C:\excel\*.xlsx")
    Do
        Debug.Print path
        path = Dir
    Loop Until path = ""
End Sub

超链接

向指定的区域或形状添加超链接

InStr 函数

指定一个字符串在另一字符串中最先出现的位置

InStrRev 函数，返回一个字符串在另一个字符串中出现的位置，从字符串的末尾算起。

Name 语句

重命名磁盘文件、目录或文件夹、移动文件

语法

Name 旧路径名称 As 新路径名称

Name 语句重命名文件，并在必要时将起移动到其他目录或文件夹

MkDir 语句

新建目录或文件夹

Like 运算符

用来比较两个字符串

语法

result = string Like pattern

result 任何数值变量
string 任何字符串表达式
pattern 任何字符串表达式

Exit 结束语句

给代码提前结束的出口

编写格式

Exit Do // 只能卸载 Do 循环里面
Exit For // 只能卸载 for 循环里面
Exit Sub // 只能卸载 sub 子过程里

数组

• 把数据写入到数组中，批量地管理数据
• VBA 读取对象的值慢于读取内存中的值，借用数组可以让代码运行速度大幅提升
• 数组中的值由于储存在内存中，所以无法保留，当代码执行结束，就会小时
• 数组中的每个元素都具有唯一的地址，更改其中一个，不会影像其他元素

数组的维度

• 数组可以是 1 维、2 维，一直可以到 60 维，但是日常工作中，通常使用 1 维和 2 维
• excel 工作表中，每一行或每一列可以转换成一维数组，多行多列转换成二维数组

example

arr = Range(“a1:e1”) // 生成数组，如果在 arr 前面加上 set 就会生成对象
Range(“a2:e2”) = arr

动态数组

Dim arr()
ReDim arr() 重新定义
ReDim Preserve arr() 重新定义数组的同时，保留数组之前的值，必须在前面提前定义 arr

声明中有变量时只能使用 ReDim
ReDim arr(1 To h)

UBound(arr,1) // 返回数组第一维的上界（最大下标）
UBound(arr,2) // 返回数组第二维的上界（最大下标）
LBound(arr,2) // 返回数组第二维的下界（最小下标）

转置 sheet 的行列互换

字典 Dictionary

提高对数组的掌控能力

字典的关键词具有唯一性

事件

一个动作引发的事件，VBA 中，工作簿、工作表、控件、窗体、图表都支持事件。

事件级别

• 应用程序级 任何工作簿
• 工作簿级 当前工作簿
• 工作表级 当前工作表

Application.EnableEvents = True 启用事件
Application.EnableEvents = False 禁用事件

聚光灯效果加载项代码

Public WithEvents EX As Excel.Application

Private Sub EX_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
    ’ 表格默认样式
    Cells.Interior.Pattern = xlPatternAutomatic
    If Target.Row = 1 Then Exit Sub
    If Target.Count = 1 Then
        Target.EntireColumn.Interior.Pattern = xlChecker
        Target.EntireColumn.Interior.PatternColor = 49407
        Target.EntireRow.Interior.Pattern = xlChecker
        Target.EntireRow.Interior.PatternColor = 49407
    End If
End Sub

Private Sub Workbook_Open()
    Set EX = Excel.Application
End Sub