在几乎所有Microsoft Office产品中运行的VBA 编程^{(VBA programming)}平台是任何人都可以用来增强他们对这些产品的使用的最强大的工具之一。

本VBA初学者指南将向您展示如何将开发人员^(Developer)菜单添加到您的Office应用程序，如何进入VBA编辑器窗口，以及基本VBA语句和循环如何工作，以便您可以开始在Excel、Word、Powerpoint中使用^(Powerpoint)VBA，Outlook和OneNote。

本VBA指南使用最新版本的Microsoft Office产品。如果您有早期版本，您可能会看到与屏幕截图略有不同。

如何启用和使用 VBA 编辑器^{(How To Enable & Use The VBA Editor)}

在本指南中使用的任何Office产品中，您可能会注意到您没有引用的开发人员^(Developer)菜单。开发人员菜单仅在Excel、Word、Outlook和Powerpoint中可用。OneNote不提供从应用程序内部编辑VBA代码的工具，但您仍然可以引用OneNote API以与其他Office程序中 的OneNote交互。^(OneNote)

您将在我们即将发布的高级 VBA^{(Advanced VBA)}指南中了解如何执行此操作。

• 要在任何办公产品中启用开发人员菜单，请选择^(Developer)文件^(File)菜单，然后从左侧导航菜单中 选择选项。^(Options)
• 您将看到一个选项^(Options)菜单弹出窗口。从左侧导航菜单中选择自定义功能区。^{(Customize Ribbon)}

左侧列表包括该Office^(Office)应用程序中可用的所有可用菜单和菜单命令。右侧的列表是当前可用或激活的列表。

• 您应该在右侧列表中看到Developer，但它不会被激活。只需^(Just)选中复选框即可激活开发人员^(Developer)菜单。

• 如果您在右侧看不到Developer可用，则将左侧的^(Developer)Choose commands从下拉菜单更改为All Commands。从列表中找到Developer并在中心选择^(Developer)Add>>以将该菜单添加到Ribbon。
• 完成后选择确定^(OK)。
• 开发者^(Developer)菜单激活后，您可以返回主应用程序窗口并从顶部菜单中 选择开发者。^(Developer)
• 然后从功能区的控件组中选择^(Controls)查看代码^{(View Code)}以打开VBA编辑器窗口。

• 这将打开VBA编辑器窗口，您可以在其中键入您将在接下来的几节中学习的代码。

• 尝试将开发人员^(Developer)菜单添加到您每天使用的一些Office应用程序中。^(Office)一旦您可以轻松打开VBA编辑器窗口，请继续阅读本指南的下一部分。

初学者的通用 VBA 编程技巧^{(General VBA Programming Tips for Beginners)}

您会注意到，当VBA编辑器打开时，左侧面板中的导航选项看起来与一个Office应用程序不同。

这是因为可以放置VBA代码的可用对象取决于应用程序中有哪些对象。例如，在Excel 中^(Excel)，您可以将VBA代码添加到工作簿或工作表对象。在Word中，您可以将VBA代码添加到文档中。在Powerpoint中，仅适用于模块。

所以，不要对不同的菜单感到惊讶。VBA代码的结构和语法在所有应用程序中都是相同的。唯一的区别是您可以引用的对象以及您可以通过VBA代码对这些对象执行的操作。

在我们深入研究可以通过VBA代码对它们执行的不同对象和操作之前，让我们先看看在编写VBA代码时可以使用的最常见的VBA结构和语法。^(VBA)

在哪里放置 VBA 代码^{(Where To Put VBA Code)}

当您在VBA编辑器中时，您需要使用编辑窗口顶部的两个下拉框来选择要将代码附加到哪个对象以及何时运行代码。

例如，在Excel 中^(Excel)，如果您选择Worksheet和Activate，代码将在工作表打开时运行。 

可用于触发VBA代码的其他工作表操作包括工作表更改时间、关闭（停用）时间、工作表计算运行时间等。

当您在编辑器中添加VBA代码时，请始终确保将您的VBA代码放在对象上并使用您想要用来触发该代码的正确操作。

VBA IF 语句^{(VBA IF Statements)}

IF 语句在VBA中的工作方式与在任何其他编程语言中的工作方式一样。

IF 语句的第一部分查看一个条件或一组条件是否为真。这些条件可以通过AND或 OR 运算符连接起来以将它们链接在一起。

