excel之VBA总结

目录

• 过程体调用过程体：call 名称
  • msgbox inputbox
  • 显示一个过程体返回的时间
  • for循环
    • 格式1：
    • 格式2：
  • do while 循环
    • range(“a” & i) 表示单元格位置
  • IF判断
    • 格式：
• 记住进行删除或者插入操作时：最好让循环数量从大到小 记住步进一定要写-1
  • • 删除整行
  • with多调用
  • 算数运算符
  • 通配符
  • 操作工作表
    • select(选中一张表有三种格式：
    • 删除表时，忽略警告：
    • 记住合并的单元格只能赋值给相同合并的单元格
    • 获取一张表的行数
  • 操作工作䈬
    • 打开工作䈬
    • 添加工作䈬
    • 屏幕更新
  • 获取单元格的几种形式
    • 直接获取
    • 偏移获取
    • 获取整行
    • 复制单元格
    • 清空单元格
  • 自动过滤
  • 事件监听
    • 改变设置
    • 遇到错误时跳过循环
  • VBA中使用函数公式
  • 工作表函数
  • VBA中基类常用的函数
  • 自定义函数
    • 存储对象的变量要用 set 定义
  • 【Dir函数】
  • 【find函数】
  • VBA数组
  • 改变外部文件的名称
  • 类模块

过程体调用过程体：call 名称

msgbox inputbox

显示一个过程体返回的时间

dim t as integer
t = Timer
Timer - t

for循环

格式1：

Sub ABC()

'定义变量为什么类型

Dim i As Integer

For i = 1 to 10 step 5

'循环体
    exit for '结束当前循环
    goto 100 '跳出当前位置到指定位置

Next
100
End Sub

格式2：

Dim i as Worksheets
For each i in Sheets
	i.select
    
Next

do while 循环

Do
    '过程
loop
---------
Do while '条件 满足
    '过程
loop
---------
Do
    '过程
    if '条件
        Exit do
    End if
loop

相对位置，添加按钮控件(右键按钮控件设置固定大小位置)

range(“a” & i) 表示单元格位置

IF判断

格式：

If Range("a" & i) = "" Then
    Exit For
ElseIf Range("a" & i) = "" Then
    
else 
    
End If

记住进行删除或者插入操作时：最好让循环数量从大到小 记住步进一定要写-1

删除整行

Sheet1.Range("D" & i).Select
selection.EntireRow.Delete

with多调用

With VBA
    .Strings()			'文本函数
    .Math()				'数学函数
    .Datetime()			'日期时间
    .FileSystem()		'文件信息
    .Financial()		'财务函数
    .Information()		'信息函数
    .Interaction()		'交互函数
End With

算数运算符

<>   '不等于
like '模糊查询

通配符

* '表示多个匹配数值'
? '表示匹配一个字符'
# '代表任意数字'
! '逻辑与或非'
[A-Z] '大写字母'
[A-Za-z]'字母
[0-9]'数字
[!A-Z] '非大写字母
[!0-9] '非数字'

操作工作表

select(选中一张表有三种格式：

1. ​ Sheet1.Select (直接用表的大名来调用)
2. ​ Sheets(“表名”).Select （用表的名称来查找）
3. ​ Sheets(1).Select (表示当前的所有表的下标)

)

删除表时，忽略警告：

Excel.Application.DisplayAlerts = False

Excel.Application.DisplayAlerts = True

记住合并的单元格只能赋值给相同合并的单元格

获取一张表的行数

Sheet1.Range("a65536").End(xlUp).Row

操作工作䈬

打开工作䈬

Workbooks.Open Filename:="文件名"
'当前活动的工作䈬
ActiveWorkbook.Sheets(1).Range("a1") = "又到此一游"
'保存关闭工作䈬
ActiveWorkbook.Save
ActiveWorkbook.Close

添加工作䈬

Workbooks.Add
ActiveWorkbook.SaveAs Filename:="D:\宝藏\MY_view\office\excel\11.xlsx"
ActiveWorkbook.Close

屏幕更新

Excel.Application.ScreenUpdating = false
Excel.Application.ScreenUpdating = true

获取单元格的几种形式

直接获取

[a10]

cells(10,1)

range("a10").value

偏移获取

range("a1").offset(10,0) 第一个参数表示从当前单元格向下，第二个参数表示从当前单元格向左右数

Range("a10").End (xlUp)

获取整行

range("a10").EntireRow

range("a10").resize(1,10)

复制单元格

range("a10").copy 复制的地方

清空单元格

range(“a10”).clearContents

自动过滤

range(“a1”).AutoFilter Field:= 4, Criterial:=Range(“a1”) 表示过滤的列和值

range(“a1”).AutoFilter 启动或关闭自动过滤

事件监听

改变设置

当值改变时，启动监听

这在change监听中非常危险，所以最好在change监听中添加临时不允许事件触发

application.EnableEvents = False
application.EnableEvents = True

遇到错误时跳过循环

On Error Resume Next

---

---

VBA中使用函数公式

'判断是否是数值：isnumeric()
'转换为数值：val()
'VBA.Strings.InStr("查询的字符串","查询的字符")

工作表函数

with Application.worksheetfunction
.Split("切割的字符串","切割的字符")(0)  //取值不用[]
.CountA()  计数
.Vlookup()  在一个区域内查询一个值
end with 

VBA中基类常用的函数

With VBA
    .Strings()			'文本函数
    .Math()				'数学函数
    .Datetime()			'日期时间
    .FileSystem()		'文件信息
    .Financial()		'财务函数
    .Information()		'信息函数
    .Interaction()		'交互函数
End With

自定义函数

"自定义函数可以用来柔和VBA和excel原生函数实现更强大的功能"
Function 函数名(参数,参数,参数...)

End Function

存储对象的变量要用 set 定义

【Dir函数】

Sub test()
    dim str As string
    dim wb As Workbook
    Dim i as Integer
    
    str = Dir("d:\data\*.*")
    for i = 1 to 100
    set wb = workbooks.Open("d:\data\" & str)
        
        '对外部的表操作
        
        wb.Close
        str = Dir
        if str = "" Then 
            Exit For
        end if
    Next
End Sub
    

【find函数】

Sub test()
    Dim mg As Range
    Set mg Range("d:d").Find(Range("i3"))
        if Not mg is Nothing Then
            Range("m3") = mg.Offset(0,3)
        End IF
End sub
    

VBA数组

'定义一维数组
'带初始值定义数组
dim arr('数量)
'不带初始值定义数组，但是后面要重定义
dim arr()
redim arr(1 to 7)
'定义二维数组
arr = range("a1:b4")
'数组在excel表中定义的是以横向存储
ubound(arr) : '返回数组上界
lbound(arr) : '返回数组下界

改变外部文件的名称

1. name    文件路径  as  文件路径

类模块

可以包含函数，过程体作为方法，属性

定义属性

property get 方法名()
    方法名 = 赋值
End Property