Excel VBA,没你想的那么难

第一章 概述

介绍了VBA的作用：减少无脑地重复性Excel操作

宏的录制：开发工具->录制宏（可设置快捷键）->使用相对引用->操作->停止录制

可以在 插入->按钮 里面设置按钮快捷键

宏的源代码就是由VBA编写，可以在 宏->编辑 里面查看

VB= Visual Basic For Applications

第二章 认识编辑工具

VBA编译器（Visual Basic Editor）打开方式： Excel窗口中输入Alt+F11开发工具->Visual Basic开发工具->查看代码右键工作表->查看代码 编译器窗口

立即窗口可用于调试

VBA代码：Sub语序以"Sub 宏名"开头，以"End Sub"结束，如下图。

第三章VBA遵循规则

Excel中数据只有文本、数值、日期值、逻辑值、错误值5种类型声明变量变量赋值

[Let] 变量名称 = 要储存的数据#Let可省略

对象赋值

用于储存工作簿、工作表、单元格等对象(Object)时用

Set 变量名称 = 要储存的对象名称Dim sht As Worksheet #定义一个工作表对象shtSet sht = ActiveSheet #将活动工作表赋给变量sht

实例一：变量输入

Sub vary()Dim temptemp = 3000Range("A1").Value = tempEnd Sub

实例二：对象输入

Sub content()Dim sht As WorksheetSet sht = ActiveSheetsht.Range("A1") = "lkr"

声明变量方法 同时声明多个变量

Dim sht As Worksheet, IntCount As integer

使用类型声明符

Dim Str$#即将Str声明为string

声明变量可不指定类型

Dim Str#将默认定义为Variant类型

若在代码前面加上"Option Explicit"，则强制所有代码声明变量

6. 不同作用域的变量

7. 多个单变量组成的数组，将每个单变量称为数组的元素

Dim arr(0 To 100) As Byte

通过索引可以查找数组数据

8. 声明多维数组

Dim arr(1 To 3, 1 To 5) As ByteDim arr(3, 5) As Byte#默认起始索引号为0，可通过开头输入"OPTION BASE 0"设置

声明动态数组 使用Public或Dim语句声明数组时，不能使用变量来确定数组的尺寸

Sub Test()Dim a As Integera = Application.WorksheetFunction.CountA(Range("A:A"))#VBA中使用工作表函数，需要借用Application对象的WorksheetFunction属性来调用#错误如下：Dim arr(1 To a) As String #错误！！！#正确如下：Dim arr() As String #定义动态数组ReDim arr(1 To a) #ReDim可以重新定义数组范围，但不可以重新定义数组类型End Sub

使用Array创建数组

arr = array(1, 2, 3, 4, 5, 6)

使用Split创建数组

arr = Split("一,二,三,四,五", ",")#按照","拆分成数组

通过单元格创建数组

Dim arr As Variantarr = Range("A1,C3").ValueRange("E1,G3").Value = arr

数组常用函数 UBound求数组最大索引号

UBound(数组名称)若为UBound(数组名称,1)，则表示第一维度的最大索引号

LBound求数组最小索引号

LBound(数组名称)

求数组包含元素个数UBound(数组名称) - LBound(数组名称) + 1Join函数合并一维数组成字符串

txt = Join(arr,"@") #以@为分割符

将数组保存的数据写入单元格

Range("A1").Value = arr(2)Range("A1,A9").Value = Application.WorksheetFunction.Transpose(arr)#将一维数组写入单元格时，单元格区域需为同一行，若要按列输入，可通过工作表中的transpose函数将数组转置为一列

声明常量时要同时给常量赋值

Const 常量名称 As 数据类型 = 值

注意：常量也有不同的作用域，可用Public定义为公共常量

16. 引用对象

Application.Workbooks("Book1").Worksheets("Sheet2").Range("A2")#若Book1是活动工作簿，可写成Worksheets("Sheet2").Range("A2")#若Sheet2是活动工作表，可写成Range("A2")

对象-属性-方法 对象和属性是相对的方法就是在对象上执行的某个动作或操作属性返回对象包含的内容或具有的特点，方法是对对象的一种操作按ctrl+J可以调出方法/属性列表 VBA的四类运算符：算术运算符、比较运算符、文本运算符和逻辑运算符算术运算符

20.比较运算符

通配符文本运算符

文本运算符有+和&两种，他们都可以使得运算符左右两边的字符串合并为一个字符串

22.逻辑运算符

运算优先级

IF语序

If ... Then ... Else...End If#例如if range("A1").value >=60 then range("A2").Value = "及格" else range("A2").Value = "不及格"

多重if语序

#方法一：If....Then...ElseIf...Then...Else...End IfEnd If方法二：If...Then...ElseIf...Then...ElseIf...Then...Else...End If

Select Case语句

#同样应用于多选问题Select Case 表达式Case Is 条件表达式1...Case Is 条件表达式2...Case Is 条件表达式3...Case Else...End Select

循环语序 For…Next语序

