ExcelVBA技巧

第一篇 Excel VBA基础

第一章 Excel VBA 基础知识

技巧1、录制宏并获取有效的代码

使用宏录制器是获取Excel VBA代码最简单的方式，尤其是对于没有任何编程经验的VBA学习者。例如希望在VBA中使用的Excel的“高级筛选”功能从图1-1所示的Database工作表的数据表中获得在图1-2所示的Result工作表内指定的日期范围内的数据，并将数据复制到以单元格B5开始的单元格区域。

 

其中“宏名”文本框显示为默认的名称“Macro1”，建议修改为有意义的名称，在“快捷键”文本框中输入的字母将作为运行宏的组合键即<Ctrl+Shift+字母>。在“保存在”下拉列表扣列出了保存录制宏的3个位置：当前工作簿，新工作簿和个人宏工作簿，选择“当前工作簿”将宏保存在活动工作簿中，选择“新工作簿”将宏保存在一个新的空工作簿，选择“个人宏工作簿”将宏保存在名为Personal.xls的特定文件中，启动Excel时会自动地装载该文件。在“说明”文本框中添加宏的注释，即显示在代码顶部的文字，用来提供与该宏相关的信息。

通常在录制宏之前，应规划好操作步骤，尽可能少地录制不必要的操作。

Step2 单击“确定”按钮开始录制宏。此时会出现如图1-4所示的工具栏，包含两个按钮：“停止

录制”按钮和“相对引用”按钮。单击“停止录制”按钮将停止录制操作，单击“相对引用”按钮将以相对于当前单元格的方式录制操作。默认的情况下，Excel采用绝对引用的方式录制宏。

注意：此时工作簿状态栏中会出现“录音”的提示，菜单也由“录制新宏”变为“停止录制”。

Step3 在Excel中进行操作。单击菜单“数据”→“筛选”→“高级筛选”，在打开的“高级筛选”对话框中选中“将筛选结果复制到其他位置”单选按钮，如图1-5所示。

Step4 在“高级筛选”对话框中单击“列表区域”文本框，然后用鼠标选定Database工作表中A1:F10单元格区域，即原始数据列表。

Step5 在“高级筛选”对话框中单击“条件区域”文本框，然后用鼠标选定Result工作表中的B2:C3单元格区域，即指定的日期范围。

Step6 在“高级筛选”对话框中单击“复制到”文本框，然后用鼠标选定Result工作表中的B5单元格，即目标数据区域的起始单元格。完成选择后的“高级筛选”对话框如图1-6所示。

Step7 单击“确定”按钮，获取的数据将复制到单元格B5开始的区域中，结果如图1-7所示。

宏录制将记录步骤3至步骤7所进行的操作，并将其转换成相应的VBA代码。

Step8 关闭宏录制，单击菜单“工具”→“宏”→“Visual Basic编程器”或者<Alt+F11>组合键在VBE中查看录制的代码，如图1-8所示。

Step9 整理或修改代码。录制操作完成，接下来应该在VBE中整理或修改宏录制器所录制的宏代码，使宏更简洁、更灵活且更有效率。

通常宏录制器会“认真地”记录用户的每一步操作，应用与操作相关的所有属性和方法的所有参数，而实际进行的操作仅仅为其中的一部分，因此可以删除代码中与操作无关的方法参数和属性。比如在大多数情况下，有关选定对象和激活对象的代码并无实际的用途，而且会降低宏代码的整体效率，所以可以删除。

此外，通过录制而得到的宏代码不够灵活，仅能用于执行某项特定的操作任务；不能做出判断，不能执行循环，不能提示用户僌相关信息，不能使用为题赋值；只能生成Sub过程。因此为了创建功能更强大的宏程序，有必要对录制宏的代码进行改进。

在修改录制的代码时，应及时地调试代码，以免误删所需要的代码。

本例中录制的代码很简洁，没有多余的代码，因此无须整理。在进一步的应用中，需要获取AdvancedFilter方法所在的代码并适当地修改该代码，当用户在Database工作表中增加数据时能查询最新的数据，使代码更灵活。修改后的代码如下：

