Office VBA 入门Getting started with VBA in Office

你是否面临着重复清理 Word 中五十个表格的工作?Are you facing a repetitive clean up of fifty tables in Word? 你是否希望有一个特殊的文档能够在打开时提示用户进行输入?Do you want a particular document to prompt the user for input when it opens? 你是否感觉难以弄明白如何将 Microsoft Outlook 中获取的联系人有效地导入 Microsoft Excel 电子表格?Are you having difficulty figuring out how to get your contacts from Microsoft Outlook into a Microsoft Excel spreadsheet efficiently?

可以使用 Office 的 Visual Basic for Applications (VBA)(一种可用于扩展 Office 应用程序的简单且强大的编程语言)来执行这些任务并完成许多其他任务。You can perform these tasks and accomplish a great deal more by using Visual Basic for Applications (VBA) for Office—a simple, but powerful programming language that you can use to extend Office applications.

本文适用于希望了解 VBA 和希望深入了解编程如何帮助他们自定义 Office 的有经验的 Office 用户。This article is for experienced Office users who want to learn about VBA and who want some insight into how programming can help them to customize Office.

Office 应用程序套件具有一组丰富的功能。The Office suite of applications has a rich set of features. 可通过多种不同的方法对文档、电子邮件、数据库、表单、电子表格和演示文稿进行创作、格式设置和操纵。There are many different ways to author, format, and manipulate documents, email, databases, forms, spreadsheets, and presentations. Office VBA 编程的杰出之处在于,你可以使用鼠标、键盘或对话框执行的几乎每一个操作也都可以使用 VBA 完成。The great power of VBA programming in Office is that nearly every operation that you can perform with a mouse, keyboard, or a dialog box can also be done by using VBA. 此外,若某个操作可以使用 VBA 执行一次,则可以同样轻松地执行该操作一百次。Further, if it can be done once with VBA, it can be done just as easily a hundred times. (实际上,自动执行重复任务是 Office VBA 最常见的用途之一。)(In fact, the automation of repetitive tasks is one of the most common uses of VBA in Office.)

除了可通过编写 VBA 脚本来加速执行日常任务外,还可以使用 VBA 为 Office 应用程序添加新功能,或以特定于业务需要的方式来提示文档用户并与之交互。Beyond the power of scripting VBA to accelerate every-day tasks, you can use VBA to add new functionality to Office applications or to prompt and interact with the user of your documents in ways that are specific to your business needs. 例如,可以编写一些 VBA 代码,使其在用户首次尝试保存文档时显示一个弹出消息,提醒用户将文档保存到特定网络驱动器上。For example, you could write some VBA code that displays a pop up message that reminds users to save a document to a particular network drive the first time they try to save it.

本文探讨了一些利用 VBA 编程强大功能的主要原因。This article explores some of the primary reasons to leverage the power of VBA programming. 它探讨了 VBA 语言和可用于处理你的解決方案的开箱即用工具。It explores the VBA language and the out-of-the-box tools that you can use to work with your solutions. 最后,它包括一些提示和方法以避免一些常见编程错误。Finally, it includes some tips and ways to avoid some common programming frustrations and missteps.


有兴趣开发跨多个平台扩展 Office 体验的解决方案吗?Interested in developing solutions that extend the Office experience across multiple platforms? 查看新的 Office 外接程序模型Check out the new Office Add-ins model. 与 VSTO 外接程序和解决方案相比,Office 外接程序的占地面积较小,您可以使用几乎任何 web 编程技术(如 HTML5、JavaScript、CSS3 和 XML)构建它们。Office Add-ins have a small footprint compared to VSTO Add-ins and solutions, and you can build them by using almost any web programming technology, such as HTML5, JavaScript, CSS3, and XML.

何时使用 VBA 及使用原因When to use VBA and why

考虑使用 Office 中的 VBA 编程功能有若干主要原因。There are several principal reasons to consider VBA programming in Office.

自动化和重复Automation and repetition

当应用到用于设置格式或纠正问题的重复性解决方案时,VBA 非常高效。VBA is effective and efficient when it comes to repetitive solutions to formatting or correction problems. 举例来说,你是否曾经在 Word 中更改过每一页顶部的段落样式?For example, have you ever changed the style of the paragraph at the top of each page in Word? 你是否曾经不得不对从 Excel 粘贴到 Word 文档或 Outlook 电子邮件中的多个表格重新设置格式?Have you ever had to reformat multiple tables that were pasted from Excel into a Word document or an Outlook email? 你是否曾经必须对多个 Outlook 联系人进行相同的更改?Have you ever had to make the same change in multiple Outlook contacts?

如果你有必须进行十多二十次的更改,则可能值得使用 VBA 来自动进行该更改。If you have a change that you have to make more than ten or twenty times, it may be worth automating it with VBA. 如果是必须进行数百次的更改,那么当然值得考虑。If it is a change that you have to do hundreds of times, it certainly is worth considering. 你可手动完成的几乎任何格式设置或编辑更改都可用 VBA 完成。Almost any formatting or editing change that you can do by hand, can be done in VBA.

用户交互的扩展Extensions to user interaction

有时,你希望鼓励或强迫用户以不属于标准应用程序的特定方式与 Office 应用程序或文档进行交互。There are times when you want to encourage or compel users to interact with the Office application or document in a particular way that is not part of the standard application. 例如,你可能希望在用户打开、保存或打印文档时,提示他们执行一些特定操作。For example, you might want to prompt users to take some particular action when they open, save, or print a document.

Office 应用程序之间的交互Interaction between Office applications

