VBA 常用类

一：类图

选中一个标识符 ：Fn + F1 可以查看具体的API文档TODO。

二：示例API

要在 Excel 里创建一些自定义应用程序，需要一些常用对象或者对象集合的工作知识，例如 Range，Workbook (Workbooks)，Worksheet (Worksheets)，Window (Windows) 和 Application。

当你在一个现行 VBA 过程，对对象、属性或方法有疑义时按 F1 打开在线帮助，如果你需要快速列出每个可用对象的属性和方法时，或者查找一个很难找到的过程时，使用对象浏览器。如果你想要测试VBA并且立即查看VBA命令的结果时，激活立即窗口。

Workbooks(“工作簿名”)
ActiveWorkbook 活动工作簿
ThisWorkBook 代码所在工作簿
Sheets(n)：第n个工作表，n按照从1的顺序
Sheets(“工作簿名”)
ActiveSheet 活动工作簿，被选中的Sheet
Range(“单元格地址”)
ActiveCell：活动单元格，鼠标选中的单元格

' InStr函数：指定一个字符串在另一个字符串中最先出现的位置
index = instr("helloworoldhello", "hello")
' 从指定位置之后开始查找
index = instr(5, "helloworoldhello", "hello")
' 从右往左开始查找
index = InStrRev("helloworoldhello", "hello")
x = Mid("xxxx-abc", InStrRev("-") + 1)

' like: ? 任意一个字符，* 零个或多个字符，# 任意一个数组0-9, [charlist] charlist中的任意一个字符（单一字符），![charlist] 不在charlist中的任意一个字符
"excle" Like "?"
"vBa" Like [a-zA-Z]

[b:b].Replace "excel", "Excel"

' 拆分, 分隔符不传默认为空格
arr = Split("a-b-c", "-")

' 数组拼接
arr = Array(1, 2, 3)
t = Join(arr, "-")

' 数组筛选Filter
arr = Array("a", "b", "c")
brr = Filter(arr, "a")

'获取当前年月日
Date
Now
Timer

正则表达式：需要工具-引用-  Miscrosoft VBScript Regular Express
正则表达式可以用于查找和替换, 表达式中如果要包括双引号，外边要使用两个双引号书写，Pattern="""(.+?)"""
Dim reg As RegExp
Set reg2 = Createject("VBSCRIPT.REGEXP")
With reg
	.Global= True
	.Pattern="\d+"
	.IgnoreCase = True
	.MultiLine = True
	Set result = .Execute(item)
	
	For Each ele In result
		Debug.Print ele.submatches(0)
		fist = ele.FirstIndex
		len = ele.Lenght
		MsgBox ele(0)
	Next ele
End With


With reg2
	.Global= True
	.Pattern="[a-zA-Z]+\d+"
	For Each item In [a2:a6]
		If	.test(item) Then
			Set result = .Execute(item)
			For Each ele In result
				item.OffSet(0,2) = .Replace(item, "替换字符")
				MsgBox ele
			Next ele
		End If
	Next item
End With


' 快速赋值给固定单元格
[A1] = Workbooks("xxx.xlsm").Name
[A2] = ActiveWorkbook.Name
[A3] = Sheets(1).Name
[A4] = Sheet1.Name

MsgBox Range("A1")
' 也能给单元格赋值
Range(A1) = "xxx"
Range("A1:B3") = "xxx"
' 选中某个范围
Range("A1:B3").Select
' 获取单元格值
MsgBox Cells(2, 1)
MsgBox Cells(2, "A")
Cells(2, 1) = "第二行第一列"

Range("A1:B3").ReSize(1, 5) = arr
' 指定其它Sheet
Sheet2.Range("A1：B3") = arr
Sheet2.[A1：B3] = arr

Sheets(1).Name = "xiugai Sheet 名称"
' 获取文件位置
ThisWorkBook.Path

' 获取互动单元格的地址
ActiveCell.Address
ActiveCell.Address(0, 0)
' 新增工作簿
Set wb = Workbooks.Add
' 当工作簿发生变化时可以通过ThisWorkbook来引用当前的工作簿，而不是活动的工作簿
wb.SaveAs ThisWorkbook.Path & "\" & ThisWorkbook.Sheets(1).Cells(y, "h")