Sub GetDatas()

    Dim lLastRow As Long

    lLastRow = Sheets(“Database”).UsedRange.Rows.Count

    Range(“B5:G65536”).Clear

    Sheets(“Database”).Range(“A1:F” & lLastRow).AdvancedFilter _

          Action:=xlFilterCopy, _

          CriteriaRange:=Range(“B2:C3”), _

          CopyToRange:=Range(“B5”), _

          Unique:=False

End Sub

代码解析：

第3行中的代码获取Database工作表中已使用区域的行数并赋值给变量lLastRow。

第4行中的代码清除了当前工作表中上次查询所获得的数据，以免在本次查询的结果中留下不必要的数据。

第5行中的代码修改了宏录制器生成的代码，使用变量lLastRow扩展数据列表，从而可以获取最新的数据。

修改代码后可以在Database工作表中添加数据，重新运行GetData过程。如果新添加的数据满足条件，就会自动地添加到结果数据表中。

注意：运行此段代码时，Result工作表应该为活动工作表。

可以修改Result工作表单元格区域B3:C3中的日期范围，然后再次运行GetData过程，从而获取满足该日期范围的数据。

技巧2 执行宏（调用过程）的5种方式

在Excel中执行宏，实际上就是调用ExcelVBA过程。Excel提供了多种调用过程的方式，以满足不同的情形和适应不同的需求。下面介绍几种常用的调用过程的方法。

2-1 利用“窗体”控件或对象调用过程

Step1 从“窗体”工具栏中拖放“按钮”控件到工作表的适当位置，将出现“宏”对话框；或者在已经存在于工作表中的“按钮”上单击右键，从弹出的快捷菜单中选择“指定宏”，如图2-1所示。显示“窗体”工具栏的方法是单击菜单“视图”→“工具栏”→“窗体”。

Step2 在“宏”对话框中选择相应的“宏名”，然后单击“确定”按钮，如图2-2所示。

Step3 单击工作表中的任意一个单元格，取消对该按钮的选择。现在只要单击该按钮就能够执行宏（调用指定的过程）。Excel工作表中的图片、形状也可以调用过程，调用的方法与上面的步骤相同。

2-2 利用“控件工具箱”控件调用过程

步骤1 选择“控件工具箱”工具栏中的“命令按钮”进入设计模式。显示“控件工具箱”工具栏的方法是单击菜单“视图”→“工具栏”→“控件工具箱”。

步骤2 拖放“命令按钮”控件到工作表的适当位置，双击该命令按钮或者在该命令按钮上单击右键，从弹出的快捷菜单中选择“查看代码”如图2-3所示。

步骤3 在该命令按钮的单击事件过程中，输入调用过程的代码如下：

Private Sub commandbutton1_click()

    Call getdatas

End Sub

其中Call语句调用GetDatas过程。也可以不加Call语句，而直接输入要调用的过程名称GetDatas。

步骤4 回到Excel界面，单击“控件工具箱”工具栏左上角的按钮退出设计模式，如图2-4所示。

此时单击该命令按钮即可调用指定的过程。

2-3 在菜单或工具栏中调用过程

在Excel菜单中能够添加菜单项，然后使用该菜单项调用过程。

步骤1 单击菜单“工具”→“自定义”，在弹出的“自定义”对话框中选择“命令”选项卡。

步骤2 在左侧的“类别”栏中找到“宏”，然后单击右侧的“命令”栏中的“自定义菜单项”。

步骤3 拖动“自定义菜单项”到菜单栏，例如将其拖至菜单“文件”中的“文件搜索”的下方，创建一个自定义菜单项，如图2-5所示。

步骤4 在刚创建的自定义菜单项中单击右键。

步骤5 在弹出的右键菜单中可以重命名该菜单项，为其命名一个有意义的名称，例如输入“数据搜索”。

步骤6 在右键菜单中单击“指定宏”。

步骤7 在打开的“指定宏”对话框中选择相应的宏名，然后单击“确定”按钮。

步骤8 在“自定义”对话框中单击“关闭”按钮。