你是否需要将所有联系人从 Outlook 复制到 Word,然后以特定方式其进行格式设置?Do you need to copy all of your contacts from Outlook to Word and then format them in some particular way? 或者,你是否需要将数据从 Excel 移到一组 PowerPoint 幻灯片?Or, do you need to move data from Excel to a set of PowerPoint slides? 有时,简单的复制和粘贴不能达到你的目的,或者速度太慢。Sometimes simple copy and paste does not do what you want it to do, or it is too slow. 你可以使用 VBA 编程同时与两个或更多 Office 应用程序的详细信息交互,然后根据一个应用程序中的内容修改另一个应用程序中的内容。You can use VBA programming to interact with the details of two or more Office applications at the same time and then modify the content in one application based on the content in another.

换另一种方式执行任务Doing things another way

VBA 编程是一种功能强大的解决方案,但并不总是最佳方法。VBA programming is a powerful solution, but it is not always the optimal approach. 有时,有必要使用其他方式来实现你的目标。Sometimes it makes sense to use other ways to achieve your aims.

关键问题是,是否有更简单的方法。The critical question to ask is whether there is an easier way. 开始 VBA 工程之前,请考虑内置的工具和标准功能。Before you begin a VBA project, consider the built-in tools and standard functionalities. 举例来说,如果你有非常耗时的编辑或布局任务,请考虑使用样式或加速键来解决问题。For example, if you have a time-consuming editing or layout task, consider using styles or accelerator keys to solve the problem. 你是否能执行一次任务,然后使用 CTRL+Y(重做)来重复操作?Can you perform the task once and then use CTRL+Y (Redo) to repeat it? 你是否能使用正确的格式或模板创建新文档,然后将内容复制到该新文档中?Can you create a new document with the correct format or template, and then copy the content into that new document?

Office 应用程序功能强大;你需要的解决方案可能已经存在。Office applications are powerful; the solution that you need may already be there. 在开始编程之前,请花一些时间来详细了解 Office。Take some time to learn more about Office before you jump into programming.

在开始 VBA 工程之前,请确保你有时间来使用 VBA。Before you begin a VBA project, ensure that you have the time to work with VBA. 编程需要专注,并且不可预测。Programming requires focus and can be unpredictable. 特别是作为初学者,除非你有时间来仔细认真地工作,否则绝不要转向编程。Especially as a beginner, never turn to programming unless you have time to work carefully. 如果迫在眉睫的最后期限可能会造成非常紧张的情况,请尝试编写“快速脚本”来解决问题。Trying to write a "quick script" to solve a problem when a deadline looms can result in a very stressful situation. 如果你的时间很紧,则可能需要使用传统方法,即使这些方法单调又重复。If you are in a rush, you might want to use conventional methods, even if they are monotonous and repetitive.

VBA 编程 101VBA Programming 101

通过代码使应用程序执行操作Using code to make applications do things

你可能认为编写代码既神秘又困难,但是基本原则使用日常推理且完全可以访问。You might think that writing code is mysterious or difficult, but the basic principles use every-day reasoning and are quite accessible. Microsoft Office 应用程序以这样的方式创建:它们公开能够接收指令的事物(称为对象),这与将电话设计为包含按键以用于和电话交互的方式相同。Microsoft Office applications are created in such a way that they expose things called objects that can receive instructions, in much the same way that a phone is designed with buttons that you use to interact with the phone. 按下按键时,电话会识别指令,并按照拨号顺序包含对应的号码。When you press a button, the phone recognizes the instruction and includes the corresponding number in the sequence that you are dialing. 在编程时,你通过向应用程序中的各种对象发送指令与应用程序进行交互。In programming, you interact with the application by sending instructions to various objects in the application. 这些对象是可扩展的,但各有各的限制。These objects are expansive, but they have their limits. 这些对象只能执行为其设计的操作,并且只能按照你的指令执行操作。They can only do what they are designed to do, and they will only do what you instruct them to do.

例如,假设用户在 Word 中打开一个文档,进行一些更改,保存文档,然后将其关闭。For example, consider the user who opens a document in Word, makes a few changes, saves the document, and then closes it. 在 VBA 编程领域,Word 会公开 Document 对象。In the world of VBA programming, Word exposes a Document object. 通过使用 VBA 代码,你可以指示 Document 对象执行诸如打开、保存或关闭等操作。By using VBA code, you can instruct the Document object to do things such as Open, Save, or Close.

下面一节讨论如何组织和描述对象。The following section discusses how objects are organized and described.

对象模型The Object Model

开发人员在层次结构中组织编程对象,该层次结构被称为应用程序的对象模型。Developers organize programming objects in a hierarchy, and that hierarchy is called the object model of the application. 例如,Word 具有包含 Document 对象的顶级应用程序对象。Word, for example, has a top-level Application object that contains a Document object. Document 对象包含 Paragraph 对象等。The Document object contains Paragraph objects and so on. 对象模型大致反映你在用户界面中看到的内容。Object models roughly mirror what you see in the user interface. 它们是应用程序及其功能的概念图。They are a conceptual map of the application and its capabilities.

对象的定义被称为类,因此,你可能看到这两个术语互换使用。The definition of an object is called a class, so you might see these two terms used interchangeably. 从技术上来讲,类是用于创建或实例化对象的说明或模板。Technically, a class is the description or template that is used to create, or instantiate, an object.

对象一旦存在,你就可以通过设置其属性和调用其方法来进行操控。Once an object exists, you can manipulate it by setting its properties and calling its methods. 如果将对象视为一个名词,则属性是描述该名词的形容词,而方法是使该名词活动起来的动词。If you think of the object as a noun, the properties are the adjectives that describe the noun and the methods are the verbs that animate the noun. 更改某个属性将更改对象外观或行为的某些品质。Changing a property changes some quality of appearance or behavior of the object. 调用任一对象方法将导致对象执行某些操作。Calling one of the object methods causes the object to perform some action.