一个例子是检查电子表格中的成绩是否高于或低于“及格”成绩，并将通过或失败状态分配给另一个单元格。

If Cells(2, 2) > 75 Then Cells(2, 3) = “Pass” Else Cells(2, 3) = “Fail”

如果您不想将整个语句放在一行中，可以通过在行尾添加“_”符号将其拆分为多行。

If Cells(2, 2) > 75 Then _
Cells(2, 3) = “Pass”  Else _
Cells(2, 3) = “Fail”

使用这种技术通常可以使代码更易于阅读和调试。

下一个循环的 VBA^{(VBA For Next Loops)}

IF 语句非常适合单个比较，例如上面查看单个单元格的示例。但是，如果您想遍历整个单元格范围并在每个单元格上执行相同的 IF 语句怎么办？

在这种情况下，您需要一个FOR循环。 

为此，您需要使用范围的长度，并按包含数据的行数循环该长度。

为此，您需要定义范围和单元格变量，并循环访问它们。您还需要定义一个计数器，以便将结果输出到相应的行。所以你的VBA代码首先会有这一行。

Dim rng As Range，单元格 As Range ^{(Dim rng As Range, cell As Range)}
Dim rowCounter as Integer

定义范围大小如下。 

Set rng = Range(“B2:B7”)
rowCounter = 2

最后，您可以创建FOR循环以逐步遍历该范围内的每个单元格并进行比较。

For Each cell In rng
   If cell.Value > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"

   rowCounter = rowCounter + 1

Next cell

运行此VBA 脚本^{(VBA script)}后，您会在实际电子表格中看到结果。

VBA While 循环^{(VBA While Loops)}

While循环^(Loop)也循环一系列语句，就像FOR循环一样，但循环继续的条件是保持为真的条件。

例如，您可以编写与上述相同的FOR循环，作为WHILE循环，只需使用 rowCounter 变量，如下所示。

While rowCounter < rng.Count + 2
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   rowCounter = rowCounter + 1
Wend

注意：需要rng.Count + 2终止限制，因为行计数器从 2 开始，需要在数据结束的第 7 行结束。但是，范围 (B2:B7) 的计数只有 6，只有当计数器大于计数器时，While 循环才会结束——因此最后一个 rowCounter 值需要为 8（或 rng.Count + 2）。^(GREATER)

您还可以按如下方式设置 While 循环：

While rowCounter <= rng.Count + 1

您只能将范围计数 (6) 增加 1，因为一旦 rowCounter 变量到达数据的末尾（第 7 行），循环就可以结束。

VBA Do While 和 Do until 循环^{(VBA Do While and Do Until Loops)}

Do While 和 Do Until 循环与 While 循环几乎相同，但工作方式略有不同。

• While 循环在循环^{(While Loop)}开始时检查条件是否为真。
• Do-While 循环^{(Do-While Loop)}在执行循环中的语句后检查条件是否为真。
• Do-Until 循环^{(Do-Until Loop)}在执行循环后检查条件是否仍然为假。

在这种情况下，您可以将上面的 While 循环重写为 Do-While 循环，如下所示。

Do
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   
   rowCounter = rowCounter + 1
Loop While rowCounter < rng.Count + 2

在这种情况下，逻辑不会发生太大变化，但是如果您想确保在所有语句运行后进行逻辑比较（无论如何都允许它们至少运行一次），那么 Do-While或 Do-Until 循环是正确的选择。

VBA 选择案例语句^{(VBA Select Case Statements)}

要开始构建VBA^(VBA)代码，您需要了解的最后一种逻辑语句是Select Case语句。

鉴于上面的示例，假设您想要一个不只是通过失败的评分方法。相反，您想要分配从 A 到 F 的字母等级。

您可以使用以下Select Case语句执行此操作：

For Each cell In rng
   
   Select Case cell

   Case 95 To 100
      Cells(rowCounter, 3) = "A"
   Case 85 To 94
      Cells(rowCounter, 3) = "B"
   Case 75 To 84
      Cells(rowCounter, 3) = "C"
   Case 65 To 74
      Cells(rowCounter, 3) = "D"
   Case 0 To 64
      Cells(rowCounter, 3) = "F"
   End Select

   rowCounter = rowCounter + 1

Next cell