wb.Close
' 打开
Workbooks.Open("xxx.xlxs")
' 关闭文件
ActiveWorkbook.Close
' 新增并命名
Worksheets.Add.Name = "Sheet2"
' 放在最后面，放在某个Sheet后面
Worksheets.Add(after:=Sheets(Sheets.Count)).Name = "Sheet2"
' 复制工作表的内容
ThisWorkbook.Sheets(1).Copy ActiveWorkbook.Sheets(1)

' 选中单元格
Range("A1").Activate
Range("A1").Copy [b1]

' 只粘贴值不粘贴格式，必须写两行
Range("A1").Copy 
Range("B1").PasteSpecial xlPasteValues

' 写成一行必须冒号
Range("A1").Copy: Range("B1").PasteSpecial xlPasteValues

' 清空单元格内容包括格式
Range("A1").Clear
' 清楚内容不清楚格式
Range("A1").ClearContents
' 移动
Range("A1").Cut Range("B1")

' 获取表格的上下左右边界对应的单元格
基准单元格.End(xlUp) 上
基准单元格.End(xlDown) 下
基准单元格.End(xlToLeft) 左
基准单元格.End(xlToRight) 右


' 获取表格的边界：最大行号和最大列号
' 这种方式可能存在问题，当遇到空格就会停止，后面和下面的不再累计
Range("A1").End(xlToRight).Column
Range("A1").End(xlDown).Row


' 选中所有行
Rows.Select
Rows(1).Select
Rows(1:10).Select

Range("A1:C20").Rows(1:10).Select

' 获取最大行号
Rows.Count
' 获取最大列号
Columns.Count

' 获取行号：思路从最右往左找，从最下往上找
' 但是如果最右边的和最下面一行的数据是空，获取行号和列号也不是准确的
Cells(Rows.Count, "f").End(xlUp).Row
Cells(1, Columns.Count).End(xlToLeft).Column

' 可用区域: 获取行和列(支持个别单元格为空)
' 注意：Sheet中如果在表格区域之外不能有值，有值会被算做表格中的一部分会被计算进去
Worksheets(1).UsedRange.Rows.Count
Worksheets(1).UsedRange.Columns.Count

' 如果表格外部还有内容，只要中间有空行和空列分割开就能够识别，不算做表格的一部分
Range("a1").CurrentRegion.Rows.Count
Range("a1").CurrentRegion.Columns.Count



' 单元格便宜上、左为负数, 从0开始
Range("a1").Offset(8, 4).Select 
' 从1开始便宜
Range("a1")(8, 4).Select 


' Resize 以左上角为基准，重新调整指定区域大小返回Range
' 作用是定义到位置了，然后再以此为基准，获取相关Range，比如获取一整行Range
Range("a1").Resize(4,5).Select
[F1].Resize(4,5) = arr

' 工作表函数：对B列中所有为女的求C列的平均值
' 好像之前学的函数都位于WorksheetFunction这个对象下面
Application.WorksheetFunction.Average(arr)
[F1] = Application.WorksheetFunction.AverageIf([b:b], "女", [c:c])
[F2] = WorksheetFunction.CountIfs([b:b], "女", [c:c], ">=90")
[F3] = WorksheetFunction.CountA([b:b])
[F3] = Application.WorksheetFunction.Sum([b:b])
Application.WorksheetFunction.RandBetween(最小值，最大值)
Application.WorksheetFunction.Round
' 根据索引返回数组中的数据，可以传0
WorksheetFunction.Index(arr, 行, 列)
Application.WorksheetFunction.Match("值", [a:a], 0)
WorksheetFunction.Find("关键字", ss， 1)
' 二维数组转一维数组
Application.Transpose(arr)

Application.Trim(" xxab ")
Len("abc")



' 生成随机数
Int((最大值 - 最小值 + 1) * RND + 最小值)