本文中的 VBA 代码针对打开的 Office 应用程序运行,在该应用程序中,代码操控的许多对象已经打开并正在运行;例如,Application 本身、Excel 中的 Worksheet、Word 中的 Document、PowerPoint 中的 Presentation、Outlook 中的 Explorer 和 Folder 对象。The VBA code in this article runs against an open Office application where many of the objects that the code manipulates are already up and running; for example, the Application itself, the Worksheet in Excel, the Document in Word, the Presentation in PowerPoint, the Explorer and Folder objects in Outlook. 在了解对象模型的基本布局以及 Application 的一些关键属性(允许您访问当前状态)后,便可以开始使用 Office VBA 扩展和操控 Office 应用程序了。Once you know the basic layout of the object model and some key properties of the Application that give access to its current state, you can start to extend and manipulate that Office application with VBA in Office.


例如,在 Word 中,你可以通过使用 Application 对象的 ActiveDocument 属性更改属性,并调用当前 Word 文档的方法。In Word, for example, you can change the properties and invoke the methods of the current Word document by using the ActiveDocument property of the Application object. ActiveDocument 属性将返回对当前在 Word 应用程序中处于活动状态的 Document 对象的引用。This ActiveDocument property returns a reference to the Document object that is currently active in the Word application. “返回引用”意思是“可以访问。”"Returns a reference to" means "gives you access to."

下面的代码完全按照它所表达的含义运行;即,保存应用程序中的活动文档。The following code does exactly what it says; that is, it saves the active document in the application.


从左到右读取代码,“在该 Application 中(Document 由 ActiveDocument 引用),调用 Save 方法。”Read the code from left to right, "In this Application, with the Document referenced by ActiveDocument, invoke the Save method." 请注意,Save 是最简单的方法;它不需要从你那里获得任何详细指令。Be aware that Save is the simplest form of method; it does not require any detailed instructions from you. 你指示 Document 对象执行 Save 指令,它不需要你输入更多的信息。You instruct a Document object to Save and it does not require any more input from you.

如果某个方法需要更多信息,则这些详细信息被称为参数。If a method requires more information, those details are called parameters. 下面的代码运行 SaveAs 方法,该方法需要文件的新名称。The following code runs the SaveAs method, which requires a new name for the file.

Application.ActiveDocument.SaveAs ("New Document Name.docx")

方法名称后的括号中列出的值是参数。Values listed in parentheses after a method name are the parameters. 此处,文件的新名称是 SaveAs 方法的参数。Here, the new name for the file is a parameter for the SaveAs method.


可以使用相同的语法来设置用于读取属性的属性。You use the same syntax to set a property that you use to read a property. 下面的代码执行一种方法以选择 Excel 中的单元格 A1,然后设置属性以在该单元格中放置内容。The following code executes a method to select cell A1 in Excel and then to set a property to put something in that cell.

    Application.Selection.Value = "Hello World"

VBA 编程的第一个挑战是了解每个 Office 应用程序的对象模型以及阅读对象、方法和属性语法。The first challenge in VBA programming is to get a feeling for the object model of each Office application and to read the object, method, and property syntax. 对象模型在所有 Office 应用程序中都类似,但每个对象模型都特定于它所操控的文档和对象的种类。The object models are similar in all Office applications, but each is specific to the kind of documents and objects that it manipulates.

代码段的第一行中有 Application 对象(这次是 Excel),然后是 ActiveSheet,它提供对活动工作表的访问。In the first line of the code snippet, there is the Application object, Excel this time, and then the ActiveSheet, which provides access to the active worksheet. 在这之后,是不太熟悉的术语“Range”,它表示“用此方法定义单元格的范围。”After that is a term not as familiar, Range, which means "define a range of cells in this way." 代码指示 Range 自行创建,并且只将 A1 作为其定义的一组单元格。The code instructs Range to create itself with just A1 as its defined set of cells. 也就是说,代码的第一行定义对象“Range”,并对其运行方法以选择它。In other words, the first line of code defines an object, the Range, and runs a method against it to select it. 结果会自动存储在名为 SelectionApplication 的另一个属性中。The result is automatically stored in another property of the Application called Selection.

代码的第二行将 SelectionValue 属性设置为文本“Hello World”,并且该值出现在单元格 A1 中。The second line of code sets the Value property of Selection to the text "Hello World", and that value appears in cell A1.

你编写的最简单的 VBA 代码可能只是获取对 Office 应用程序中你要处理的对象的访问权限并设置属性。The simplest VBA code that you write might simply gain access to objects in the Office application that you are working with and set properties. 例如,你可以在 VBA 脚本中获取对 Word 中表中各行的访问权限并更改其格式。For example, you could get access to the rows in a table in Word and change their formatting in your VBA script.

这听起来简单,但可能非常有用;一旦编写了代码,你就可以利用编程的所有强大功能在多个表或文档中进行相同更改,或者依据某种逻辑或条件来进行更改。That sounds simple, but it can be incredibly useful; once you can write that code, you can harness all of the power of programming to make those same changes in several tables or documents, or make them according to some logic or condition. 对于计算机而言,进行 1000 项更改与进行 10 项更改并无不同,因此这里对于较大的文档和问题而言就有了规模效应,而这正是 VBA 能够真正出彩和节省时间的原因。For a computer, making 1000 changes is no different from making 10, so there is an economy of scale here with larger documents and problems, and that is where VBA can really shine and save you time.

宏和 Visual Basic 编辑器Macros and the Visual Basic Editor

既然对 Office 应用程序公开其对象模型的方式有了一定了解,你可能会渴望尝试调用对象方法、设置对象属性,以及响应对象事件。Now that you know something about how Office applications expose their object models, you are probably eager to try calling object methods, setting object properties, and responding to object events. 为此,你必须在 Office 可以理解的位置以 Office 可以理解的方式编写代码;通常使用 Visual Basic 编辑器进行。To do so, you must write your code in a place and in a way that Office can understand; typically, by using the Visual Basic Editor. 尽管默认情况下已安装该编辑器,但在功能区上将其启用之前,许多用户都不知道有该编辑器。Although it is installed by default, many users do not know that it is even available until it is enabled on the ribbon.

