Excel VBA教程–如何使用Visual Basic在电子表格中编写代码

介绍 (Introduction)

This is a tutorial about writing code in Excel spreadsheets using Visual Basic for Applications (VBA).

这是一个有关使用Visual Basic for Applications(VBA)在Excel电子表格中编写代码的教程。

Excel is one of Microsoft’s most popular products. In 2016, the CEO of Microsoft said  "Think about a world without Excel. That's just impossible for me.” Well, maybe the world can’t think without Excel.

Excel是Microsoft最受欢迎的产品之一。 2016年，微软首席执行官说：“想想没有Excel的世界。对我而言，这简直是不可能的。” 好吧，也许没有Excel，世界将无法思考。

• In 1996, there were over 30 million users of Microsoft Excel (source).

  在1996年，有超过3000万的Microsoft Excel用户( 来源 )。

• Today, there are an estimated 750 million users of Microsoft Excel. That’s a little more than the population of Europe and 25x more users than there were in 1996.
  如今，估计有7.5亿Microsoft Excel用户。 这比欧洲人口多一点，用户比1996年多25倍。

We’re one big happy family!

我们是一个幸福的大家庭！

In this tutorial, you’ll learn about VBA and how to write code in an Excel spreadsheet using Visual Basic.

在本教程中，您将了解VBA以及如何使用Visual Basic在Excel电子表格中编写代码。

先决条件 (Prerequisites)

You don’t need any prior programming experience to understand this tutorial. However, you will need:

您不需要任何编程经验即可了解本教程。 但是，您将需要：

• Basic to intermediate familiarity with Microsoft Excel
  基本到中等熟悉Microsoft Excel
• If you want to follow along with the VBA examples in this article, you will need access to Microsoft Excel, preferably the latest version (2019) but Excel 2016 and Excel 2013 will work just fine.
  如果要遵循本文中的VBA示例，则需要访问Microsoft Excel，最好是最新版本(2019)，但Excel 2016和Excel 2013可以正常工作。
• A willingness to try new things
  愿意尝试新事物

学习目标 (Learning Objectives)

Over the course of this article, you will learn:

在本文中，您将学到：

1. What VBA is
   什么是VBA
2. Why you would use VBA
   为什么要使用VBA
3. How to get set up in Excel to write VBA
   如何在Excel中进行设置以编写VBA
4. How to solve some real-world problems with VBA
   如何使用VBA解决一些实际问题

重要概念 (Important Concepts)

Here are some important concepts that you should be familiar with to fully understand this tutorial.

这里是一些重要概念，您应该熟悉这些概念才能完全理解本教程。

Objects: Excel is object-oriented, which means everything is an object - the Excel window, the workbook, a sheet, a chart, a cell. VBA allows users to manipulate and perform actions with objects in Excel.

对象 ：Excel是面向对象的，这意味着一切都是对象-Excel窗口，工作簿，工作表，图表，单元格。 VBA允许用户对Excel中的对象进行操作和执行操作。

If you don’t have any experience with object-oriented programming and this is a brand new concept, take a second to let that sink in!

如果您没有任何面向对象编程的经验，而这是一个全新的概念，请花一点时间让它沉入其中！

Procedures: a procedure is a chunk of VBA code, written in the Visual Basic Editor, that accomplishes a task. Sometimes, this is also referred to as a macro (more on macros below). There are two types of procedures:

过程 ：过程是用Visual Basic编辑器编写的，完成任务的VBA代码的一部分。 有时，这也称为宏(下面的更多宏)。 有两种类型的过程：

• Subroutines: a group of VBA statements that performs one or more actions
  子例程：一组执行一个或多个动作的VBA语句

• Functions: a group of VBA statements that performs one or more actions and returns one or more values

  函数：一组执行一个或多个动作并返回一个或多个值的VBA语句

Note: you can have functions operating inside of subroutines. You’ll see later.

注意：可以在子例程内部运行函数。 待会见。

Macros: If you’ve spent any time learning more advanced Excel functionality, you’ve probably encountered the concept of a “macro.” Excel users can record macros, consisting of user commands/keystrokes/clicks, and play them back at lightning speed to accomplish repetitive tasks. Recorded macros generate VBA code, which you can then examine. It’s actually quite fun to record a simple macro and then look at the VBA code.

宏 ：如果您花了很多时间学习更高级的Excel功能，则可能遇到了“宏”的概念。 Excel用户可以记录由用户命令/按键/单击组成的宏，并以闪电般的速度播放它们以完成重复的任务。 录制的宏会生成VBA代码，然后您可以对其进行检查。 录制一个简单的宏，然后查看VBA代码，实际上非常有趣。

Please keep in mind that sometimes it may be easier and faster to record a macro rather than hand-code a VBA procedure.

请记住，有时录制宏可能比手动编码VBA过程更容易，更快。

For example, maybe you work in project management. Once a week, you have to turn a raw exported report from your project management system into a beautifully formatted, clean report for leadership. You need to format the names of the over-budget projects in bold red text. You could record the formatting changes as a macro and run that whenever you need to make the change.

例如，也许您从事项目管理。 每周一次，您必须将来自项目管理系统的原始导出报告转换为格式精美，干净的报告，以进行领导。 您需要将预算外项目的名称设置为红色粗体。 您可以将格式更改记录为宏，并在需要进行更改时运行。