此时在菜单“文件”下就新增了一个名为“数据搜索”的菜单项，单击该菜单项即可调用指定的“自定义按钮”拖至工具栏相应的位置并指定宏。

2-4 在一个过程中调用另一个过程

在使用VBA编写程序时，一个好的习惯是尽量将实现单独功能的代码放置在独立的过程中，并在主过程中调用这些独立的过程。这样不仅便于程序的调试，而且程序的结构清晰，便于理解和维护，取也能够重复使用通用的过程代码。

下面用一个示例演示在程序中调用过程的方法。如图2-6所示的工作表，其中“加班费基数”固定，每个加班为80元，“加班数”由人工输入，“加班系数”根据加班个数而不同，“加班费”为“加班费基数”、“加班数”和“加班系数”的乘积。要求自动地根据“加班数”确定“加班系数”并得到“加班费”数值，然后将最大加班费数值所在的单元格的底纹设置为绿色。

示例代码如下：

Dim lLastRow As Long

Sub myMain()

   Dim rng As Range

   lLastRow = Range("A" & Cells.Row.Count).End(xlUp).Row

   Range("D3:E" & lLastRow).Clear

   '调用获取加班系数的过程

   Call GetExtraNum

   '调用计算加班费的过程

   Call CalculateExtra

   Set rng = Range("E3:E" & lLastRow)

   '调用设置加班费不够格式的过程

   Call SetFormat(rng)

End Sub

'获取加班系数并输入工作表

Sub GetExtraNum()

    Dim i As Long, ExtraNum As Single

    For i = 3 To lLastRow

        ExtraNum = Range("C" & i).Value

        Range("D" & i).Value = GetExtra(ExtraNum)

    Next i

End Sub

'计算加班费并输入工作表

Sub CalculateExtra()

   Range("E3:E" & lLastRow).FormulaR1C1 = "=RC2*RC3*RC4"

End Sub

'设置加班费列的格式并突出显示最大值

Sub SetFormat(rngF As Range)

    With rngF

        .NumberFormat = "0.00"

        .HorizontalAlignment = xlCenter

        .FormatConditions.Delete

        .FormatConditions.Add Type:=xlExpression, Formulal:="=RC5=max(c5)"

        .FormatConditions(1).Interior.ColorIndex = 4

    End With

End Sub

'获取加班系数

Function GetExtra(num As Single) As Single

    Select Case num

        Case 1, 2

            GetExtra = 1

        Case 3 To 5

            GetExtra = 1.05

        Case Is > 5

            GetExtra = 1.1

        Case Else

            GetExtra = 0

    End Select

End Function

代码解析：

本示例包括5个过程，其中myMain过程为主调过程，用来调用另外4个过程。

GetExtraNum过程用来获致“加班系数”并输入到工作表相应的单元格中。该过程中调用GetExtra函数过程根据“加班数”来获取相应的“加班系数”，其中使用了Select Case结构语句。

CalculateExtra过程用来计算“加班费”并输入工作表，代码中使用R1C1样式的公式计算单元格数值的乘积。

SetFormat过程用来设置“加班费”列的格式，其中第31行至第33行中的代码使用条件格式来判断单元格区域中的最大值，并对该值所在的单元格设置格式。

第7行、第9行和第12行中的代码调用Sub过程，第19行中的代码调用Function过程。除了本书后面介绍的属性过程外，ExcelVBA有两种类型的过程：Sub过程和Function过程。

在Sub过程中能够调用Function过程和其他的Sub过程。如果从另一个过程中调用Sub过程，那么该Sub过程可以接受输入参数。

注意：不能运行一个带有直接输入参数的Sub过程。

Function过程也可以接受输入参数，能够调用另一个Function过程，甚至Sub过程。与Sub过程不同的是：Function过程能够返回值。

注意：自定义函数内置的工作表函数的效率更低，特别是在工作表中大量地使用自定义函数时。此外，自定义函数不能操作工作表及其单元格，不能改变单元格的格式设置，例如复制或移动单元格、设置字体的颜色等。

