向还不是程序员的 Excel 高级用户介绍 Excel 2010 中的 Visual Basic for Applications (VBA)。本文提供 VBA 语言概述、有关如何在 Excel 2010 中访问 VBA 的说明、面向实际 Excel VBA 编程问题的解决方案的详细说明以及有关编程和调试的提示。
适用范围: Excel 2010 | Office 2010 | SharePoint Server 2010 | VBA | Visual Basic for Applications (VBA)
excel-2010-中使用-vba">1、为什么在 Excel 2010 中使用 VBA?
Microsoft Excel 2010 是一个功能相当强大的工具,您可以使用它操作、分析和显示数据。不过有时候,尽管标准 Excel 用户界面 (UI) 中提供了丰富的功能集,但您可能仍想要找到一种更简便的方法来执行繁琐的重复任务,或执行某个 UI 似乎无法解决的任务。幸运的是,像 Excel 这样的 Office 应用程序提供了 Visual Basic for Applications (VBA)。这是一种编程语言,您可以通过它来扩展这些应用程序。
VBA 是通过运行宏(在 Visual Basic 中编写的分步过程)来工作的。学习编程可能看起来很困难,但只要多些耐心,多学习像本文中介绍的示例,许多用户会发现,甚至只需学会少量 VBA 代码,就会使工作变得更加简单,而且可以在 Office 中完成他们以前认为不可能做到的事情。一旦学会了一些 VBA,便可以更加轻松地掌握更多的 VBA,因此,这种可能性是无限的。
迄今为止,在 Excel 中使用 VBA 最常见的原因就是自动完成重复的工作。例如,假定您有好几十个工作簿,每个工作簿都包含好几十个工作表,这些工作表都需要进行更改。这些更改既可能很简单,例如对固定的单元格范围应用新格式;也可能很复杂,例如,查看每个工作表中的数据的统计特征,再选择最恰当的图表类型来显示数据和这些特征,然后创建图表并设置相应格式。
对于上述任一种情况,您都可能不愿意手动执行这些任务,最多也就愿意重复执行几次。您可以改为使用 VBA 来编写要 Excel 执行的显式指令,从而自动完成这些任务。
VBA 不仅仅可用于重复任务。您还可以使用 VBA 构建 Excel 的新功能(例如,您可以开发新算法来分析数据,然后使用 Excel 中的图表功能显示结果),也可以执行将 Excel 与其他 Office 应用程序(如 Microsoft Access 2010)集成的任务。事实上,在所有 Office 应用程序中,Excel 最常用作一个类似于常规开发平台的工具。除了所有涉及列表和会计的显而易见的任务之外,从数据可视化到软件原型制作的大量任务中,开发人员都可使用 Excel。
尽管有这么多原因要在 Excel 2010 中使用 VBA,但一定要记住,解决问题的最佳方案可能是根本就不涉及 VBA。即使没有 VBA,Excel 仍然提供了大量的功能,就连高级用户也不可能完全熟悉这些功能。在决定采用 VBA 解决方案之前,请全面搜索”帮助”和联机资源以确定没有更简单的方法。
2、VBA 编程 101
2.1、通过代码使应用程序执行操作
您可能认为编写代码是一项神秘而又复杂的工作,但基本原则是使用日常推理,并且很容易掌握。Office 2010 应用程序的创建方式公开了一些名为对象 的内容,这些对象可以接收指令。通过向应用程序中的各种对象发送指令,可与应用程序进行交互。这些对象数量众多、类型各异而且使用灵活,但是它们也有局限性。这些对象只能执行为其设计的操作,并且只按照您的指令执行操作。
2.2、对象
在应用程序的名为对象模型 的层次结构中,编程对象彼此之间有系统地相互关联。对象模型会大致反映您在用户界面中看见的内容;例如,Excel 对象模型包含 Application、Workbook、Sheet 和 Chart 对象以及其他很多对象。对象模型是应用程序及其功能的概念图。
2.3 属性和方法
可通过设置对象的属性 和调用对象的方法 来操作对象。设置属性可更改对象的某些性质。调用方法可使对象执行某个操作。例如,Workbook 对象具有一个用于关闭工作簿的 Close 方法和一个用于表示工作簿中当前活动的工作表的 ActiveSheet 属性。
2.4 集合
许多对象有单复数之分,例如 Workbook 和 Workbooks;Worksheet 和 Worksheets,等等。对象的复数形式称作集合。集合对象用于对集合中的多个项执行一个操作。本文稍后将介绍如何使用 Worksheets 集合来更改工作簿中每个工作表的名称。
3、宏和 Visual Basic 编辑器
现在,您已对 Microsoft Excel 2010 如何公开其对象模型的方式有所了解,接下来可以尝试调用对象方法并设置对象属性。为此,您必须在一个位置以 Office 可以理解的方法编写代码;通常使用是 Visual Basic 编辑器。尽管默认情况下会安装该编辑器,但在功能区中启用该编辑器之前,许多用户都不知道该编辑器的存在。
3.1 “开发工具”选项卡
所有 Office 2010 应用程序都使用功能区。功能区中有一个”开发工具”选项卡,在此可以访问 Visual Basic 编辑器和其他开发人员工具。由于 Office 2010 在默认情况下不显示”开发工具”选项卡,因此必须使用以下过程启用该选项卡:
3.2 启用”开发工具”选项卡
- (1)在”文件”选项卡上,选择”选项”打开”Excel 选项”对话框。
- (2)单击该对话框左侧的”自定义功能区”。
- (3)在该对话框左侧的”从下列位置选择命令”下,选择”常用命令”。
- (4)在该对话框右侧的”自定义功能区”下,选择”主选项卡”,然后选中”开发工具”复选框。
- (5)单击”确定”。
在 Excel 显示”开发工具”选项卡之后,注意选项卡上”Visual Basic”、”宏”和”宏安全性”按钮的位置。
图 1. Excel 2010 中的”开发工具”选项卡
3.3 安全问题
单击”宏安全性”按钮可以指定哪些宏可以运行并需满足哪些条件。尽管未授权宏代码可能会严重损害计算机,但阻止您运行有帮助的宏的安全条件会严重妨碍您的工作效率。宏安全性是一个复杂而又涉及广泛的话题,您应研究并了解是否应使用 Excel 宏。
在本文中,请注意,如果当您打开一个包含宏的工作簿时,在功能区和工作表之间出现”安全警告: 宏已被禁用”条,则可单击”启用内容”按钮来启用宏。
此外,作为一种安全措施,您不能以默认的 Excel 文件格式 (.xlsx) 保存宏;而必须将宏保存在具有一个特殊扩展名 .xlsm 的文件中。
3.4 Visual Basic 编辑器
以下过程演示如何创建一个储存宏的新的空白工作簿。然后,可以按 .xlsm 格式保存该工作簿。
3.5 创建一个新的空白工作簿
- (1)单击”开发工具”选项卡上的”宏”按钮。
- (2)在随后出现的”宏”对话框中,在”宏名称”下键入 Hello。
- (3)单击”创建”按钮打开 Visual Basic 编辑器,其中包含已键入的新宏的大纲。
VBA 是一种功能齐全的编程语言,并具有一个相应的功能齐全的编程环境。本文只介绍那些您刚开始编程所使用的工具,而不介绍 Visual Basic 编辑器中的大部分工具。出于这个原因,请关闭 Visual Basic 编辑器左侧的”属性”窗口,并忽略在代码上方显示的两个下拉列表。
图 2. Visual Basic 编辑器
Sub 代表子例程,现在可将它定义为”宏”。运行 Hello 宏将运行 Sub Hello() 与 End Sub 之间的任何代码。
现在,请编辑宏,使其类似于以下代码。
Sub Hello()
MsgBox ("Hello, world!")
End Sub
返回到 Excel 中的”开发工具”选项卡,再次单击”宏”按钮。
在随后出现的列表中选择”Hello”宏,然后单击”运行”显示包含文本”Hello, world!”的小型消息框。
您刚才在 Excel 中创建并实现了自定义 VBA 代码。在消息框中单击”确定”关闭消息框并完成宏的运行。
如果未出现消息框,请检查宏安全性设置并重新启动 Excel。
3.6 使宏可供访问
还可以从”视图”选项卡访问”宏”对话框。但是,如果您频繁使用某个宏,则使用一个快捷方式或”快速访问工具栏”按钮来访问它可能会更方便。
若要在”快速访问工具栏”中为”Hello”宏创建一个按钮,请使用以下过程。
以下过程描述了如何在”快速访问工具栏”上为宏创建按钮:
3.7 在”快速访问工具栏”中为宏创建按钮
(1)单击”文件”选项卡。
(2)单击”选项”打开”Excel 选项”对话框,然后单击”快速访问工具栏”。
(3)在”从下列位置选择命令:”下的列表中,选择”宏”。在随后出现的列表中查找类似于”Book1!Hello”的文本,并选择该文本。
(4)单击”添加 >>”按钮将宏添加到右侧的列表中,然后单击”修改…”按钮选择与该宏关联的按钮图像。
(5)单击”确定”。现在,您应在”快速访问工具栏”中的”文件”选项卡上方看到新按钮。
现在,您不必使用”开发工具”选项卡,便可随时快速运行宏,赶快试试吧。