For <循环变量> = <初值> To <终值> [Step步长值]<循环体>[Exit For]<循环体>Next [循环变量]

For Each…Next语序

#For Each...Next用于遍历集合或数组中的每个元素For Each 变量 In 集合名称或数组名称语句块1[Exit For]语句块2Next [元素变量]

Do While语序

#开头判别式Do [While 循环条件]<循环体>[Exit Do][循环体]Loop#结尾判别式Do<循环体>[Exit Do][循环体]Loop [While 循环条件]

Do While中的Exit Do语句应该有条件判别，例子如下

If ... Then Exit Do

Do Until语序

#开头判别式Do [Until 循环条件]<循环体>[Exit For]<循环体>Loop#结尾判别式Do<循环体>[Exit For]<循环体>Loop [Until 循环条件]

GoTo语句

#在目标语句前加上一个带冒号的文本字符串或不带冒号的数字标签x: mysum = mysum + iIf i <=100 Then GoTo x

With语句，简写代码

Sub语序

Public公共过程域的Public可以省略，默认为公共过程；宏对话框中只显示公共过程，私有过程没有显示，私有过程只有当前模块中可以调动在过程中调用过程

#方法1过程名,参数1,参数2,...#方法2Call 过程名 (参数1,参数2,...)#方法3Application.Run "过程名",参数1，参数2,...

向过程传递参数

Sub ShtAdd(shtcount As Integer)#若在括号中加上ByVal，则引用过程中不会再改变参数的值，即shtcount=8不会被调用Worksheets.Add Count = shtcountshtcount = 8End SubCall ShtAdd(2)

函数

设置单元格颜色

Range("A1").Interior.Color = RGB(255,255,0)

计算单元格颜色函数(可作为函数写法参考)

易失性函数

#在代码中加入Application.Volatile True#每次数据更改，函数都会重新计算

代码书写 用空格+下划线可以实现分行书写，即 _用冒号可以实现多行书写到同一行，即 :单引号为注释，即 ’

第四章 操作对象，解决工作中的实际问题

VBA中常用的对象程序运算步骤数据更新关闭

Application.ScreenUpdating = False

不再显示警告对话框

执行某写删除操作时会出行警告弹框

Application.DisplayAlerts = FalseApplication.DisplayAlerts = True

调用Excel中的函数

Application.WorksheetFunction.XXXXXX#注意：并不是所有工作表函数都能通过Worksheet调用

Application对象的常用属性

Workbook代表一个工作簿，workbooks代表当前打开的所有工作薄，即工作薄集合获取工作薄信息

ThisWorkbook.Name #获得工作簿名称ThisWorkbook.Path #获得工作簿文件所在路径ThisWorkbook.FullName #获得带路径的工作薄名称

创建空白工作簿

#直接创建Workbooks.Add#指定模板Workbooks.Add Template:= "D:\模板.xlsm"#指定工作簿类型Workbooks.Add Template:=xlWBATExcel4MacroSheet

9. 用Open打开工作簿

Workbooks.Open "D:\我的文件\模板.xlsm"

用Activate激活工作簿

Workbooks("工作簿1").Activate

保存文件 保存在原文件中

ThisWorkbook.Save

另存为

ThisWorkbook.SaveAs Filename:="D:\test.Xlsm"

另存为且不关闭原文件

ThisWorkbook,SaveCopyAs Filename:="D:\test.Xlsm"

关闭工作簿 关闭所有工作簿

Workbooks.Close

关闭单个工作簿

Workbooks("Book1").Close

关闭并保存工作簿

Workbooks("Book1").Close True

ThisWorkbook与ActiveWorkbook

ThisWorkbook是对代码所在工作薄的引用，ActiveWorkbook是对活动工作簿的引用

引用工作表

#同理Worksheet代表一张工作表，Worksheets代表多张工作表的集合Worksheets("sheet1")

用Add新建工作表 活动工作表前插入一张工作表

Worksheets.Add

用before或after参数指定插入工作表位置

Worksheets.Add before/after:= Worksheets("sheet1")

Count指定插入数量

Worksheets.Add Count:=3

修改工作表名称

Worksheets(2).Name = "工作表"ActiveSheet.Name = "工作表"

删除工作表

Worksheets("Sheet1").Delete

激活工作表

Worksheets(1).ActivateWorksheets(1).select

将工作表复制/移动到指定位置

Worksheets(3).Copy before :=Worksheet(1)Worksheets(3).Copy after :=Worksheet(3)#复制工作簿中的第一张工作表到新工作簿中Worksheet(1).Copy#移动Worksheets(3).Move before :=Worksheet(1)Worksheets(3).Move after :=Worksheet(3)#移动工作簿中的第一张工作表到新工作簿中Worksheet(1).Move

设置Visible属性

Worksheets(1).Visible = False #隐藏Wokrsheets(1).Visible = True #显示

Worksheets和Sheets的区别

Range的引用

#引用多个不连续的单元格区域Range("A1:A10,A4:E6,C3:D9").Select #用逗号分隔#引用多个区域的公共区域Range("B1:B10 A4:D6") #用空格分隔#引用两个区域围成的矩形区域Range("B6:B10","D2:D8").Select #双引号标注和逗号分隔