在myMain过程中使用Call语句调用过程，也可以省略Call语句，直接使用过程的名称来调用该过程。在使用Call语句调用过程时，如果该过程有参数，则必须在被调过程的参数的周围加上括号，如第12行中的代码。如果省略Call语句，对于带参数的Sub过程，参数的周围则不应该放置括号；对于Function过程，如果要使用该函数过程的返回值，则应该在参数的周围放置括号，如果不需要使用带参数的Function过程的返回值，在参数的周围则不需要放置括号。

在定义参数时，建议同时声明参数的类型。对于Function过程，建议同时声明该函数过程的返回值类型，例如第37行中的代码。此外在指定参数的值时，应使用“：=”指定参数的名称以便于理解。

运行myMain过程后结果如图2-7所示。

2-5 利用Excel事件调用过程

如果在打开工作簿时，希望直接运行技巧1中的GetDatas过程，则可在工作簿的Open事件过程中输入下面的代码：

Private SubWorkbook_Open（）

    GetDatas

End Sub

当然还可以使用Application对象的Run方法执行过程。关于Run方法的具体使用请参阅技巧19.

 

 

技巧3 快速输入代码的方法

在编写VBA程序时，充分地利用VBE的相关设置和工具并了解一些技巧。将有助于快速地输入代码，尤其在代码较多时。

3-1 进行必要的辅助设置

在VBE的“选项”对话框中有一些设置能够辅助VBA代码的输入，或者避免干扰VBA的输入，在VBE中单击菜单“工具”-“选项”，弹出如图3-1所示的“选项”对话框。

建议取消勾选“自动语法检测”复选框。如果勾选该复选框，那么每当在代码模块中新输入行存在编译错误时都会弹出一个错误消息框，如图3-2所示。

 

此时用户需要单击“确定”按钮取消消息框，才能继续输入代码。如果在“选项”对话框中取消该复选框，则不会弹出消息框，但存在错误的代码行仍然会标记颜色，提醒该用户存在错误。

建议勾选“自动列出成员”、“自动显示快速信息”以及“自动显示数据提示”等复选框，这样在输入VBA代码时可以提供辅助输入提示或者必要的参考信息，从而有助于代码的输入。

如果勾选“自动列出成员”复选框，那么在输入代码时就会显示出对象的成员列表，包括该对象的属性，方法和事件，选择需要的成员后按空格键即可完成自动输入。如果选中“自动显示快速信息”复选框，那么输入函数、属性和方法时就会显示出相关的参数变量信息。如果选中“自动显示数据提示”复选框，在代码处于高度状态时，鼠标指针放置在变量上方就会显示相应的值。

3-2 使用“编辑”工具栏

 

在VBE的“编辑”工具栏上（如图3-3所示），使用一些按钮有助于快速地输入正确的VBA代码。

如果在输入对象和钟点并且出现了与该对象相关的属性和方法列表后按<ESC>键关闭了该列表，或者关闭了函数，属性和方法的参数提示信息，则能够使用该工具栏上相应的“属性/方法列表”按钮和“参数信息”按钮再次显示信息。

同样，在使用常数的语句中按下“常数列表”按钮，则会出现相关的常数列表。选择了VBA语句、函数、方法、过程名称或者常数后单击“快速信息”按钮，则会显示所选项目的语法和常数值。当输入某个关键字的前几个字母后按下<Ctrl+Space>组合键或者单击“自动完成关键字”按钮，则会自动地输入该关键字。

在VBE的“编辑”工具栏上（如图3-3所示），使用一些按钮有助于快速地输入正确的VBA代码。

如果在输入对象和钟点并且出现了与该对象相关的属性和方法列表后按<ESC>键关闭了该列表，或者关闭了函数，属性和方法的参数提示信息，则能够使用该工具栏上相应的“属性/方法列表”按钮和“参数信息”按钮再次显示信息。

同样，在使用常数的语句中按下“常数列表”按钮，则会出现相关的常数列表。选择了VBA语句、函数、方法、过程名称或者常数后单击“快速信息”按钮，则会显示所选项目的语法和常数值。当输入某个关键字的前几个字母后按下<Ctrl+Space>组合键或者单击“自动完成关键字”按钮，则会自动地输入该关键字。

 

