Microsoft Excel使用户能够使用宏和Visual Basic for Applications ( VBA ) 脚本自动执行功能和命令。VBA 是 Excel用来创建宏的编程语言。^{(VBA is the programming language Excel)}它还将根据特定条件执行自动命令。

宏是一系列预先录制的命令。当给出特定命令时，它们会自动运行。如果您在Microsoft Excel中有重复执行的任务，例如会计、项目管理或工资单，自动化这些流程可以节省大量时间。

在Excel功能区^(Ribbon)的开发人员^(Developer)选项卡下，用户可以记录鼠标点击和击键（宏）。但是，某些功能需要比宏提供的更深入的脚本。这就是VBA脚本的巨大优势所在。它允许用户创建更复杂的脚本。

在本文中，我们将解释以下内容：

• 启用脚本和宏
• 如何在 Excel 中创建宏
• 宏的具体示例
• 了解有关 VBA 的更多信息
• 创建一个按钮^(Button)以开始使用VBA
• 添加代码^{(Add Code)}以赋予按钮功能^{(Button Functionality)}
• 它有效吗？

启用脚本和宏^{(Enabling Scripts & Macros)}

在Excel^(Excel)中创建宏或VBA脚本之前，您必须启用功能区^(Ribbon)菜单上的开发人员^{(Developer )}选项卡。默认情况下不启用开发人员选项卡。^(Developer)要启用它：

• 打开 Excel 工作表。
• 单击文件^{(File )}>选项^{( Options )}>自定义功能区。^{( Customize Ribbon.)}

• 在Developer^(Developer)旁边的框中打勾。

• 单击功能区^(Ribbon)菜单中的开发人员^(Developer)选项卡。

• 接下来，单击宏安全^{(Macro Security)}并勾选启用所有宏旁边的框（不推荐；可能运行有潜在危险的代码）。 ^{(Enable all macros (not recommended; potentially dangerous code can run). )}
• 然后单击确定。^{( OK.)}

宏默认情况下未打开并带有警告的原因是它们是可能包含恶意软件的计算机代码。

^(Make)如果您在Excel和其他Microsoft程序中处理共享项目，请确保文档来自受信任的来源。

使用完脚本和宏后，请禁用所有宏以防止潜在的恶意代码感染其他文档。

在 Excel 中创建宏^{(Create a Macro in Excel)}

您在录制宏时 在Excel中执行的所有操作都会添加到其中。

• 在 Developer 选项卡中，单击Record Macro。

• 输入宏名称^{(Macro name)}、快捷键^{(Shortcut key)}和说明。^{(Description. )}宏^(Macro)名称必须以字母开头，并且不能有任何空格。快捷键必须是字母。

从以下选项中确定要存储宏的位置：

• 个人宏工作簿^{(Personal Macro Workbook)}：这将创建一个隐藏的Excel文档，其中存储的宏可用于任何Excel文档。
• 新工作簿^{(New Workbook)}：将创建一个新的Excel文档来存储创建的宏。
• 此工作簿^{(This Workbook)}：这将仅应用于您当前正在编辑的文档。

完成后，单击OK。 

• 运行^(Run)您想要自动化的操作。完成后，单击停止录制^{(Stop Recording)}。 
• 当您想要访问您的宏时，请使用您为其提供的键盘快捷键。

宏的具体示例^{(Specific Example Of a Macro)}

让我们从一个简单的客户电子表格开始，以及他们欠多少钱。我们将首先创建一个宏来格式化工作表。

假设您决定所有电子表格都应使用不同的格式，例如将名字和姓氏放在不同的列中。 

您可以手动更改此设置。或者，您可以使用宏创建程序来自动为您正确格式化。

录制宏^{(Record The Macro)}

• 点击录制宏^{(Record Macro)}。我们将其命名为Format_Customer_Data并单击OK。 
• 为了获得我们想要的格式，我们将第一列名称更改为First Name。 
• 然后在 A 旁边插入一列并将其命名为Last Name。 
• 突出显示^(Highlight)第一列中的所有名称（仍包括名字和姓氏），然后单击功能区导航中的数据^(Data)。
• 单击文本到列^{(Text to Columns)}。

• 勾选分隔^(Delimited)>下一步^{(Next )}>按空格^{(Separate by Space)}分隔>下一步^{(Next )}>完成^(Finish)。请参阅下面的屏幕截图以及上面的过程如何分隔名字和姓氏。

• 要格式化到期余额^{(Balance Due)}字段，请突出显示金额。单击^(Click)主页^{(Home )}>条件格式^{(Conditional Formatting)}>突出显示单元格规则^{(Highlight Cell Rules)}>大于^{(Greater Than )}> 0。

这将突出显示有余额的单元格。由于进一步说明格式，我们添加了一些没有余额的客户。  

• 返回到Developer并单击Stop Recording。

应用宏^{(Apply The Macro)}