所有 Office 应用程序都使用该功能区。All Office applications use the ribbon. 功能区上的一个选项卡是 “开发工具” 选项卡,在此可以访问 Visual Basic 编辑器和其他开发人员工具。One tab on the ribbon is the Developer tab, where you access the Visual Basic Editor and other developer tools. 由于 Office 在默认情况下并不显示 “开发工具” 选项卡,因此你必须使用以下过程启用它:Because Office does not display the Developer tab by default, you must enable it by using the following procedure:

启用“开发工具”选项卡To enable the Developer tab

  1. “文件” 选项卡上,选择 “选项” 以打开 “选项” 对话框。On the File tab, choose Options to open the Options dialog box.

  2. 选择该对话框左侧的 “自定义功能区”Choose Customize Ribbon on the left side of the dialog box.

  3. 在该对话框左侧的 “从下列位置选择命令” 下,选择 “常用命令”Under Choose commands from on the left side of the dialog box, select Popular Commands.

  4. 在该对话框右侧的 “自定义功能区” 下,从下拉列表框中选择 “主选项卡”,然后选中 “开发工具” 复选框。Under Customize the Ribbon on the right side of the dialog box, select Main Tabs in the drop down list box, and then select the Developer checkbox.

  5. 选择“确定”。Choose OK.


在 Office 2007 中,显示 “开发工具” 选项卡的方法是选择 Office 按钮,选择 “选项”,然后在 “选项” 对话框的 “常用” 类别中选中 “在功能区显示‘开发工具’选项卡” 复选框。In Office 2007, you displayed the Developer tab by choosing the Office button, choosing Options, and then selecting the Show Developer tab in Ribbon check box in the Popular category of the Options dialog box.

启用 “开发工具” 选项卡后,可以轻松找到 “Visual Basic”“宏” 按钮。After you enable the Developer tab, it is easy to find the Visual Basic and Macros buttons.

图 1.“开发工具”选项卡上的按钮Figure 1. Buttons on the Developer tab


安全问题Security issues

为了保护 Office 用户免受病毒和危险宏代码的影响,你无法在使用标准文件扩展名的标准 Office 文档中保存宏代码,To protect Office users against viruses and dangerous macro code, you cannot save macro code in a standard Office document that uses a standard file extension. 而是必须将代码保存在带有特殊扩展名的文件中。Instead, you must save the code in a file with a special extension. 例如,你无法将宏保存在带有 .docx 扩展名的标准 Word 文档中;而是必须使用带有 .docm 扩展名的特殊 Word 启用宏的文档。For example you cannot save macros in a standard Word document with a .docx extension; instead, you must use a special Word Macro-Enabled Document with a .docm extension.

打开 .docm 文件时,Office 安全功能可能仍会在通知或不通知你的情况下阻止文档中的宏运行。When you open a .docm file, Office security might still prevent the macros in the document from running, with or without telling you. 在所有 Office 应用程序的信任中心中检查设置和选项。Examine the settings and options in the Trust Center on all Office applications. 默认设置禁止宏运行,但会提示你宏已禁用,并为你提供用于为该文档重新启用宏的选项。The default setting disables macro from running, but warns you that macros have been disabled and gives you the option to turn them back on for that document.

通过创建可信位置、受信任的文档或受信任的发布者,可以指定运行宏的特定文件夹。You can designate specific folders where macros can run by creating Trusted Locations, Trusted Documents, or Trusted Publishers. 最便携的选项是使用受信任的发布者,它使用分发的数字签名文档。The most portable option is to use Trusted Publishers, which works with digitally signed documents that you distribute. 有关特定 Office 应用程序中的安全设置的详细信息,请打开 “选项” 对话框,然后依次选择 “信任中心”“信任中心设置”For more information about the security settings in a particular Office application, open the Options dialog box, choose Trust Center, and then choose Trust Center Settings.


某些 Office 应用程序(如 Outlook)默认情况下会将宏保存在你本地计算机上的主模板中。Some Office applications, like Outlook, save macros by default in a master template on your local computer. 虽然在运行自己的宏时,该策略会减少自己的计算机上的本地安全问题,但如果你要分发自己的宏,则需要部署策略。Although that strategy reduces the local security issues on your own computer when you run your own macros, it requires a deployment strategy if you want to distribute your macro.

录制宏Recording a macro

选择 “开发工具” 选项卡上的 “宏” 按钮时,会打开 “宏” 对话框,该对话框允许访问可从特定文档或应用程序访问的 VBA 子例程或宏。When you choose the Macro button on the Developer tab, it opens the Macros dialog box, which gives you access to VBA subroutines or macros that you can access from a particular document or application. “Visual Basic” 按钮可打开 Visual Basic 编辑器,可使用该编辑器创建和编辑 VBA 代码。The Visual Basic button opens the Visual Basic Editor, where you create and edit VBA code.

Word 和 Excel 中的 “开发工具” 选项卡上的另一个按钮是 “录制宏” 按钮,该按钮自动生成 VBA 代码,能够再现你在应用程序中执行的操作。Another button on the Developer tab in Word and Excel is the Record Macro button, which automatically generates VBA code that can reproduce the actions that you perform in the application. “录制宏” 是可以用于详细了解 VBA 的理想工具。Record Macro is a terrific tool that you can use to learn more about VBA. 阅读生成的代码可以让你深入了解 VBA,并在你作为用户需要了解的 Office 知识和作为程序员需要了解的 Office 知识之间架起一座稳定的桥梁。Reading the generated code can give you insight into VBA and provide a stable bridge between your knowledge of Office as a user and your knowledge as a programmer. 唯一需要说明的一点是,生成的代码可能令人困惑,因为宏编辑器必须对你的意图进行一些假设,而这些假设不一定准确。The only caveat is that the generated code can be confusing because the Macro editor must make some assumptions about your intentions, and those assumptions are not necessarily accurate.