注意：在中文操作系统中，由于<Ctrl+Space>组合键经常作为切换中英文输入法的快捷键，因此此时无法使用此快捷键自动地输入关键字。

3-3 导入现有的代码

有许多通用的VBA代码，或者其他工程中的代码稍作修改就能运用于某工程中，此时 可以将这些代码导入到该工程中重复使用，而不必从头输入代码。

当然从其他的模块中复制所需的代码块，然后将这些代码粘贴到正在编写的模块中，也有助于快速地编写代码。

3-4 使用宏录制器生成代码

对于与Excel内置功能有着相同操作的代码，可以先使用宏录制器将Excel中的操作转换为相应的代码，然后在录制的代码中复制需要的部分到所需的代码模块中。特别是需要输入大量格式设置的代码时，宏录制器能够帮助快速地生成代码。当然在应用录制的代码之前应该进行必要的修改。

技巧4 编写高效的VBA代码

4-1 善于使用注释

在编写代码的同时使用注释，简要地说明编写的每个过程的目的，关键语句实现的操作，对过程所做的任何修改，描述变量等，可以使代码更容易阅读，理解和维护。

也可以利用注释标记分隔过程，以区分不同的过程，使程序结构一目了然。

此外使用VBE中“编辑”工具栏的“设置注释块/解释注释块”，也可以将VBA语句暂时设置成注释块，以便于调试代码。

4-2 合适的命名并使用变量

在代码中尽量还要使用“硬编码”，而使用更为灵活的变量。为变量起一个合适的名称并遵守一定的命名规范，将有助于理解变量的作用。

建议在VBE的“选项”对话框中勾选“要求变量声明”复选框，以强制声明所有的变量。这样做有以下的3个好处。

1）容易发现并修改变量名拼写错误，否则VBA会认为其错误的拼写为一个新的Variant类型变量，并赋予其值为0或空（字符）。

2）可以提高程序的执行速度，不必每一次在编译时都对变量进行检查。

3）能为对象变量自动地列出可用的属性和方法列表，以防止对属性和方法拼写错误而出现无效的方法和属性。

在声明变量时，应该明确变量的作用域，避免变量混淆。在使用变量时，要遵守变量的语法规则，例如使用Set关键字为对象变量赋值，使用New关键字实例化对象变量。在对象变量使用完成后诮及时地释放变量。此外对于在循环中使用的计数变量，应确保该变量遵循一定的规律变化，而不会意外地改变该变量的值。

4-3 不要随便省略

在VBA中大部分对象都有一个默认的属性，例如Range对象的默认属性Value属性。在书写代码时可以省略.Value，其效果与写上.Value是相同的。但是建议在编写代码时，即使是默认的属性还是要写全，以便于阅读和理解程序，从而养成良好的程序设计习惯。

在调用其他程序时最好写上Call关键字。当然Call关键字可写可不写，但是加上Call关键字能够清楚地表明该程序正在调用另一个过程。

4-4 限制GoTo语句的使用

在VBA中已经有很多循环结构可用来实现循环功能，而使用GoTo语句进行循环则违背了结构化程序设计的原则，使得程序代码更难阅读，也容易出错。建议尽量不要使用GoTo语句，除非该语句能够真正地简化代码，或者非得使用该语句不可。

4-5 合理使用循环

在使用循环结构时，无论是否满足循环结束的条件，只要达到了目的就应退出循环，以减少不必要的循环次数。

此外循环的开始和结束比较特殊，一般要检查最开始或最结尾时循环进行的操作，以免出现不符合要求的结果或错误。

4-6 声明函数类型

在自定义函数时，建议用As关键字声明函数返回值的数据类型，要养成声明函数返回值的数据类型的习惯。

4-7 使用名称

在程序中对六里桥区域引用时，最好对该单元格区域指定名称。定义名称后，无论该单元格区域添加或删除行，都能保证引用的是正确的单元格区域，并且还能使用对象的一些通用的属性。