' 排序
' 接收一个范围变量需要用Set关键字
' := 作用忽略传参顺序，根据传参名字来匹配
' 1:升序xlAscending，2是降续xlDescending
Set range1 = Range("a1").CurrentRegion
If range1 Is Nothing Then Exit Sub

range1.Sort Range("j1"), Order1:=xlAscending, Range("j2"), , 2, Header:= xlYes
'What
Range("A1:A10").Find("xxx")
' After 从A5往后查找
Range("A1:A10").Find("xxx", Range("A5"))

' LookIn : xlformulas查询公式，xlvalues查询值，xlcomments查询批注
Range("A1:A10").Find("xxx", Range("A5"), xlcomments)

'SearchOrder: 1：xlWhole精确查找，2：xlPart模糊查找
Range("A1:A10").Find("xxx", , xlvalues, xlPart)
' 查找方向 1：xlnext向下查找，2：xlprevious向上查找
Range("A1:A10").Find("xxx", , xlvalues, xlPart, , xlnext)
' MatchCase区分大小写，默认为False
Range("A1:A10").Find("xxx", , xlvalues, xlPart, , xlnext，True)

' 查找下一个
Set ss = Range("a:a").Find("*", xlComments)
Set ss1 = Range("a:a").FindNext(ss)
Set ss2 = Range("a:a").FindNext(ss1)
Set ss3 = Range("a:a").FindNext(ss2)

' 筛选: 筛选列
' File: 筛选哪一列，从1开始
’ Criteria1 条件1
' Operator 条件类型 xlAnd/xlOr
' Criteria2 条件2
' VisibleDropDown : 是否徐碍事筛选字段的下拉箭头，默认True
' xlTop10Items: 筛选前几项、xlBottom10Items 筛选后几项、xlTop10Percent 前百分比、xlBottom10Percent 后百分比
' 
Range("e1").AutoFilter(4, "> 300", xlAnd, "< 400")
' 根据值筛选
Range("e1").AutoFilter 2， Array("值1", "值2")， xlFilterValues
' 根据颜色筛选
Range("e1").AutoFilter 2， GRB(255,0,0)， xlFilterCellColor
' 根据字体颜色
Range("e1").AutoFilter 2， GRB(255,0,0)， xlFilterFontColor
' 根据图标
Range("e1").AutoFilter Filed:=4, Criteria1:=ActiveWorkbook.IconSets(x13Arrows).Item(1), Operator:=xlFilterIcon
' xlFilterDynamic 动态筛选
' 将筛选后的数据恢复成原样
Sheet1.AutoFilterMode=False

' 删除重复项
Range("h:h").RemoveDuplicates Columns:=1, Header:=xlNo

' 并集：对集合中的所有元素进行操作
Set s1 = Union([A1], [A5:A7], [A9])
s1.Interior.ColorIndex = 3
Union([A1], [A5:A7], [A9]).Select
Union([A1], [A5:A7], [A9]).Copy [f1]

' 交集
Intersect(Range("d4:e17"), [b8:g12]).Interior.ColorIndex = 3

' 指定: xlCellTypeBlanks 空单元格，枚举值很多
Range("A1:C10").SpecialCells(xlCellTypeBlanks).Select

' 空值添加注释
Dim r1 As Range
Set r1 = Range("a:a").SpecialCells(xlCellTypeComments)
For Each item In r1
	item.AddComment "缺考"
	item.Comment.Shape.TextFrame.AutoSize = True
	item.Comment.Visible = True
Next

' 填充
Range("A3") = "=A1*A2"
Range("A3").AutoFill Range("A3:A10")

' 不带格式只填充值
Range("A3").AutoFill Range("A3:A10")， 4
' 只填充格式
Range("A3").AutoFill Range("A3:A10")，3
' 填充连续的数值
Range("A3").AutoFill Range("A3:A10"), 2


' 替换
Range("C1:F5").Replace what:="excel", replacement:="Excel"
' 区分大小写
Range("C1:F5").Replace what:="excel", replacement:="Excel", MatchCase:=True