录制宏To record a macro

  1. 将 Excel 打开为新的工作簿,并选择功能区中的 “开发工具” 选项卡。Open Excel to a new Workbook and choose the Developer tab in the ribbon. 选择 “录制宏” 并接受 “录制宏” 对话框中的所有默认设置,将 “Macro1” 作为宏名称,将 “此工作簿” 作为位置。Choose Record Macro and accept all of the default settings in the Record Macro dialog box, including Macro1 as the name of the macro and This Workbook as the location.

  2. 选择 “确定” 开始录制宏。Choose OK to begin recording the macro. 请注意按钮文本是如何变为 “停止录制” 的。Note how the button text changes to Stop Recording. 完成想要录制的操作后即选择该按钮。Choose that button the instant you complete the actions that you want to record.

  3. 选择单元格 B1 并键入程序员经典的第一个字符串:Hello World。Choose cell B1 and type the programmer's classic first string: Hello World. 停止键入并查看 “停止录制” 按钮;它将灰显,因为 Excel 正在等待你完成在该单元格中键入值的操作。Stop typing and look at the Stop Recording button; it is grayed out because Excel is waiting for you to finish typing the value in the cell.

  4. 选择单元格 B2 以完成单元格 B1 中的操作,然后选择 “停止录制”Choose cell B2 to complete the action in cell B1, and then choose Stop Recording.

  5. “开发工具” 选项卡上选择 “宏”,选择 “Macro1”(如果未选择),然后选择 “编辑” 以在 Visual Basic 编辑器中查看 Macro1 的代码。Choose Macros on the Developer tab, select Macro1 if it is not selected, and then choose Edit to view the code from Macro1 in the Visual Basic Editor.

图 2. Visual Basic Editor 中的宏代码Figure 2. Macro code in Visual Basic Editor

Visual Basic Editor 中的宏代码

查看代码Looking at the code

你创建的宏看起来应类似于如下代码。The macro that you created should look similar to the following code.

Sub Macro1()
' Macro1 Macro
    ActiveCell.FormulaR1C1 = "Hello World"
End Sub

请注意与前面在单元格 A1 中选择文本的代码段的相似之处,以及不同之处。Be aware of the similarities to the earlier code snippet that selected text in cell A1, and the differences. 在此代码中,选择了单元格 B1,然后将字符串“Hello World”应用到了已激活的单元格。In this code, cell B1 is selected, and then the string "Hello World" is applied to the cell that has been made active. 文本两边的引号指定与数字值对照的字符串值。The quotes around the text specify a string value as opposed to a numeric value.

是否记得如何选择单元格 B2 以再次显示 “停止录制” 按钮?Remember how you chose cell B2 to display the Stop Recording button again? 该操作也显示为一行代码。That action shows up as a line of code as well. 宏录制器记录每次击键。The macro recorder records every keystroke.

以撇号开头并由编辑器标为绿色的代码行是说明代码或提醒你和其他程序员代码用途的注释。The lines of code that start with an apostrophe and colored green by the editor are comments that explain the code or remind you and other programmers the purpose of the code. VBA 忽略以单引号开头的任何行或行的一部分。VBA ignores any line, or portion of a line, that begins with a single quote. 在代码中编写清除的相应注释非常重要,但该讨论是不在本文范围内。Writing clear and appropriate comments in your code is an important topic, but that discussion is out of the scope of this article. 本文后面对此代码的引用不包括这四个注释行。Subsequent references to this code in the article do not include those four comment lines.

在宏录制器生成代码时,它使用复杂的算法确定预期的方法和属性。When the macro recorder generates the code, it uses a complex algorithm to determine the methods and the properties that you intended. 如果未能识别给定的属性,有许多可用的资源能够提供帮助。If you do not recognize a given property, there are many resources available to help you. 例如,在已录制宏中,宏录制器生成了引用 FormulaR1C1 属性的代码。For example, in the macro that you recorded, the macro recorder generated code that refers to the FormulaR1C1 property. 不清楚上述内容的含义?Not sure what that means?


请注意,Application 对象隐含在所有 VBA 宏中。Be aware that Application object is implied in all VBA macros. 录制的代码适用于每行开头的 ApplicationThe code that you recorded works with Application. at the beginning of each line.

使用开发工具帮助Using Developer Help

选择已录制宏中的“FormulaR1C1”****,并按 F1。Select FormulaR1C1 in the recorded macro and press F1. 帮助系统运行快速搜索,确定相应的主题位于 Excel 帮助的“Excel 开发工具”部分,并列出 FormulaR1C1 属性。The Help system runs a quick search, determines that the appropriate subjects are in the Excel Developer section of the Excel Help, and lists the FormulaR1C1 property. 可以选择该链接了解有关该属性的详细信息,但在执行该操作前,请注意窗口底部附近的 Excel 对象模型参考链接。You can choose the link to read more about the property, but before you do, be aware of the Excel Object Model Reference link near the bottom of the window. 选择该链接可查看 Excel 在其对象模型中用于描述工作表及其组件所使用的对象的长列表。Choose the link to view a long list of objects that Excel uses in its object model to describe the Worksheets and their components.

选择以上任一选项以查看适用于该特定对象的属性和方法,以及对于不同相关选项的交叉引用。Choose any one of those to see the properties and methods that apply to that particular object, along with cross references to different related options. 许多帮助项也有简短的代码示例,可以为你提供帮助。Many Help entries also have brief code examples that can help you. 例如,可以点击 Borders 对象中的链接,以查看如何在 VBA 中设置边框。For example, you can follow the links in the Borders object to see how to set a border in VBA.

Worksheets(1).Range("A1").Borders.LineStyle = xlDouble

编辑代码Editing the code

边框代码看起来与录制的宏不同。The Borders code looks different from the recorded macro. 对象模型令人迷惑的一点是可使用多种方法处理任何给定对象(在本例中为单元格 A1)。 One thing that can be confusing with an object model is that there is more than one way to address any given object, cell A1 in this example.