在我们记录宏以正确格式化之前，让我们从原始电子表格开始。单击^(Click)Macros，选择并运行^(Run)您刚刚创建的宏。

当你运行一个宏时，所有的格式都会为你完成。我们刚刚创建的这个宏存储在Visual Basic 编辑器^{(Visual Basic Editor)}中。

用户可以通过几种不同的方式运行宏。阅读运行宏^{(Run a macro)}以了解更多信息。  

了解有关 VBA 的更多信息^{(Learn More About VBA)}

要了解 VBA，请单击“开发人员”选项卡中的“^(Developer)宏^(Macro)” 。找到您创建的一个并单击编辑。^(Edit.)

您在上面的框中看到的代码是您在录制宏时创建的。 

当您想以相同方式格式化其他客户付款电子表格时，这也是您将运行的内容。

创建一个按钮以开始使用 VBA^{(Create a Button To Get Started With VBA)}

使用上面与客户相同的电子表格以及他们欠多少钱，让我们创建一个货币转换器。

• 要插入按钮元素，请导航到开发人员^{(Developer )}选项卡。 
• 从控件^(Controls)部分中插入^(Insert)旁边的下拉列表中选择ActiveX命令按钮^{(ActiveX Command Button )}。

• 将按钮拖动^(Drag)到电子表格上的任意位置，以便您可以轻松访问它并在以后根据需要进行更改。

• 要附加代码，请右键单击该按钮并选择Properties。我们将保持名称^(Name)作为命令按钮^{(CommandButton)}和要转换的^{( Convert )}标题^{(Caption )}（这是按钮文本）。

添加代码以赋予按钮功能^{(Add Code To Give The Button Functionality)}

VBA编码不会发生在Excel界面中。它是在一个单独的环境中完成的。  

• 转到开发人员^(Developer)选项卡并确保设计模式^{(Design Mode)}处于活动状态。

• 要访问我们刚刚创建的按钮的代码，请右键单击它并选择View Code。

• 查看下面屏幕截图中的代码，请注意代码的开头（Private Sub）和结尾（End Sub）已经存在。

• 下面的代码将驱动货币转换过程。

ActiveCell.Value = (ActiveCell * 1.28)

我们在本节中的目的是转换电子表格中的货币。上面的脚本反映了从GBP^(GBP)到USD的汇率。单元格的新值将是当前的值乘以 1.28。

下面的屏幕截图显示了代码在插入后在VBA窗口中的外观。

• 转到顶部导航中的文件，^{(File )}然后单击关闭并返回 Microsoft Excel^{(click on Close and Return to Microsoft Excel)}以返回Excel主界面。

它有效吗？^{(Did It Work?)}

在测试代​​码之前，您必须首先禁用设计模式^{(Design Mode)}（单击它）以避免进一步修改并提供按钮功能。

• 在电子表格中输入^(Type)任意数字，然后单击转换^(Convert)按钮。如果你的数字值增加了大约四分之一，它就起作用了。

在本例中，我将数字 4 放入一个单元格中。点击Convert后，数字变为 5.12。由于 1.28 的 4 倍是 5.12，因此代码执行正确。

现在您已了解如何在Excel中创建宏或脚本，您可以使用它们在^(Excel)Excel中自动执行大量操作。

How To Create a VBA Macro Or Script In Excel

Mіcrosoft Excеl enаbles users to automate features and сommands υsing macros and Visual Basic for Applications (VBA) scripting. VBA is the programming language Excel uses to create macros. It will also execute automated commands based on specific conditions.

Macros are a series of pre-recorded commands. They run automatically when a specific command is given. If you have tasks in Microsoft Excel that you repeatedly do, such as accounting, project management, or payroll, automating these processes can save a lot of time.

Under the Developer tab on the Ribbon in Excel, users can record mouse clicks and keystrokes (macros).  However, some functions require more in-depth scripting than macros can provide. This is where VBA scripting becomes a huge benefit. It allows users to create more complex scripts.

In this article, we will explain the following:

• Enabling Scripts & Macros
• How to Create a Macro in Excel
• Specific Example of a Macro
• Learn More About VBA
• Create a Button to Get Started with VBA
• Add Code to Give the Button Functionality
• Did it Work?

Enabling Scripts & Macros

Before you can create macros or VBA scripts in Excel, you must enable the Developer tab on the Ribbon menu. The Developer tab is not enabled by default. To enable it:

• Open an Excel worksheet.
• Click on File > Options > Customize Ribbon.

• Put a tick in the box next to Developer.

• Click on the Developer tab from the Ribbon menu.

• Next, click on Macro Security and tick the box next to Enable all macros (not recommended; potentially dangerous code can run). 
• Then click OK.

The reason macros are not turned on by default and come with a warning is that they are computer code that could contain malware.

Make sure the document is from a trusted source if you are working on a shared project in Excel and other Microsoft programs.

When you are done using your scripts and macros, disable all macros to prevent potentially malicious code from infecting other documents.