此VBA脚本运行后生成的电子表格如下所示。

现在，您了解了在Microsoft Office应用程序中开始使用^{(Microsoft Office)}VBA所需了解的一切。

The Best VBA Guide (For Beginners) You’ll Ever Need

The VBA programming platform that runs throughout nearly all Microsoft Office products is one of the most powerful tools anyone can use to enhance their use of those products.

This VBA guide for beginners will show you how to add the Developer menu to your Office application, how to enter the VBA editor window, and how basic VBA statements and loops work so that you can get started using VBA in Excel, Word, Powerpoint, Outlook, and OneNote.

This VBA guide uses the latest version of Microsoft Office products. If you have an earlier version you may see some slight differences from the screenshots.

How To Enable & Use The VBA Editor

In any of the Office products used in this guide, you may notice that you don’t have the referenced Developer menu. The developer menu is only available in Excel, Word, Outlook, and Powerpoint. OneNote doesn’t offer a tool to edit VBA code from inside the application, but you can still reference the OneNote API to interact with OneNote from other Office programs. 

You’ll learn how to do this in our upcoming Advanced VBA guide.

• To enable the Developer menu in any office product, select the File menu, and select Options from the left navigation menu. 
• You’ll see an Options menu pop-up. Select Customize Ribbon from the left navigation menu.

The left list includes all available menus and menu commands available in that Office application. The list on the right are those that are currently available or activated.

• You should see Developer in the list on the right, but it will not be activated. Just select the checkbox to activate the Developer menu.

• If you don’t see Developer available on the right, then change the left Choose commands from dropdown to All Commands. Find Developer from the list and select Add>> in the center to add that menu to the Ribbon.
• Select OK when you’re done.
• Once the Developer menu is active, you can go back to your main application window and select Developer from the top menu. 
• Then select View Code from the Controls group in the ribbon to open the VBA editor window.

• This will open the VBA editor window where you can type the code you’ll learn in the next few sections.

• Try adding the Developer menu to a few of the Office applications you use every day. Once you’re comfortable opening the VBA editor window, continue on to the next section of this guide.

General VBA Programming Tips for Beginners

You’ll notice when the VBA editor opens, the navigation options in the left panel look different from one Office application to the other.

This is because the available objects where you can place VBA code depend on what objects there are in the application. For example, in Excel, you can add VBA code to workbook or sheet objects. In Word, you can add VBA code to documents. In Powerpoint, only to modules.

So, don’t be surprised by the different menus. The structure and syntax of the VBA code is the same across all applications. The only difference are the objects you can reference and the actions you can take on those objects through the VBA code.

Before we dive into the different objects and actions you can take on them via VBA code, let’s first look at the most common VBA structure and syntax you can use when you write VBA code.

Where To Put VBA Code

When you’re in the VBA editor, you need to use the two dropdown boxes at the top of the editing window to choose which object you want to attach the code to, and when you want the code to run.

For example, in Excel, if you choose Worksheet and Activate, the code will run whenever the worksheet is opened. 

Other worksheet actions you can use to trigger your VBA code include when the worksheet changes, when it’s closed (deactivated), when the worksheet calculation is run, and more.

When you add VBA code in the editor, always make sure to place your VBA code on the object and using the correct action that you want to use to trigger that code.

VBA IF Statements

An IF statement works in VBA just like it works in any other programming language.

The first part of the IF statement looks at whether a condition or set of conditions is true. These conditions can be joined by an AND or OR operator to link them together.

One example would be to check if a grade in a spreadsheet is above or below a “passing” grade, and assigning the pass or fail status to another cell.

If Cells(2, 2) > 75 Then Cells(2, 3) = “Pass” Else Cells(2, 3) = “Fail”

If you don’t want the entire statement on a single line, you can split it into multiple lines by adding a “_” symbol at the end of the lines.

If Cells(2, 2) > 75 Then _
Cells(2, 3) = “Pass”  Else _
Cells(2, 3) = “Fail”

Using this technique can often make code much easier to read and debug.

VBA For Next Loops

IF statements are great for single comparisons, like the example above of looking at a single cell. But what if you want to loop through an entire range of cells and do the same IF statement on each?

In this case you’d need a FOR loop. 

To do this, you’d need to use the length of a range, and loop through that length by the number of rows that contain data.