有时,学习编程的最好方法是对某些运行代码进行微小更改,然后查看结果。Sometimes the best way to learn programming is to make minor changes to some working code and see what happens as a result. 立即尝试。Try it now. 在 Visual Basic 编辑器中打开 Macro1,并将代码更改为以下内容。Open Macro1 in the Visual Basic Editor and change the code to the following.

Sub Macro1()
    Worksheets(1).Range("A1").Value = "Wow!"
    Worksheets(1).Range("A1").Borders.LineStyle = xlDouble
End Sub


在处理代码时尽可能地使用复制和粘贴操作,以避免键入错误。 Use Copy and Paste as much as possible when working with code to avoid typing errors.

无需保存代码即可进行尝试,因此请返回到 Excel 文档,选择 “开发工具” 选项卡上的 “宏”,选择 “Macro1”,然后选择 “运行”You do not need to save the code to try it out, so return to the Excel document, choose Macros on the Developer tab, choose Macro1, and then choose Run. 单元格 A1 现在包含文本 Wow! Cell A1 now contains the text Wow! 且其四周有双线边框。and has a double-line border around it.

图 3.第一个宏的运行结果Figure 3. Results of your first macro


刚才录制了宏,阅读了对象模型文档,并通过简单编程制作了一个可执行某项操作的 VBA 程序。You just combined macro recording, reading the object model documentation, and simple programming to make a VBA program that does something. 恭喜!Congratulations!

宏没有运行?Did not work? 阅读 VBA 中的调试建议。Read on for debugging suggestions in VBA.

编程提示和技巧Programming tips and tricks

从示例开始Start with examples

VBA 社区非常大;在 Web 上进行搜索几乎总是可以获得其执行的操作与你要执行的操作类似的 VBA 代码示例。The VBA community is very large; a search on the Web can almost always yield an example of VBA code that does something similar to what you want to do. 如果你找不到好的示例,请尝试将任务分解为较小的单元,然后搜索其中每个单元,或者尝试考虑更常见、但类似的问题。If you cannot find a good example, try to break the task down into smaller units and search on each of those, or try to think of a more common, but similar problem. 从示例开始可为你节省数小时的时间。Starting with an example can save you hours of time.

这并不意味着编写严密的免费代码在 Web 上等待你使用。That does not mean that free and well-thought-out code is on the Web waiting for you to come along. 事实上,你找到的一些代码可能有缺陷或错误。In fact, some of the code that you find might have bugs or mistakes. 但你联机或在 VBA 文档中找到的示例可为你提供良好的开端。The idea is that the examples you find online or in VBA documentation give you a head start. 请记住,学习编程需要时间和思索。Remember that learning programming requires time and thought. 在你忙于使用另一个解决方案来解决你的问题之前,请询问自己 VBA 是不是解决此问题的正确选择。Before you get in a big rush to use another solution to solve your problem, ask yourself whether VBA is the right choice for this problem.

处理较简单的问题Make a simpler problem

编程过程可能会迅速变得复杂化。Programming can get complex quickly. 将问题分解为尽可能小的逻辑单元,然后独立编写并测试每个单元,这一点很重要,尤其对于初学者。It is critical, especially as a beginner, that you break the problem down to the smallest possible logical units, then write and test each piece in isolation. 如果你面前有太多代码,并且你变得迷惑或糊涂,请停止并撇开问题。If you have too much code in front of you and you get confused or muddled, stop and set the problem aside. 当你重新面对问题时,可以将一个小问题复制到新模块中,解决该问题,让代码运行,并测试它以确保它能够运行。When you come back to the problem, copy out a small piece of the problem into a new module, solve that piece, get the code working, and test it to ensure that it works. 然后移动到下一个部分。Then move on to the next part.

缺陷和调试Bugs and debugging

有两种主要类型的编程错误:语法 错误,即违反编程语言的语法规则;运行时 错误,即看上去语法正确,但在 VBA 尝试执行代码时失败。There are two main types of programming errors: syntax errors, which violate the grammatical rules of the programming language, and run-time errors, which look syntactically correct, but fail when VBA attempts to execute the code.

虽然修复这些错误可能令人沮丧,但语法错误很容易捕获;如果您在代码中键入语法错误,Visual Basic 编辑器会发出嘟嘟声并闪烁。Although they can be frustrating to fix, syntax errors are easy to catch; the Visual Basic Editor beeps and flashes at you if you type a syntax error in your code.

例如,在 VBA 中必须使用双引号将字符串值括起来。For example, string values must be surrounded by double quotes in VBA. 若要了解改用单引号会发生什么情况,请返回到 Visual Basic 编辑器并将代码示例中的 "Wow!"To find out what happens when you use single quotes instead, return to the Visual Basic Editor and replace the "Wow!" 替换为 'Wow!'string in the code example with 'Wow!' (即,单词 Wow 用单引号括起来)。(that is, the word Wow enclosed in single quotes). 如果选择下一行,Visual Basic 编辑器会做出响应。If you choose the next line, the Visual Basic Editor reacts. 错误“编译错误:预期: 表达式”不是很有帮助,但是生成错误的行变为红色,告诉你该行中存在语法错误,因此,此程序将无法运行。The error "Compile error: Expected: expression" is not that helpful, but the line that generates the error turns red to tell you that you have a syntax error in that line and as a result, this program will not run.

选择 “确定”,然后将文本改回“Wow!”。Choose OK and change the text back to"Wow!".

运行时错误较难以捕获,因为编程语法看上去正确,但代码在 VBA 尝试执行时失败。Runtime errors are harder to catch because the programming syntax looks correct, but the code fails when VBA tries to execute it.