'With 用于简化代码，相当于链式调用
' Range("a1").Font.Bold = True
' Range("a1").Font.Italic = True
' Range("a1").Font.Size = 12

With Range("a1").Font
	.Bold = True
	.Italic = True
	.Size = 12
End With

Dim ss$
' 获取第一个值，相当于初始化  Dir("/xxx/yy/") 目录最后要加一个/
ss = Dir("/xxx/yy/*.doc")
Do
	ss = Dir  ' 遍历下一个
Loop Until ss = ""

' 默认列举文件，可以指定列举文件夹
ss = Dir("/xxx/yy/*.doc", vbDirectory)

'新建文件件
MkDir "/a/b/c"  

' Name语句：重命名或者移动磁盘文件、目录、文件夹
Name "/xxx/yy/1.xlsx" As "/xxx/yy/2.xlsx"
Name "/xxx/yy/1.xlsx" As "/zzz/1.xlsx"
Name "/xxx/yy/1.xlsx" As "/zzz/2.xlsx"

' 复制Sheet
Set wb = Workbooks.Open(ThisWorkbook.Path & "/" & "a.xlsx")
wb.Sheets(1).Cells.Copy ThisWorkbook.Sheets(1).[a1]
wb.Close

' 清空单元格
Cells.Clear

' 超链接
ActiveSheet.Hyperlinks.Add  Range("d2"),"D\xx\yy\abc.xlsx", "Sheet2!a1", "打开文件", "显示文本"

' 分列
[B:B].TextToColumns Destination:=Range("C1"), Other:=True, OtherChar:= "\"

Sheets("Sheet2").Activate

Sub 获取单元格的RGB()
	y = Range("G12").Interior.Color
	r = y Mod 256
	g = y \ 256 Mod 256
	b = y \ 256 ^ 2 Mod 256
	获取单元格的RGB = "RGB(" & r & "," & g "," & b & ")" 
End Sub

Sub

三：Range

Range是VBA中使用最频繁的一个类。

Application.Workbooks("Chap02.xls").Worksheets("Sheet1") .Range("A4").ClearContents

Worksheets("Sheet1").Range(Cells(1,1), Cells(10, 5).Select

Sheets(″Sheet1″).Select
Cells(1,1).Value
Range("A5:A10").Select
Range(Cells(6, 1), Cells(10, 1)).Select
Selection.Resize(2, 4).Select
Selection.Offset(2, 2).Resize(2, 4).Select

Range("A4").Formula = "Price"

Selection.Offset(1, 3).Select

Worksheet(1).Range(″A1:A1000″).Rows.Count
Workbooks(1).Sheets(1).Range(″A1″).Value = 100

AverageValue=Application.WorksheetFunction.Average(Worksheets(1).Range( ″
A1:A1000″))

Set MySheet = Workbooks(1).Sheets(1)
MySheet.Range(″A1″).Value = 100

With Workbooks(1).Sheets(1).Range(″A1:A1000″).Font
     .Name = ″Pay″
     .FontStyle = ″Bold″
End With

减少对象的激活和选择

如果你的通过录制宏来学习VBA的，则你的VBA程序里一定充满了对象的激活和选择，例如Workbooks(XXX).Activate、Sheets(XXX).Select、Range(XXX).Select 等,但事实上大多数情况下这些操作不是必需的。例如：

Sheets(″Sheet3″).Select
Range(″A1″).Value = 100
Range(″A2″).Value = 200

可改为：

With Sheets(″Sheet3″)
  .Range(″A1″).Value = 100
  .Range(″A2″).Value = 200
End With

Range 属性返回一个单元格或者单元格区域。引用必须是 A1 在引号里的样式（例如：”A1”）引用可以包括区域运算符冒号（例如：”A1:B2”）或者联合运算符逗号（例如：”A”,”B12”）

Range属性

用 Cells 属性

当你要选择一个确定的单元格时，Cells 属性要求两个自变量，第一个是行号，第二个是列号或者列字母。自变量输入在括号中。如果忽略自变量，Excel 将会选择当前工作表的所有单元格。