图文详解:Excel 2016 编写简单VBA程序

打开Visual Basic编辑器

在Office 2016中,编写VBA代码、调试宏以及应用程序开发等都离不开Visual Basic编辑器,使用Visual Basic编辑器可以完成创建VBA过程、创建VBA用户窗体、查看或修改对象属性以及调试VBA程序等任务。下面来介绍Excel 2016启动VBA编辑器的两种方法。

方法一:启动Excel 2016,在“开发工具”选项卡中单击“代码”组中的“Visual Basic”按钮,如图25-19所示,可打开Visual Basic编辑器窗口。

图25-19 单击“Visual Basic”按钮

方法二:在“开发工具”选项卡的“代码”组中单击“宏”按钮打开“宏”对话框,在对话框中选择宏,单击“编辑”按钮,如图25-20所示,可打开Visual Basic编辑器窗口。

图25-20 单击“编辑”按钮打开Visual Basic编辑器

提示:实际上,在Office 2016中打开Visual Basic编辑器的方式很多,按“Alt+F11”键能够快速打开Visual Basic编辑器。在Excel 2016工作表标签上鼠标右击,在获得的关联菜单中选择“查看代码”命令,也能够打开Visual Basic编辑器。

轻松输入代码

Visual Basic编辑器的“代码”窗口的智能感应技术能够为开发者提供代码方面的帮助,如在输入代码时显示“属性/方法”列表、能自动显示参数信息以及自动生成关键字等功能。这些功能使程序设计者不再需要记忆大量的VBA函数和方法,使开发者能快速而高效地完成应用程序的编写。本节将以创建简单的Excel应用程序为例来介绍在“代码”窗口中编写程序的技巧。

步骤1:打开Visual Basic编辑器,选择“视图”|“工具栏”|“编辑”命令打开“编辑”工具栏。在代码编写时,如果需要获得对象、属性或方法提示,可以单击“编辑”工具栏的“属性/方法”按钮,打开一个列表框。在列表框中双击需要添加的内容即可将其直接添加到代码中,如图25-21所示。

图25-21 使用“属性/方法”列表

步骤2:在“代码”窗口输入代码,当输入对象名和句点后,VBA会自动给出一个下拉列表框,如图25-22所示。拖动列表框右侧的滚动条可以查看所有可用的属性和方法,双击需要的项目即可将其插入到程序中。如果在输入句点后继续输入属性或方法的前几个字母,VBA会在列表自动找到匹配的项目,此时按Enter键即可将其插入程序,同时程序的输入将另起一行。如果按空格键则将匹配项目插入程序但不换行。

图25-22 选择属性或方法

步骤3:在“代码”窗口中输入一个关键字的前几个字母,单击工具栏上的“自动生成关键字”按钮,则关键字后面的字母将会自动输入。如果与输入字母相匹配的关键字有多个,则Visual Basic编辑器会给出一个下拉列表,用户可以从中选择需要的关键字,如图25-23所示。

图25-23 生成关键字

提示:在出现“属性/方法”列表后,按Esc键将取消该列表。以后再遇到相同的对象,列表也将不会再出现。此时,如果需要获得“属性/方法”列表,可以按“Ctrl+J”键。也可右击,然后再单击弹出式菜单中的“属性/方法列表”命令。

步骤4:在“代码”窗口中输入VBA常数后,如果输入“=”,Visual Basic编辑器会自动弹出一个“常数列表”列表框,如图25-24所示。双击列表中的选项,即可将其值输入代码中。当在“代码”窗口中输入VBA指令、函数、方法、过程名或常数,单击“编辑”工具栏上的“快速信息”按钮,VBA会显示该项目的语法或常数的值,如图25-25所示。

图25-24 显示“常数列表”

提示:当显示“常数列表”后,可以使用键盘的方向键选择列表中的选项,按空格会将选择内容输入程序。如果按Esc键,将关闭该列表。单击工具栏中的“常数列表”按钮或按“Ctrl+Shift+J”键同样能够打开该列表。

图25-25 显示快速信息

步骤5:在“代码”窗口中输入VBA函数后,如果函数需要参数,在输入函数名和函数的左括号后,在光标下就会出现参数信息提示。这个提示将显示函数需要的参数,随着参数的输入,提示框会将当前需要输入的函数加粗显示,如图25-26所示。

图25-26 显示参数信息

使用对象浏览器了解VBA对象

使用对象浏览器,用户可以浏览工程中所有可获得的对象并查看它们的属性、方法以及事件,此外还可查看工程中可从对象库获得的过程以及常数。对象浏览器可以显示用户所浏览的对象的联机帮助,也可用搜索和使用用户所创建的对象,其他应用程序的对象也可用其来浏览。

