VBA数据类型判断转化、对话框、空、正则表达式语法
这里整理下自己觉得VBA可能会比较有用的一些内容,主要涉及到数据类型的判断、转化,对话框的使用,关于‘空’的阐述以及正则表达式的基本语法。
一些常用的语句
Cells.Hyperlinks.Delete
ActiveSheet.PivotTables("数据透视表1").PivotCache.Refresh
exit do ' 结束do循环
eixt for ' 结束for循环
exit sub ' if condition then ~~~ : end end是结束整个程序。exit sub只是结束这个过程。
for循环:
exit for 表实结束for循环,可以结合if判断结束循环。
for i = 1 to n
......
next
______________________________
for each var in list
......
next
_____________________________
do循环类型1:
do while +条件 while表示符合条件时执行循环
.....
loop
do循环类型2:
do until + 条件 until表示当符合条件时跳出循环
......
loop
do循环类型3:
使用if + exit do 结束循环。
do
if + 条件
exit do
end if
loop
do循环4:
do
......
loop until+ 条件 while和until灵活使用,一个符合条件执行循环,一个符合条件跳出循环。
.Range("a6:at6").Copy
.Range("a7:a7").PasteSpecial Paste:=xlPasteFormats
Shell "Notepad.exe " & f, vbNormalFocus ' f:txt文件完整路径
With ActiveSheet.UsedRange
.Copy
.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False '复制后要清空剪切板,不然再操作复制,会出错。
End With
on error resume next ' 遇到错误忽略继续
on error goto line ' 遇到错误跳转
on error goto 0 ' 遇到错误报错。可以再on error resume next 后调用恢复报错。
' goto是跳转语句,也可以结合判断使用
If VBA.IsError(Range("A1").Value) = True Then ' 判断单元公式返回的是否是错误值
ActiveCell.AddComment ("Hello")
Range("a1:h1").EntireColumn.AutoFit
Cells(2, 3).Formula = "=a2&b2" 输入公式
Range(Cells(2, 3), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 3)).FillDown ' 使用filldown公式向下填充
range("a2:b" & usedrange.rows.count).filldown ' 假设分别在a1和b1单元格输入公式的情况下,下面单元格自动填充
ActiveWindow.DisplayGridlines = False ' 隐藏网格线
Range("C:C,E:E").EntireColumn.Hidden = True ' 隐藏列
' 取消行列隐藏
Cells.EntireColumn.Hidden = False
Cells.EntireRow.Hidden = False
range("a1").Interior.ColorIndex = xlNone ' 取消背景颜色
range("a1").Interior.ColorIndex =3 ' 红色
Range("h:l,o:o,r:s,aa:ab").Interior.Color = vbGreen ' 也可以直接设置color