例如,打开 Visual Basic 编辑器,并在宏中将 Value 属性名称更改为 ValueX,故意引入运行时错误,因为 Range 对象没有名为 ValueX 的属性。For example, open the Visual Basic Editor and change the Value property name toValueX in your Macro, deliberately introducing a runtime error since the Range object does not have a property called ValueX. 返回到 Excel 文档,打开 “宏” 对话框并再次运行 Macro1。Go back to the Excel document, open the Macros dialog box and run Macro1 again. 应该会看到 Visual Basic 消息框,该消息框解释运行时错误,文本为“对象不支持此方法属性。”You should see a Visual Basic message box that explains the run-time error with the text, "Object doesn't support this property of method." 虽然文本内容很清楚,但请选择 “调试” 来了解详细信息。Although that text is clear, choose Debug to find out more.

当您返回到 Visual Basic 编辑器时,编辑器处于特定调试模式,即使用黄色突出显示失败的代码行。When you return to the Visual Basic Editor, it is in a special debug mode that uses a yellow highlight to show you the line of code that failed. 与预期的一样,突出显示了包括 ValueX 属性的行。As expected, the line that includes the ValueX property is highlighted.

可以对运行中的 VBA 代码进行更改,以将 ValueX 改回 Value,然后选择 “调试” 菜单下的绿色小播放按钮。You can make changes to VBA code that is running, so change ValueX back to Value and choose the little green play button underneath the Debug menu. 程序应能够再次正常运行。The program should run normally again.

了解如何精心地使用调试器以获得更长、更复杂的程序是一个不错的主意。It is a good idea to learn how to use the debugger more deliberately for longer, more complex programs. 至少,了解如何设置断点以在想要查看代码时停止执行、代码运行时如何添加监视点以查看不同变量和属性的值、以及如何逐行查看代码时。At a minimum, learn a how to set break-points to stop execution at a point where you want to take a look at the code, how to add watches to see the values of different variables and properties as the code runs, and how to step through the code line by line. 这些选项在 “调试” 菜单中均有提供,认真的调试器用户通常会记住相应的键盘快捷方式。These options are all available in the Debug menu and serious debugger users typically memorize the accompanying keyboard shortcuts.

恰当使用参考材料Using reference materials well

若要打开内建于 Office 帮助的开发人员参考,请通过选择功能区中的问号或按 F1 打开任何 Office 应用程序中的帮助参考。To open the Developer Reference that is built into Office Help, open the Help reference from any Office application by choosing the question mark in the ribbon or by pressing F1. 然后,在 “搜索” 按钮右侧,选择下拉箭头以筛选内容。Then, to the right of the Search button, choose the dropdown arrow to filter the contents. 选择开发人员参考Choose Developer Reference. 如果在左面板中没有看到目录,请选择小书图标将其打开,然后从中展开对象模型参考。If you do not see the table of contents in the left panel, choose the little book icon to open it, and then expand the Object Model Reference from there.

图 5. 开发工具帮助上的筛选功能适用于所有 Office 应用程序Figure 5. Filtering on developer Help applies to all Office applications

开发工具帮助上的筛选功能适用于所有 Office 应用程序

浏览对象模型参考所花费的时间将得到回报。Time spent browsing the Object Model reference pays off. 在你了解要使用的 Office 应用程序的 VBA 语法和对象模型的基础知识后,你的技能会得到提升,你将从主观猜测转变为按方法系统化编程。After you understand the basics of VBA syntax and the object model for the Office application that you are working with, you advance from guesswork to methodical programming.

当然,Microsoft Office 开发人员中心是一个提供文章、技巧和社区信息的出色门户。Of course the Microsoft Office Developer Center is an excellent portal for articles, tips, and community information.

搜索论坛和群组Searching forums and groups

所有程序员都会时不时遇到困难,即使在阅读他们可以找到的每篇参考文章之后。他们晚上睡觉时会失眠,因为他们在思考解决问题的各种方法。All programmers get stuck sometimes, even after reading every reference article they can find and losing sleep at night thinking about different ways to solve a problem. 幸运的是,Internet 促进了互相帮助以解决编程问题的开发人员社区的发展。Fortunately, the Internet has fostered a community of developers who help each other solve programming problems.

在网络上搜索“office 开发人员论坛”时会显示几个讨论组。Any search on the Web for "office developer forum" reveals several discussion groups. 你也可以搜索“office 开发”或问题描述来查找论坛、博文以及文章。You can search on "office development" or a description of your problem to discover forums, blog posts, and articles as well.

如果你已为解决问题而竭尽全力,那么就不要害怕将你的问题张贴到开发人员论坛上。If you have done everything that you can to solve a problem, do not be afraid to post your question to a developers forum. 这些论坛欢迎来自较新程序员的帖子,并且许多有经验的开发人员都很乐于提供帮助。These forums welcome posts from newer programmers and many of the experienced developers are glad to help.

下面是在向开发人员论坛中张贴内容时需遵守的几点规定:The following are a few points of etiquette to follow when you post to a developer forum:

  • 在张贴之前,应在网站上查找 FAQ 或论坛成员希望你遵循的准则。Before you post, look on the site for an FAQ or for guidelines that members want you to follow. 确保所张贴的内容符合这些准则,并位于论坛的正确区域。Ensure that you post content that is consistent with those guidelines and in the correct section of the forum.

  • 包括清楚的完整代码示例,如果您的代码是一长段代码的一部分,请考虑编辑您的代码以向其他人进行阐述。Include a clear and complete code sample, and consider editing your code to clarify it for others if it is part of a longer section of code.

  • 清楚准确地描述你的问题,并概述你为解决此问题已经采取的所有步骤。Describe your problem clearly and concisely, and summarize any steps that you have taken to solve the problem. 花些时间尽力编写好的帖子,尤其在你慌乱或匆忙时。Take the time to write your post as well as you can, especially if you are flustered or in a hurry. 采用对首次阅读问题陈述的读者有意义的方式介绍情况。Present the situation in a way that will make sense to readers the first time that they read the problem statement.

  • 要有礼貌,并表达你的感激之情。Be polite and express your appreciation.

进一步了解编程Going further with programming

虽然本文很短且只谈到 VBA 和编程的表面内容,但希望它能够为你提供足够的入门信息。Although this article is short and only scratches the surface of VBA and programming, it is hopefully enough to get you started.