步骤1:启动Excel,按“Alt+F11”键打开VBA编辑器。选择“视图”|“对象浏览器”命令打开“对象浏览器”窗口,在“对象浏览器”窗口的“工程/库”下拉列表中选择需要查询的对象库类型,如图25-27所示。

步骤2:在“类”窗口中选择需要查询的对象,此时在右侧将显示该对象的对象成员。选择一个对象成员,在“对象浏览器”窗口下方将显示该成员的定义,如图25-28所示。

图25-27 选择库类型

图25-28 显示对象成员及其信息

提示:在选择了一个对象成员后,在窗口下方将显示该成员的代码示例。同时还会包含一个超链接,单击该超链接可以跳转到对象成员所属的类或库。对于某些对象成员来说,也可以跳转到其上层类。

步骤3:在“对象浏览器”窗口的“搜索”栏中输入需要搜索的内容,单击“搜索”按钮,在“搜索结果”列表中将能够显示出所有的搜索结果。选择其中的一个选项,可以查看该对象的详细信息,如图25-29所示。

图25-29 显示搜索结果

提示:在“对象浏览器”中如果没有查询到需要的结果,可以按窗口工具栏上的“帮助”按钮,将打开“Excel帮助”窗口,使用该窗口可以获得更为详细的帮助信息。

代码的调试技巧

对于应用程序的开发,程序调试是一个重要步骤。VBA程序的调试有3种模式,它们是设计时、运行时和中断模式。Excel的Visual Basic编辑器提供了丰富的调试工具,包括断点调试、“立即窗口”、“本地窗口”和“监视窗口”等。下面对代码的调试技巧进行介绍。

步骤1:打开工作表并切换到Visual Basic编辑器,本示例的程序代码如下所示。在“代码”窗口中找到需要设置断点的语句,将光标放置到代码行中,选择“调试”|“切换断点”命令设置断点,如图25-30所示。

步骤2:按“F5”键运行程序,程序运行到断点位置即会暂停,同时标示处暂停位置,如图25-31所示。再次按“F5”键程序将继续运行。

图25-30 创建断点

图25-31 程序在断点处暂停

提示:鼠标在“代码”窗口边界标识条上单击可以直接创建断点。将插入点光标放置到程序中后,按“F9”键可以在该语句处添加断点,按“Ctrl+Shift+F9”组合键或选择“调试”|“清除断点”命令可以清除创建的断点。

步骤3:选择“视图”|“本地窗口”命令打开“本地窗口”对话框,按“F8”键逐语句执行程序。在运行到断点处时,“本地窗口”中将显示程序中表达式的当前值和变量类型,如图25-32所示。

图25-32 程序调试时“本地窗口”显示的内容

提示:“本地窗口”只有在中断模式下才能显示相应的内容,其只能显示当前过程中变量或对象的值,当程序从一个过程转到另一个过程时,其显示的内容也会相应发生改变。在“本地窗口”中,单击对象名称左侧的按钮可展开或收起对象的属性和成员列表。

步骤4:选择“视图”|“监视窗口”命令打开“监视窗口”对话框,选择“调试”|“添加监视”命令打开“添加监视”对话框,在“表达式”文本框中输入需要监视的条件,选择“当监视值为真时中断”单选按钮,单击“确定”按钮关闭对话框,如图25-33所示。

步骤5:再次打开“添加监视”对话框,在“表达式”文本框中输入变量名,单击“确定”按钮关闭对话框,如图25-34所示。

图25-33 添加监视条件

图25-34 添加第二个监视条件

步骤6:按“F5”键运行程序,程序将在满足条件(即i=50)时进入中断模式,“代码”窗口中指示出程序当前运行语句。同时在“监视窗口”中将可以看到监视变量的值,如图25-35所示。

图25-35 “监视窗口”中显示变量的变化

提示:如果需要编辑已有的监视条件,可以在“监视窗口”中选择某个监视条件后选择“调试”|“编辑监视”命令打开“编辑监视”对话框对监视条件进行编辑修改。在“监视窗口”中选择监视条件后,按Delete键可以将其删除。

步骤7:选择“视图”|“立即窗口”命令打开“立即窗口”对话框,在代码中添加“Debug.Print s”和“Debug.Print i”语句。按“F5”键运行程序,“立即窗口”中将显示变量s和变量i的运行结果,如图25-36所示。

图25-36 “立即窗口”中显示两个变量的值

提示:Debug是代码调试的一个重要工具,使用Debug对象的Print方法能使程序员在不暂停程序的情况下监控变量执行过程中的变化。Print方法在“立即窗口”中显示文本,Print方法显示的文本将不会在程序运行时看到,只能在“立即窗口”中显示。