To do this, you need to define the range and cell variables, and loop through them. You’ll also need to define a counter so you can output the results to the appropriate row. So your VBA code would first have this line.

Dim rng As Range, cell As Range
Dim rowCounter as Integer

Define the range size as follows. 

Set rng = Range(“B2:B7”)
rowCounter = 2

Finally, you can create your FOR loop to step through every cell in that range and do the comparison.

For Each cell In rng
   If cell.Value > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"

   rowCounter = rowCounter + 1

Next cell

Once this VBA script is run, you see the results in the actual spreadsheet.

VBA While Loops

A While Loop also loops through a series of statements, just like the FOR loop, but the condition of the looping to continue is a condition remaining true.

For example, you could write the same FOR loop above, as a WHILE loop, by simply using the rowCounter variable as follows.

While rowCounter < rng.Count + 2
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   rowCounter = rowCounter + 1
Wend

Note: the rng.Count + 2 terminating limit is required because the row counter starts at 2 and needs to end on row 7 where the data ends. However, the count of the range (B2:B7) is only 6, and the While loop will only end once the counter is GREATER than the counter – so the last rowCounter value needs to be 8 (or rng.Count + 2).

You could also set up the While loop as follows:

While rowCounter <= rng.Count + 1

You can only increment the range count (6) by 1, because once the rowCounter variable reaches the end of the data (row 7), the loop can finish.

VBA Do While and Do Until Loops

Do While and Do Until loops are nearly identical to While loops, but work slightly different.

• The While Loop checks whether a condition is true at the beginning of the loop.
• The Do-While Loop checks whether a condition is true after executing the statements in the loop.
• The Do-Until Loop checks whether a condition is still false after executing the loop.

In this case you would rewrite the While loop above as follows, as a Do-While loop.

Do
   If Cells(rowCounter, 2) > 75 Then _
   Cells(rowCounter, 3) = "Pass" Else _
   Cells(rowCounter, 3) = "Fail"
   
   rowCounter = rowCounter + 1
Loop While rowCounter < rng.Count + 2

In this case the logic doesn’t change very much, but if you want to make sure the logic comparison takes place after all of the statements are run (allowing them all to run no matter what at least once), then a Do-While or Do-Until loop is the right option.

VBA Select Case Statements

The final type of logical statement you’ll need to understand to start structuring your VBA code are Select Case statements.

Given the example above, let’s say you want to have a grading method that isn’t just pass fail. Instead, you want to assign a letter grade from A through F.

You could do this with the following Select Case statement:

For Each cell In rng
   
   Select Case cell

   Case 95 To 100
      Cells(rowCounter, 3) = "A"
   Case 85 To 94
      Cells(rowCounter, 3) = "B"
   Case 75 To 84
      Cells(rowCounter, 3) = "C"
   Case 65 To 74
      Cells(rowCounter, 3) = "D"
   Case 0 To 64
      Cells(rowCounter, 3) = "F"
   End Select

   rowCounter = rowCounter + 1

Next cell

The resulting spreadsheet after this VBA script runs looks like the one below.

Now you know everything you need to know to start using VBA in your Microsoft Office applications.

Related posts

• MS Excel 的高级 VBA 指南

• Microsoft Teams 的 13 个最佳键盘快捷键

• 如何在 Excel 中创建 VBA 宏或脚本

• 什么是 Microsoft Publisher？初学者指南

• 如何修复Word中的书签未定义错误

• 如何修复 Outlook 卡在加载配置文件上

• 如何在 Microsoft Office 中创建 PDF 文档

• 在 Word 和 Excel 中快速拼出数字

• 如何在 Excel 中修复一行

• 如何在 Word 中创建和运行宏

• 如何在 Excel 中使用 Flash 填充

• 让 OpenOffice Writer 的外观和功能更像 Microsoft Word

• 打开 DOCX 文件时修复结束标记开始标记不匹配错误

• 如何修复 VLOOKUP 等 Excel 公式中的#N/A 错误

• 如何在 Word 中添加脚注

• 为 Microsoft Office 创建或生成键盘快捷键

• 如何在 PowerPoint 演示文稿中添加幻灯片编号

• 使用 Excel 的假设分析目标搜索工具

• 10 个恢复丢失或忘记的 Outlook PST 密码的工具

• 如何将 Excel 工作表插入 Word Doc