index = instr("who you are","are")
数据类型的判断、转化
- 类型判断:返回bool值
- typename(i) :判断数据格式
- IsNumeric:判断变量的值是否为数值
- isdate:判断变量的值是否为日期
- isnull:判断变量的值是否包含任何有效数据
- isempty:判断变量的值是否为空
- IsArray : 判断出变量是否为一个数组。
- IsError:判断变量是否返回的是一个错误值
- IsObject:判断变量是否表示对象变量
- 类型转化
很多时候在判断时候,数据类型不一致会带来干扰,这时候可以使用类型转化函数。
- CBool :Boolean
- CDate :Date 任何有效的日期表达式。可以使用isdate判断date是否可以被转化为日期或时间。
- CInt :Integer -32,768 至 32,767,小数部分四舍五入。
- CLng :Long -2,147,483,648 至 2,147,483,647,小数部分四舍五入。
- CSng :Single 负数为 -3.402823E38 至 -1.401298E-45;正数为 1.401298E-45 至 3.402823E38。
- CStr :String 依据 expression 参数返回Cstr。
- CVar :Variant 若为数值,则范围与 Double 相同;若不为数值,则范围与 String 相同。
- Val :将数字转化为值value。
- int: 转化为整数。比如int(“10”)
关于“空”
- variant
A = Null '一个variant变量可以赋值为Null
IsNull(A) '判断一个varint变量是否为Null
一个尚未初始化的variant变量是empty
a = empty
A 等于 Empty, 因为尚未初始化的“不定型变量”都等于 Empty。
'但如果检测 A = “” 或 A = 0, 也都可以得到 True 值。
- string
判断一个string变量为""(空字符串)
a = “”
- integer
定义一个integer变量后,它自动初始化为0
a = 0
- object
定义一个object变量后,它自动初始化为nothing。对象要使用is判断
a is nothing
对话框的使用
这里主要涉及到inputbox输入框、msgbox弹出框、以及选择文件、文件夹对话框
-
Application.inputbox(prompt,title,default,left,top,helpfile,helpcontextid,type)
- prompt:提示信息 - 必选参数
- title:标题
- default:输入框内默认显示内容
- Left:指定对话框相对于屏幕左上角的 X 坐标(以磅为单位)
- Top:指定对话框相对于屏幕左上角的 Y 坐标(以磅为单位)
- HelpFile:此输入框使用的帮助文件名, 如果有 HelpFile 和 HelpContextID 参数,对话框中会显示帮助按钮
- HelpContextID:HelpFile 中帮助主题的上下文 ID 号
- Type:指定返回的数据类型, 如果省略此参数,则对话框返回文本
- type参数: {0:公式,1:数字,2:文本,4:逻辑值,8:range单元格引用,16:错误值,64:数值数组}
-
inputbox和Application.inputbox区别:
点击取消,前者返回是空。后者返回是false,用以区分返回输入是空还是点击了取消;后者可以限定输入的数据类型,如果输入数据类型与要求不符合会报错。
Sub inputbox()
'通过inputbox传入单元格
Dim inputbx
On Error GoTo 100
Set inputbx = Application.inputbox("请选择单元格", "Title", "点击工作表单元格", , , , , 8)
inputbx.Interior.Color = vbGreen
100:
End Sub
-
MsgBox(prompt[,buttons][,title][,helpfile,context])
- 参数
- prompt - 必需的参数。在对话框中显示为消息的字符串。提示的最大长度大约为1024个字符。 如果消息扩展为多行,则可以使用每行之间的回车符(Chr(13))或换行符(Chr(10))来分隔行。
- buttons - 可选参数。一个数字表达式,指定要显示的按钮的类型,要使用的图标样式,默认按钮的标识以及消息框的形式。如果留空,则按钮的默认值为0。
- title - 可选参数。 显示在对话框的标题栏中的字符串表达式。 如果标题留空,应用程序名称将被放置在标题栏中。
- helpfile - 可选参数。一个字符串表达式,标识用于为对话框提供上下文相关帮助的帮助文件。
- Context - 可选参数。一个数字表达式,用于标识由帮助作者分配给相应帮助主题的帮助上下文编号。 如果提供上下文,则还必须提供helpfile。
- Buttons参数
该参数可以是用过数字或者英文标志,同时也可以多个一起使用,中间使用‘+’号连接。
- 0 vbOKOnly - 仅显示“确定” 按钮。
- 1 vbOKCancel - 显示“确定” 和“取消” 按钮。
- 2 vbAbortRetryIgnore - 显示“中止”,“重试”和“忽略” 按钮。
- 3 vbYesNoCancel - 显示“是”,“否”和“取消” 按钮。
- 4 vbYesNo - 显示“是”和“否”按钮。
- 5 vbRetryCancel - 显示“重试”和“取消”按钮。
- 16 vbCritical - 显示严重消息图标。
- 32 vbQuestion - 显示警告查询图标。
- 48 vbExclamation - 显示警告消息图标。
- 64 vbInformation - 显示信息消息图标。
- 0 vbDefaultButton1 - 第一个按钮是默认的。
- 256 vbDefaultButton2 - 第二个按钮是默认的。
- 512 vbDefaultButton3 - 第三个按钮是默认的。
- 768 vbDefaultButton4 - 第四个按钮是默认的。
- 0 vbApplicationModal 应用程序模式 - 当前的应用程序将不会工作,直到用户响应消息框。
- 4096 vbSystemModal 系统模式 - 所有的应用程序将不会工作,直到用户响应消息框。
- MsgBox函数返回值
- vbOK - 确定 按钮被点击。
- vbCancel - 取消 按钮被点击。
- vbAbort - 中止 按钮被点击。
- vbIgnore - 忽略 按钮被点击。
- vbYes - 是 按钮被点击。
- vbNo - 否 按钮被点击。
msb = MsgBox("选择是、否或取消", vbYesNoCancel + vbInformation, "标题")