Cell引用单元格

#工作表对象.Cells(行号,列标)ActiveSheet.Cells(3,4).Value = 20

直接引用单元格（不能在括号中使用变量）

[B2][A1:D10][A1:A10,C1:C10,E1:E10] #三个单元格区域的并集[B1:B10 A5:D5] #两个单元格区域的交集[n] #被定义为n的单元格区域

整行引用

Rows("3:10").Rows("1:1").Select#选中第3行到第10行中的第1行#同理整列采用Columns

使用Union选择多个区域单元格

ThisWorkbook.Sheets(2).Application.Union(Range("A1:A10"), Range("D1:D5")).Select

offset参数

Range("B2,C3").Offset(5,3).Value=500#表示向下移动5个单元格后向右移动3个单元格

Resize

Range("B2").Resize(5,4).Select#重新扩展为5行4列

UsedRange

ActiveSheet.UsedRange.Select#选中已经使用的所有单元格

CurrentRegion

Range("B5").CurrentRegion.Select#返回指定单元格内的一个连续的矩形区域，遇到空行会阻断

Range的End属性

相当于返回该方向的最后一个非空单元格

Range("C5").End(xlUp)

Count属性，获得区域包含的单元格个数

ActiveSheet.UsedRange.Rows.CountActiveSheet.UsedRange.Columns.Count

Address可以获得单元格地址用Activate与Select都可以选中激活单元格Copy复制单元格【Cut同理】

源单元格区域.Copy Destination:=目标单元格#Destination:=可以省略#注意：无论源单元格是区域有多大，目标单元格都可以只指定最左上单元格

Delete删除单元格

Range("B5").Delete Shift:=xlToLeft#删除单元格后右侧单元格左移动，同理可以用xlUp等Range("B5").EntireColumns.Delete#删除整列，同理可用EntireRow

第五章 执行程序的自动开关—对象

当某个事间发生后（如打开工作簿）自动运行的过程，我们称其为“事件过程”，事件过程也是Sub过程。

与普通的Sub过程不同，事件过程的作用域、过程名称及参数都不需要设置，也不能胡乱修改，其命名规则如下：

常用事件：Open、Activate、Change

SheetChange事件需要参数(ByVal Sh As Object, ByVal Target As Range),sh代表的是被修改的单元格所在的工作表，Target代表单元格，注意：SheetChange会令每一张工作表都应用

利用Application.EnableEvents = False来禁用事件，防止Change事件不停循环

SelectionChange可以返回选择中的单元格位置

常用的WorkSheet事件，如下：

常用的Workbook事件（Workbook事件会应用到所有Worksheet中）

Application.onkey可以设置当键盘按下指定键/组合键时自动执行指定程序，但录制宏的方法更为便捷，故不提倡。

Application.onkey "+e","Hello"#当按下shift+e时，执行Hello过程

Application.OnTime可以在指定的时间，执行指定的过程

Application.OnTime TimeValue("12:00:00"),"TellMe"#中午12点时，执行TellMe过程

第六章 设计自定义的操作界面

1.表单按钮和ActiveX控件

表单控件的用法比较单一，只能在工作表中通过设置控件的格式或指定宏来使用，而ActiveX控件拥有很多属性和事件，不但可以在工作表中使用，还可以在用户窗体中使用

InputBox函数与InputBox方法的异同

MsgBox类型

MsgBox返回值如下

几个巧用的函数：窗体

#窗体加载方法Sub ShowForm()Load InputFormInputForm.ShowEnd Sub#无模式窗体允许进行窗体外的其他操作InpuForm.Show vbModeless#默认为模式窗体

Initialze事件可以初始化窗体，如下为窗体的复合框选项中可选项目的设置

6. 本章主要讲如何设计一个窗体，该功能可以简易制作一个UI，然后编辑各个UI的代码，简化使用，同时对不同使用者友善，详细可翻看本书6.7章节

第七章 调试与优化编写的代码

常见错误：编译错误、运行时错误、逻辑错误、vba的三种模式：设计模式、运行模式、中断模式按F9设置断点，可以让程序运行到断点时暂停，再按F8，逐行运行用Stop也可设置断点可用Debug.Print将值输出到立即窗口检查

Debug.Print "i= " & i

本地窗口可以看到所有变量的值以及数据类型监视窗口可以添加监视对象，实时查看数据On Error 三种形式，通常On Error要放在程序开始处，要在错误发生前

On Error GoTo a #如果发生错误，则转到标签a的语句继续执行On Error Resume Next #忽略错误的代码，继续运行程序On Error GoTo 0 #关闭错误捕捉

如何让程序合理化(占用内存更少，运行更快) 声明变量为合适的数据类型尽量避免使用Variant类型的变量不要让变量一直保持在内存中，记得释放

Dim rng As RangeSet rng = Nothing

将一维数组写入一列单元格时，应该将一维数组从行转置为列：Transpose()