4-8 一个模块实现一项任务

代码越简单，就越容易维护和修改，并且较小的程序也容易理解和调试。如果过程的代码很长，那么既不便于阅读，也不便于维护。

运用模块化的方法，将过程代码中具有独立功能的过程分离出来，形成几个具有单一功能的过程。然后在一个主过程中调用这些过程，同时将实现不相关功能的代码放置在不同的模块中，而在一个模块中包含所有相关过程的代码，这位代码则更容易维护，也容易阅读理解，还可以重复利用。

4-9 一个窗体实现一项功能

应该让一个窗体只实现一项功能，尽量保持窗体简单一些。并且窗体模块代码应该只包含操作窗体控件的过程，而没有操作窗体及其中任何控件的过程则应该放置在代码模块中。

4-10 为编写代码打好基础

在编写代码之前应该先设置好工作簿或工作表，为编写代码打好基础。例如不是必须需要利用代码实现的操作，应尽量先使用Excel的内置功能操作工作表，然后使用VBA代码实现特定的需求。特别是在处理大量的数据时，应先设计好数据的结构，对数据进行必要的整理，使其尽可能地满足一定的规律，这样往往可以极大地减少代码量，也能使开发变得更容易。

4-11 使用错误处理技术，让代码更健壮

好的应用程序应能自己对可能发生的错误进行处理。通常运行程序时所发生的错误将会导致VBA停止代码的运行，用户将会看到显示错误编号和错误说明的对话框。好的应用程序不会让用户来处理这些错误，而是在应用程序中集成了错误处理代码，然后可以跟踪并采取相应的动作。

技巧5 有效地获得帮助

大多数用户都不太可能准确地记住ExcelVBA所有的对象及其属性、方法和事件，也难以熟悉所有的VBA函数的使用方法，因此能够获得有效的帮助将有助于代码的编写。

在VBE的右上角有一个组合框，在其中输入需要帮助的问题或关键字将会出现相关的信息的列表。

在该列表中单击需要帮助的条目，将出现该条目的帮助信息。

5-2 使用<F1>键获得帮助

在代码窗口中选择需要了解信息的对象、属性、方法、事件或函数等的名称，或者将光标置于该名称内，然后按<F1>键，将出现帮助信息。

5-3 使用<F2>键获得帮助

在VBE中按<F2>键将出现“对象浏览器”窗口，如图5-4所示。

在“对象浏览器”窗口中包括已引用的库中所有的对象及其属性、方法和事件。在“对象浏览器”窗口的左上角选择类型库，然后输入关键字，单击“搜索按钮”，在“搜索结果”列表框中就会列出相应的结果。下方的“类”列表框列出了选择的库中所有的可用的对象类，其右侧列出了该类的所有成员。在“对象浏览器”窗口的底部显示了所选成员的相关代码，如果单击其中的绿色链接文本，就会在“对象浏览器”窗口中快速地跑到所选成员的类或库。

如果选择“对象浏览器”窗口中的任一条目，然后按<F1>键，则会出现与该条目相关的帮助信息。

5-4 使用宏录制器获得帮助

如果不能确定所需操作使用的对象、属性或者方法，那么可以在Excel中打开宏录制器录制相应的操作，然后查看生成的代码，从中找出所需要的对象、属性或方法。

5-5 使用立即窗口获得帮助

在VBE中单击菜单“视图”→“立即窗口”或者按<Ctrl+G>组合键会显示“立即窗口”。在“立即窗口”中可以试验代码并立即得到结果（如图5-5所示），从而帮助正确地编写代码。

5-6 在Excel论坛中搜索或提问

目前大多数的Excel论坛都提供了搜索功能，可以输入关键字来得到问题的相关帮助信息。这里以ExcelHome论坛的“搜索”为例，如图5-6所示。

在输入需要帮助的关键字并选取相应的版面后单击“站内搜索”按钮，将会出现相关的结果，如图5-7所示。此时能够从结果中查找相关的内容，看看需要解决的问题有无现成的答案。

当然也可以在Excel论坛中发帖提出自己的问题，往往也会得到较满意的答案。