步骤8:在“代码”窗口中为程序添加断点,运行程序后,在“立即窗口”中输入“?i”后按Enter键,立即窗口中将显示程序中断时变量i的值;输入“?s”后按Enter键,“立即窗口”中显示变量s的当前值,如图25-37所示。

图25-37 在“立即窗口”中显示变量的值

提示:“立即窗口”能够显示当前语境中变量或表达式的值,值可以通过Print方法或问号“?”来显示。这里要注意,“立即窗口”输出结果最多只有200行,超过200行则只显示最后200行的内容。

Excel 更改宏的安全设置操作步骤

宏的安全设置控制了宏是否可以执行,换句话说,通过更改宏的安全设置可以禁止或者允许宏的执行。更改宏的安全设置的操作步骤如下所述。

步骤1:单击功能区中“开发工具”选项卡下“代码”组中的“宏安全性”按钮,打开“信任中心”对话框。此处列出了所有可以选择的安全设置。例如此处选择“禁用所有宏,并发出通知”,如图25-17所示。单击“确定”按钮应用宏安全设置。

图25-17 宏安全设置

提示1:切换到“文件”选项卡,然后单击“选项”命令,打开“Excel选项”对话框,切换到对话框中“信任中心”选项卡,单击“信任中心设置”按钮,也可以打开“信任中心”对话框。

提示2:在Excel中所做的宏设置更改,仅应用于Excel中,而不会影响到其他的Office程序。

步骤2:打开包含宏的工作簿,此时就会发现,在编辑栏上方出现了一个安全警告,提示宏已被禁用,如图25-18所示。此时,包含宏的工作簿中的宏将不能被运行。单击安全警告提示条中的“启用内容”按钮,工作簿中的宏就可以运行了。

图25-18 安全警告

Excel 使用ActiveX控件来启动宏

在Excel中创建的宏实际上是一段程序代码,它是一个Sub过程。在工作表中添加了ActiveX控件后,用户可以在控件的事件过程中输入代码来修改宏过程,从而实现控制宏的启动。下面以使用“命令按钮”控件来启动宏为例介绍具体的操作方法。

步骤1:启动Excel并打开包含宏的工作表,在“开发工具”选项卡的“控件”组在中单击“插入”按钮,在打开的下拉列表中选择“ActiveX控件”组中的“命令按钮”控件,如图25-13所示。

步骤2:拖动鼠标在工作表中绘制一个命令按钮,鼠标右击该按钮,选择关联菜单中的“查看代码”命令。打开VBA编辑器,可以看到在按钮的代码窗口中自动添加了该按钮的Click事件过程。在该过程中添加宏所对应的过程名“设置表头格式”,如图25-14所示。

图25-13 选择“命令按钮”控件

图25-14 添加事件代码

步骤3:切换到Excel程序窗口,鼠标右击按钮,选择关联菜单中的“属性”命令打开“属性”对话框。将控件的“Caption”属性设置为“设置表头格式”,如图25-15所示。

图25-15 设置控件的“Caption”属性

步骤4:这样按钮上显示的文字将会改变,在“开发工具”选项卡的“控件”组中单击“设计模式”按钮退出控件的设计模式。单击工作表中的命令按钮控件即可启动宏,如图25-16所示。

图25-16 单击按钮启动宏

Excel 使用表单控件启动宏

Excel 2016提供了表单控件和ActiveX控件供用户使用,这两类控件都可以用来启动宏。其中,使用表单控件来启动宏在设计上比较简单,下面就介绍使用表单控件中的按钮控件来启动宏的方法。

步骤1:启动Excel 2016打开包含宏的工作表。在“开发工具”选项卡的“控件”组中单击“插入”按钮,选择“表单控件”组中的“按钮”选项,如图25-10所示。

图25-10 选择表单控件

步骤2:拖动鼠标在工作表中绘制按钮控件,绘制完成后Excel打开“指定宏”对话框。在“宏名”列表中选择宏,然后单击“确定”按钮,如图25-11所示。

步骤3:返回工作表,单元格中显示插入的按钮效果。在工作表的按钮文字上鼠标单击,修改按钮文字,如图25-12所示。完成设置后在工作表任意位置鼠标单击退出按钮编辑状态,此时单击该按钮即可启动宏。

图25-11 指定宏

图25-12 设置按钮文字

Excel 使用快捷键来快速启动宏

用户也可以为宏指定快捷键,通过快捷键来快速启动宏。下面介绍具体的操作方法。