- 返回文件完整路径:fullname
可以调用改对话框选择文件,再打开对应的文件。
Application.GetOpenFilename(文件类型筛选规则,优先显示第几个类型的文件,标题,是否允许选择多个文件名)
f = Application.GetOpenFilename("Excel文件,*.xls")
f = Application.GetOpenFilename("Excel2003文件,*.xls,Word文件,*.doc")
- 打开多种文件类型,默认显示word文件(最后的参数2表示根据前边的选项,默认打开第几个):
f = Application.GetOpenFilename("Excel2003文件,*.xls,Word文件,*.doc,文本文件,*.txt", 2)
f = Application.GetOpenFilename("Excel2003文件,*.xls,Word文件,*.doc,文本文件,*.txt", 2, "选择要汇总的文件")
Dim f
ChDrive "E"
ChDir Application.Path
f = Application.GetOpenFilename("Excel2003文件,*.xls,Word文件,*.doc,文本文件,*.txt", 1, MultiSelect:=True)
MsgBox f(1) '返回数组第一个
正则表达式
- 常用属性
- Global: 如果值为True,则搜索全部字符;如果值为False,则搜索到第1个即停止
- IgnoreCase:bool类型,是否区分大小写
- Pattern:正则表达式
- Multiline:字符串是不是使用了多行,如果是多行,$(表示以…结尾)适用于每一行的最后一个
-
常用方法
- replace:替换
replace(string,replace_str)
Sub test()
Dim regexp As Object
Dim sr
Set regexp = CreateObject("vbscript.regexp")
sr = "苹果12斤"
With reg
.Global = True
.Pattern = "[\u4e00-\u9fa5]"
Debug.Print .Replace(sr, "") '把文字替换成空,取出数字
End With
End Sub
-
test方法: 正则表达式是否能在字符串匹配到,返回bool值;test(string)
-
Execute:执行返回一个 MatchCollection (集合)对象,该对象包含每个成功匹配的 Match 对象,如果只匹配一个的话,可以用数组m(0)直接返回匹配结果。如果有使用小括号“()”分组匹配,可以使用MatchCollection对象的SubMatches获取分组的内容,再使用数组切片到对应内容。
Sub test()
Dim regexp As Object
Dim sr
Dim collections As Object
Dim submat
Dim m
sr = "5月2号销售量15,销售金额30,5月3号销售量20,销售金额40;"
Set regexp = CreateObject("vbscript.regexp")
With regexp
.Global = True
.Pattern = "销售量(\d+).+?销售金额(\d+)"
If .test(sr) Then '如果能匹配到再输入
Set collections = .Execute(sr)
For Each m In collections
Debug.Print m
For Each submat In m.submatches
Debug.Print submat
Next
Next
End If
Debug.Print collections(0).submatches(1) '我们也可以直接切片,比如第一个匹配对象的第二个分组
End With
End Sub

- MatchCollection对象的属性
- FirstIndex : 匹配内容在整个字符串中的起始位置
- Length : 匹配长度
- Value:匹配的值
- count:匹配对象的数量
- item:匹配对象,如上使用集合或者对象索引切片,比如:collections(0)
- SubMatches对象属性
- count:匹配的分组数量
- value:匹配的值
- item:使用对象的索引切片,比如:collections(0).submatches(1)

关于正则的语法各编程语言基本一致,这里就不再多赘述了,有需要mina就网上搜罗吧~~~
关于程序调试,以及查看对象的属性,查看VBE的本地窗口变量的变化及详情会是个不错的选择,此外,VBA帮助有中文译本,阅读容易,遇到问题可以查找官方文档。以下为官方文档链接:
https://docs.microsoft.com/zh-cn/office/client-developer/excel/excel-home
正则表达式测试工具分享:
链接:https://pan.baidu.com/s/1Smn65HVXcpS6H3Bes9VjvQ
提取码:6j6w
参考:《兰色幻想》VBA教程