本节简单讨论一些较关键的主题。This section briefly discusses a few more key topics.


在本文提供的简单示例中,你处理的是应用程序已创建的对象。In the simple examples in this article you manipulated objects that the application had already created. 你可能希望创建自己的对象来存储值或对应用程序中临时使用的其他对象的引用。You might want to create your own objects to store values or references to other objects for temporary use in your application. 这些对象称为变量。These are called variables.

若要在 VBA 中使用变量,必须使用 Dim 语句告诉 VBA 变量所代表的对象类型。To use a variable in VBA, must tell VBA which type of object the variable represents by using the Dim statement. 然后,设置其值并用它来设置其他变量或属性。You then set its value and use it to set other variables or properties.

    Dim MyStringVariable As String
    MyStringVariable = "Wow!"
    Worksheets(1).Range("A1").Value = MyStringVariable

分支和循环Branching and looping

本文中的简单程序从上到下一次执行一行。The simple programs in this article execute one line at a time, from the top down. 编程的真正功能来自你必须根据指定的一个或多个条件确定要执行哪些代码行的选项。The real power in programming comes from the options that you have to determine which lines of code to execute, based on one or more conditions that you specify. 你可以进一步扩展这些功能,以便可以重复执行一项操作许多次。You can extend those capabilities even further when you can repeat an operation many times. 例如,下面的代码扩展了 Macro1。For example, the following code extends Macro1.

Sub Macro1()
    If Worksheets(1).Range("A1").Value = "Yes!" Then
        Dim i As Integer
        For i = 2 To 10
            Worksheets(1).Range("A" & i).Value = "OK! " & i
        Next i
        MsgBox "Put Yes! in cell A1"
    End If
End Sub

在 Visual Basic 编辑器中键入代码或粘贴代码,然后运行编辑器。Type or paste the code into the Visual Basic Editor and then run it. 按照出现的消息框中的说明操作,并将单元格 A1 中的文本从 Wow!Follow the directions in the message box that appears and change the text in cell A1 from Wow! 更改为 Yes!to Yes! 然后再次运行编辑器,以查看循环功能。and run it again to see the power of looping. 该代码段演示变量、分支和循环。This code snippet demonstrates variables, branching and looping. 执行期间仔细阅读代码,并尝试确定每行代码执行时会发生的操作。Read it carefully after you see it in action and try to determine what happens as each line executes.

我的所有 Office 应用程序:示例代码All of my Office applications: example code

下面是一些要尝试的脚本;每个脚本解决一个 Office 实际问题。Here are a few scripts to try; each solves a real-world Office problem.

在 Outlook 中创建电子邮件Create an email in Outlook

Sub MakeMessage()
    Dim OutlookMessage As Outlook.MailItem
    Set OutlookMessage = Application.CreateItem(olMailItem)
    OutlookMessage.Subject = "Hello World!"
    Set OutlookMessage = Nothing
End Sub

请注意,有时你可能希望在 Outlook 中自动创建电子邮件;你也可以使用模板。Be aware that there are situations in which you might want to automate email in Outlook; you can use templates as well.

删除 Excel 工作表中的空行Delete empty rows in an Excel worksheet

Sub DeleteEmptyRows()
    SelectedRange = Selection.Rows.Count
    ActiveCell.Offset(0, 0).Select
    For i = 1 To SelectedRange
        If ActiveCell.Value = "" Then
            ActiveCell.Offset(1, 0).Select
        End If
    Next i
End Sub

请注意,您可以选择一列单元格,然后运行此宏来删除所选列中具有空白单元格的所有行。Be aware that you can select a column of cells and run this macro to delete all rows in the selected column that have a blank cell.

删除 PowerPoint 中的空文本框Delete empty text boxes in PowerPoint

Sub RemoveEmptyTextBoxes()
    Dim SlideObj As Slide
    Dim ShapeObj As Shape
    Dim ShapeIndex As Integer
    For Each SlideObj In ActivePresentation.Slides
        For ShapeIndex = SlideObj.Shapes.Count To 1 Step -1
            Set ShapeObj = SlideObj.Shapes(ShapeIndex)
            If ShapeObj.Type = msoTextBox Then
                If Trim(ShapeObj.TextFrame.TextRange.Text) = "" Then
                End If
            End If
        Next ShapeIndex
    Next SlideObj
End Sub

请注意,此代码循环访问所有幻灯片并删除没有任何文本的所有文本框。Be aware that this code loops through all of the slides and deletes all text boxes that do not have any text. 计数变量递减而不是递增,因为代码每次删除一个对象时,都会从集合中移除该对象,从而减小了计数。 The count variable decrements instead of increments because each time the code deletes an object, it removes that object from the collection, which reduces the count.

将 Outlook 中的联系人复制到 WordCopy a contact from Outlook to Word

Sub CopyCurrentContact()
   Dim OutlookObj As Object
   Dim InspectorObj As Object
   Dim ItemObj As Object
   Set OutlookObj = CreateObject("Outlook.Application")
   Set InspectorObj = OutlookObj.ActiveInspector
   Set ItemObj = InspectorObj.CurrentItem
   Application.ActiveDocument.Range.InsertAfter (ItemObj.FullName & " from " & ItemObj.CompanyName)
End Sub

请注意,此代码将 Outlook 中当前打开的联系人复制到打开的 Word 文档中。Be aware that this code copies the currently open contact in Outlook into the open Word document. 仅当 Outlook 中包含当前打开以供检查的联系人时,此代码才运行。This code only works if there is a contact currently open for inspection in Outlook.

支持和反馈Support and feedback

有关于 Office VBA 或本文档的疑问或反馈?Have questions or feedback about Office VBA or this documentation? 请参阅 Office VBA 支持和反馈,获取有关如何接收支持和提供反馈的指南。Please see Office VBA support and feedback for guidance about the ways you can receive support and provide feedback.