在“宏”对话框的“宏名”列表中选择一个宏,单击“选项”按钮打开“宏选项”对话框。将插入点光标放置到“快捷键”文本框中后按键盘上的键,如这里的“Q”键,如图25-9所示。单击“确定”按钮关闭“宏选项”对话框后关闭“宏”对话框,则按“Ctrl+Shift+Q”组合键将启动宏。

图25-9 设置快捷键

提示:“宏”对话框中包含“执行”与“单步执行”两个按钮。两者都可以启动宏,但是在执行宏的方式上存在差别。单击“执行”按钮,相应的宏将从首行一直执行,直到宏过程的末行执行完毕为止;单击“单步执行”按钮,执行起始位置将跳转到该宏过程的入口位置后停止,用户需要选择“执行”或“单步执行”让宏代码继续执行。

Excel 使用“宏”对话框来启动宏

在Excel 2016中,可以使用多种方式来启动宏,其中常用的方式是使用“宏”对话框来启动已经录制完成的宏。

启动Excel并打开包含宏的工作表,在“开发工具”选项卡的“代码”组中单击“宏”按钮打开“宏”对话框。在对话框的“宏名”列表中选择需要执行的宏,单击“执行”按钮即可执行该宏,如图25-8所示。

图25-8 使用“宏”对话框启动宏

Excel 设置宏的保存方式

在进行宏的录制时,可以设置录制好的宏保存的位置,宏保存位置的不同决定了宏的适用范围。下面介绍设置宏保存位置的操作方法。

步骤1:启动Excel 2016并打开工作表,在“开发工具”选项卡的“代码”组中单击“录制宏”按钮打开“录制宏”对话框。在“保存在”下拉列表中选择宏保存的位置,设置完成后单击“确定”按钮,如图25-7所示。

图25-7 “在保存在”下拉列表中选择宏保存的位置

步骤2:开始宏的录制,录制完后的宏将按照设置保存在指定的位置。

提示:在录制宏时,如果需要创建只在当前工作簿中使用的宏,可以选择“当前工作簿”选项。如果需要在多个工作簿中调用录制的宏,但又不想在任何文件中都能使用该宏,则可以将宏保存在一个新工作簿中,即选择“新工作簿”选项。如果需要宏能够被所有的工作簿使用,则可以选择“个人宏工作簿”选项。

Excel 使用相对引用录制宏

在录制宏时,如果存在对单元格的操作,则在执行该宏时将只能对录制时操作的单元格进行操作,这是因为宏记录的是单元格的绝对引用。要解决这个问题,可以使用下面的方法来进行操作。

步骤1:启动Excel 2016并打开工作表,在“开发工具”选项卡的“代码”组中单击“使用相对引用”按钮,如图25-5所示。

图25-5 单击“使用相对引用”按钮

步骤2:在“开发工具”选项卡的“代码”组中单击“宏”按钮,打开“宏”对话框,在“宏名”文本框中输入宏名称后单击“创建”按钮,如图25-6所示。此时录制的宏,单元格的引用将使用相对引用。

图25-6 打开“宏”对话框

Excel 录制宏

宏是一系列存储于Visual Basic模块中的命令和函数,它们可以在需要执行时随时运行。对于工作中经常需要完成的某些重复工作,可以通过宏来进行操作。下面介绍在Excel中录制宏的方法。

步骤1:启动Excel并打开工作表,在“开发工具”选项卡的“代码”组中单击“录制宏”按钮,打开“录制新宏”对话框,在“宏名”输入框中输入宏的名称,单价“确定”按钮,如图25-3所示。

图25-3 打开“录制新宏”对话框并输入宏名称

步骤2:进入宏录制状态,对工作表中的所有操作将被录制为宏。对表头文字的格式进行设置,完成设置后单击“停止”按钮停止宏的录制,如图25-4所示。

图25-4 单击“停止”按钮停止宏的录制

Excel 在功能区中添加“开发工具”选项卡

在Excel中,宏的录制编辑、VBA程序的编辑和加载项的使用等操作都离不开相关的命令,这些命令都放置在功能区的“开发工具”选项卡中。如果功能区中没有“开发工具”选项卡,用户则需要向功能区添加该选项卡。下面介绍具体的操作方法。

步骤1:启动Excel 2016,创建一个空白文档。依次单击“文件”选项卡下的“选项”按钮,打开“Excel选项”对话框,在对话框的左侧列表中选择“自定义功能区”选项,在右侧“自定义功能区”下拉列表中选择“主选项卡”,在其下的列表中勾选“开发工具”复选框,单击“确定”按钮,如图25-1所示。

图25-1 勾选“开发工具”复选框

步骤2:此时“开发工具”选项卡被添加到功能区中,如图25-2所示。

图25-2 “开发工具”选项卡添加到功能区中