Create a Macro in Excel

All the actions you take in Excel while recording a macro are added to it. 

• From the Developer tab, click on Record Macro.

• Enter a Macro name, a Shortcut key, and a Description. Macro names must begin with a letter and can’t have any spaces. The shortcut key must be a letter.

Decide where you want to store the macro from the following options:

• Personal Macro Workbook: This will create a hidden Excel document with stored macros to be used with any Excel documents.
• New Workbook: Will create a new Excel document to store the created macros.
• This Workbook: This will only be applied to the document you are currently editing.

When done, click OK. 

• Run through the actions you want to automate. When you are finished, click Stop Recording. 
• When you want to access your macro, use the keyboard shortcut you gave it.

Specific Example Of a Macro

Let’s start with a simple spreadsheet for customers and how much they owe. We will begin by creating a macro to format the worksheet.

Let’s assume that you decide that all spreadsheets should use a different format such as putting first and last name in separate columns. 

You can manually change this. Or you can create a program using a macro to automatically format it correctly for you.

Record The Macro

• Click on Record Macro. Let’s call it Format_Customer_Data and click OK. 
• To get the formatting we want, we will change the first column name to First Name. 
• Then insert a column next to A and call it Last Name. 
• Highlight all the names in the first column (which still include first and last name), and click on Data from the ribbon navigation.
• Click on Text to Columns.

• Tick Delimited > Next > Separate by Space > Next > Finish. See screenshot below and how the first and last names were separated by the process above.

• To format the Balance Due field, highlight the amounts. Click on Home > Conditional Formatting > Highlight Cell Rules > Greater Than > 0.

This will highlight the cells that have a balance due. We added a few customers with no balance due to further illustrate the formatting.  

• Go back to Developer and click Stop Recording.

Apply The Macro

Let’s start with the original spreadsheet before we recorded the macro to format it correctly. Click on Macros, select and Run the macro you just created.

When you run a macro, all the formatting is done for you. This macro we just created is stored in the Visual Basic Editor.

Users can run macros in several different ways. Read Run a macro to learn more.  

Learn More About VBA

To learn about VBA, click on Macro from the Developer tab. Find one you have created and click Edit.

The code you see in the box above is what was created when you recorded your macro. 

It is also what you will run when you want to format other customer payment spreadsheets in the same way.

Create a Button To Get Started With VBA

Using the same spreadsheet above with customers and how much they owe, let’s create a currency converter.

• To insert a button element, navigate to the Developer tab. 
• Select ActiveX Command Button from the dropdown next to Insert in the Controls section.

• Drag the button anywhere on the spreadsheet so you can easily access it and change it later if you want.

• To attach the code, right-click on the button and select Properties. We will keep the Name as CommandButton and the Caption to Convert (this is the button text).

Add Code To Give The Button Functionality

VBA coding doesn’t take place in the Excel interface. It is done in a separate environment.  

• Go to the Developer tab and make sure Design Mode is active.

• To access the code of the button we just created, right-click on it and select View Code.

• Looking at the code in the screenshot below, notice the beginning (Private Sub) and end (End Sub) of the code is already there.

• The code below will drive the currency conversion procedure.

ActiveCell.Value = (ActiveCell * 1.28)

Our purpose in this section is to convert the currency in our spreadsheet. The script above reflects the exchange rate from GBP to USD. The new value of a cell will be what is currently there multiplied by 1.28.

The screenshot below shows you how the code looks in the VBA window after you insert it .

• Go to File in the top navigation and click on Close and Return to Microsoft Excel to return to the main Excel interface.

Did It Work?

Before you can test your code, you must first disable Design Mode (click on it) to avoid further modifications and give the button functionality.

• Type any number into your spreadsheet and then click the Convert button. If the value of your number increases by approximately one-quarter, it worked.

For this example, I put the number 4 into a cell. After clicking Convert, the number changed to 5.12. Since 4 times 1.28 is 5.12, the code was carried out correctly.

Now that you understand how to create a macro or script in Excel, you can use them to automate a multitude of actions in Excel.

Related posts

• MS Excel 的高级 VBA 指南

• 您需要知道的 5 个 Google 表格脚本功能

• Excel中如何删除空行

• 如何使用 Excel 的朗读单元格功能

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

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

• Excel中如何按日期排序

• 如何在 Excel 中制作饼图

• 如何使用 Excel 的假设分析

• 如何在 Excel 中编写 IF 公式/语句

• 将 Excel 连接到 MySQL

• 什么是 Excel 中的 VBA 数组以及如何编程

• 如何在 Excel 中使用 PMT 函数

• Excel 电子表格中的基本一列和多列数据排序

• 如何在 Excel 中合并单元格、列和行

• 在 Excel 中使用键盘更改行高和列宽

• 如何共享 Excel 文件以方便协作

• 将 Excel 转换为 Google 表格的 4 种方法

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

• 在 Excel 中将您的工作表数据居中以进行打印