标签：语句 精通 Sub 代码 Excel VBA Value ChatGPT

原文：Mastering Advanced Excel - With ChatGPT Integration

译者：飞龙

协议：CC BY-NC-SA 4.0

第十五章将按钮分配给宏

介绍

在本章中，我们将探讨在 Excel 中将按钮分配给宏的过程。通过在快速访问工具栏和 Excel 工作表上创建按钮，我们可以自动化任务，提高效率并简化��杂操作。加入我们，深入探索 Excel 自动化的世界，并发现按钮-宏集成的力量。

结构

在本章中，我们将讨论以下主题：

• 在快速访问工具栏上创建按钮

• 修改菜单或按钮

• 在 Excel 工作表上创建按钮

• 编辑记录的宏

目标

通过本章结束时，读者将了解如何在快速访问工具栏上创建按钮以快速访问宏，并学习如何修改菜单或按钮以自定义其外观和功能。读者还将能够探索在 Excel 工作表上直接创建用于特定任务的按钮的过程，并熟练地编辑记录的宏以增强自动化并根据特定要求进行定制。

在快速访问工具栏上创建按钮

要在快速访问工具栏上创建按钮，请按照以下步骤操作：

1. 点击 Office 按钮。

2. 点击 Excel 选项。

3. 转到自定义选项卡。

4. 在“从下拉框中选择命令”下，选择宏。

5. 将您的宏添加到自定义快速访问工具栏。

参考以下图 15.1：

图 15.1：在快速访问工具栏上创建按钮

修改菜单或按钮

每当您想要创建新按钮，编辑现有按钮或从快速访问工具栏中删除任何按钮时，请按照以下步骤操作：

1. 点击修改…按钮。

2. 选择您喜欢的图标。

3. 提及显示名称。

4. 点击确定。

参考以下图 15.2：

图 15.2：修改菜单或按钮

场景 4

创建一个宏，该宏将提取按区域划分的薪水总和（使用“薪水工作表”）。单击“小计”按钮后，应在新工作表上添加按区域划分的小计，如下所示图 15.3：

图 15.3：场景 4

要解决此场景，请按照以下步骤操作：

1. 开始录制（命名为 Subtotal_Macro）

2. 选择薪水表（源数据）

3. 选择 A1 单元格（数据库从 A1 开始）

4. 按照图 15.4 中显示的方式对区域进行排序：

   

   图 15.4：场景 4 解决方案

   注意：排序必须按照小计的要求进行。错误的排序将导致错误的小计。

5. 按照图 15.5 中显示的方式对区域进行小计，并按照给定的步骤操作：

   1. 在数据选项卡上，转到大纲组。

   2. 点击小计按钮。

   3. 也选择区域。

   4. 选择求和函数（根据要求可以选择其他函数）

   5. 选择“工资”字段。

   6. 点击“确定”。

   参考图 15.5：

   

   图 15.5：场景 4 解决方案

6. 点击大纲的第二级（只显示小计）

7. 选择数据。

8. 按下 Alt + ;（分号）键组合以从选择中选择可见单元格。

9. 复制所选内容（Ctrl + C）。

10. 插入新工作表（Shift + F11）。

11. 粘贴复制的数据（Ctrl + V）。

12. 转到工资表（源数据）。

13. 删除小计，使用以下步骤：

    1. 转到“数据”选项卡。点击“小计”。

    2. 点击“RemoveAll”按钮。

14. 激活上一个工作表（Ctrl + PageUp）。

15. 停止录制

参考以下图 15.6：

图 15.6：场景 4 解决方案

在 Excel 工作表上创建按钮

在 Excel 工作表上创建一个按钮，请按照以下步骤进行：

1. 激活工资表。

2. 转到“控件”组。

3. 选择按钮（表单控件），如图 15.7（b）所示

4. 分配小计宏，如图 15.7（c）所示。

5. 更改标题（右键单击并编辑文本），如图 15.7（d）所示。

参考以下图 15.7：

图 15.7：在 Excel 工作表上创建按钮

场景 5

创建一个宏，将从不同部门和区域提取记录。用户将输入所需的部门和区域，然后点击筛选按钮，如图 15.8 所示：

图 15.8：场景 5

注意：上述宏将自动化高级筛选的工作。

按照以下步骤进行：

1. 激活工资表。

2. 为高级筛选创建条件范围。

3. 开始录制。

4. 给予名称“Filter_Macro”。

5. 存储在本工作簿中。

6. 点击数据库的标题（单元格 A5）。

7. 进行高级筛选。

   1. 点击“数据”选项卡。

   2. 点击“高级”。

   参考图 15.9：

   

   图 15.9：场景 5 解决方案

8. 提供列表范围，即数据库范围，如图 15.10 所示。

9. 提供条件范围。

10. 点击“确定”。

11. 停止录制。

12. 从“开发人员”选项卡 | “控件”组创建一个按钮。

13. 将筛选宏分配给此按钮。

参考图 15.10：

图 15.10：场景 5 解决方案

编辑记录的宏

有时记录的宏可能无法完全自动化。然后你需要编辑你记录的宏。或者，你可能想在记录的宏中添加一些操作，然后你可以编辑你的宏。

例如，在场景 5 中，不要在 Excel 中更改条件，而是让你的宏询问区域和部门。用户将输入然后点击“确定”。然后，宏将执行筛选。

要编辑你记录的宏，请按照以下步骤进行：

1. 转到“工具”。

2. 点击“宏”。

3. 打开 Visual Basic 编辑器。

4. 打开你的宏的代码窗口。

场景 6

打开场景 5 并修改代码以执行以下操作。用户应该获得一个 InputBox 来输入所需的部门和地区，然后单击筛选按钮。参考图 15.11：

图 15.11：场景 6

要编辑您录制的宏，请执行以下步骤：

1. 转到工具。

2. 选择宏。

3. 选择 Filter_Macro。

4. 点击编辑，如图 15.12 所示。

5. 打开您的宏的代码窗口。

参考图 15.12：

图 15.12：场景 6 解决方案

参考 Training File3.xls。

1. 宏定义以关键字 Sub 开头，并以 End Sub 结束。

2. Inputbox 是一个用于从最终用户获取输入的函数。

3. 范围是一个类。

注意：我们将稍后详细讨论编写过程。

参考图 15.13：

图 15.13：场景 6 解决方案

场景 7

创建一个宏来从 txt 文件（sales.txt）导入数据并设计一个数据透视表，显示按产品和月份总销售额。此宏将自动化从 txt 文件导入数据并创建一个数据透视表报告（参考 Training File4.xls）：

数字销售总和
产品	月份
光盘	一月 二月
	三月
光盘总数	187000
显��器 一月 二月
显示器总数	35000
闪存	一月
	三月
闪存总数	148000
总计	370000

表 15.1：示例数据

要解决这种情况，请按照以下步骤操作：

1. 开始录制（命名为 import_txt）

2. 点击数据菜单。

3. 选择导入外部数据 | 导入数据。

4. 选择 sales.txt。

5. 选择数据类型为分隔符，如图 15.14 所示（1）。

6. 点击下一步

7. 选择分隔符（逗号），如图 15.14 所示（2）。

8. 点击下一步。

   

   图 15.14：场景 7 解决方案

9. 选择每个数据的类型，如图 15.15 所示。

10. 点击完成。

11. 选择现有工作表选项，如图 15.15 所示。

12. 点击确定。

13. 现在使用导入的数据创建一个数据透视表，并将其放在新工作表中。

14. 停止录制。

    

    图 15.15：场景 7 解决方案

场景 8

参考 Training File4.xls：

场景-8

目的	原始代码	修���后的代码
用于文件选择	ActiveSheet.QueryTables.Ad d(Connection:=”TEXT;c:\vb a\sales.txt”,Destination:=Ran ge(“A1”))	ActiveSheet.QueryTables.Add(C onnection:=”TEXT;” & Application.GetOpenFilename, Destination:=Range(“A1”))
对于可变长度数据	ActiveWorkbook.PivotCache s.Add(SourceType:=xlDatab ase, SourceData:= “Sheet31!R1C1:R7C3”).CreatePivotTable	ActiveWorkbook.PivotCaches.A dd(SourceType:=xlDatabase, SourceData:=Range(“a1”).CurrentRegion.A ddress).CreatePivotTable

练习 1

创建一个宏（Report_title），它将在现有工作簿的单元格地址单元格内容中添加一个新工作表，并包含以下详细信息。

A2：您的公司名称

A3：标题为“每日报告”

A4：今天的日期

A6：序号

B6：产品

C6：销售数量

按照给定的步骤解决问题：

1. 点击“工具” | “宏”。

2. 选择记录新宏。

3. 将宏的名称命名为“报告标题”。

4. 分配快捷键。

5. 选择存储位置。

6. 在描述框中添加详细信息。

7. 点击“确定”。

8. 添加一个空白工作表（Shift+F11 或插入菜单 | 工作表）

9. 点击 A2 并输入你公司的名称。

10. 点击 A3，输入标题为“每日报告”。

11. 点击 A4，输入“=today()”。

12. 从 A6 到 C6 输入“序号”、“产品”和“销售数量”

13. 停止录制。

练习 2

创建一个宏，添加签名，即您的姓名和职务在数据末尾。

注意：假设工作表的格式是练习 1。

这个宏总是在当前工作表之前添加一个工作表。修改该宏，使其在当前工作表之后添加新工作表。

按照给定的步骤解决问题：

1. 点击“工具” | “宏”。

2. 选择记录新宏。

3. 将宏的名称命名为“汇总”。

4. 分配一个快捷键。

5. 选择存储宏的位置。

6. 在描述框中添加详细信息。

7. 点击“确定”。

8. 点击单元格 A6。

9. 使用 Ctrl + ↓到达数据末尾。

10. 点击“相对引用”。

11. 现在向下移动 3 行，输入你的姓名和职务。

12. 关闭相对引用。

13. 停止录制。

14. 修改语句“Sheets.add”为“sheets.add after:=activesheet”。

结论

在本章中，我们讨论了在 Excel 中为宏分配按钮的主题。我们学习了如何在快速访问工具栏上创建按钮，并通过修改菜单或按钮来自定义它们。此外，我们探讨了直接在 Excel 工作表上创建按钮的过程。我们还讨论了编辑记录的宏以增强其功能的过程。

练习

1. 创建一个宏，提取按地区汇总的工资总和，并将其添加到新工作表中。

2. 创建一个宏，根据部门和地区筛选记录。

3. 从文本文件导入数据并创建数据透视表报告。

4. 修改现有宏中的文件选择和变量长度数据的代码。

5. 练习创建一个宏，在工作表中添加每日报告标题。

6. 练习创建一个宏，在数据末尾添加签名。

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace，获取最新更新、优惠、全球技术动态、新发布和作者会话：

discord.bpbonline.com

第十六章 VBA 中的函数和子例程

介绍

本章将介绍 Excel VBA 编程中函数和子例程的概念。它解释了两者之间的区别，然后深入探讨在模块中使用 Visual Basic Editor 编写代码。本章还涵盖了用于控制过程内代码执行流程的分支技术。

结构

在本章中，我们将讨论以下主题：

• 编写过程

• Visual Basic Editor

• 插入模块

• 在模块内编写代码

• 子过程

• 函数过程

• 分支过程

目标

学习完本章后，读者将能够编写过程并了解 Visual Basic Editor，以便插入模块、编写代码等。读者还将了解子过程、函数过程以及分支过程。

编写过程

您可以为记录的每个操作编写代码。所有过程都写在一个模块内。

要编写代码，您需要打开 Visual Basic Editor。打开 Visual Basic Editor 的快捷键是 Alt + F11。

模块是一组过程。有两种类型的过程：

• 子过程：子过程用于自动化 Excel 操作。子过程是在 Sub 和 End Sub 块之间封装的代码单元。没有任何参数的子过程是一个宏。

• 函数过程：函数用于自动化任何复杂计算。函数过程在 Function 和 End Function 块之间封装。

子过程和函数过程之间的区别在下表表 16.1 中有所探讨：

子过程	函数过程
它不能返回值	它可以返回值
它可以在 Excel 对象上执行操作	它不能在 Excel 对象上执行操作

表 16.1：过程之间的区别

这里有一些你可以记住的要点：

• 两种过程可能有或没有参数。

• 没有参数的子过程是一个宏。

• 所有宏都是过程，但并非所有过程都是宏。

Visual Basic Editor

下图图 16.1 显示了 Visual Basic Editor 中的代码窗口：

图 16.1：Visual Basic Editor 中的代码窗口

Visual Basic Editor 中代码窗口的不同部分如下：

• 项目资源管理器：它显示了项目（Excel 工作簿）的分层列表以及每个项目包含和引用的所有项目。

• 属性窗口：它列出了所选对象的设计时属性及其当前设置。您可以在设计时更改这些属性。当选择多个控件时，属性窗口包含所有所选控件共有的属性列表。

• 代码窗口：使用代码窗口编写、显示和编辑 Visual Basic 代码。您可以打开与模块数量相同的代码窗口，以便轻松查看不同形式或模块中的代码，并在它们之间复制和粘贴。

图 16.2 更详细地探讨了项目资源管理器：

• 查看代码：显示代码窗口，以便您编写和编辑与所选项目相关的代码。

• 查看对象：显示所选项目的对象窗口，一个现有文档或用户表单。

• 切换文件夹：隐藏和显示对象文件夹，同时仍显示其中包含的各个项目。

• 列表窗口：列出所有加载的项目及每个项目中包含的项目。

属性是任何对象的特征。属性窗口显示了所选对象的属性，如图 16.2 所示：

图 16.2：项目资源管理器和属性窗口

现在让我们来看看代码窗口的不同部分（参考图 16.3）：

• 对象框：显示当前项目中对象的列表。

• 过程窗口：包含当前模块的所有过程或所选对象的事件。

• 过程视图：一次只显示一个过程。

• 完整模块视图：显示当前模块中的所有过程。

参考以下图 16.3：

图 16.3：代码窗口

项目资源管理器键盘快捷键

现在让我们来看看各种键盘快捷键：

• 回车 + Æ：打开列表中的所选文件，或展开和折叠列表以显示其子条目。

• SHIFT+ENTERÆ：打开所选文件的代码窗口。

• F7 + Æ：打开所选文件的代码窗口。

• SHIFT+F10 Æ：查看快捷菜单。

• HOME + Æ：选择列表中的第一个文件。

• END + Æ：选择列表中的最后一个文件。

• 右箭头 + Æ：展开列表，然后每次按下时选择列表中的子条目。

• 左箭头 + Æ：选择列表中的子条目，然后每次按下时���上移动列表，直到子条目列表折叠为文件夹。

• 上箭头 + Æ：逐个向上移动列表中的条目。

• 下箭头 + Æ：逐个向下移动列表中的条目。

插入模块

要插入模块，请按照给定步骤进行：

1. 选择要存储过程的书籍，如图 16.4（1）所示。

2. 选择“插入”菜单，然后选择“模块”，如图 16.4（2）所示。

3. 已添加 Module1。你可以通过属性窗口更改名称，如图 16.4（3）所示。

参考图 16.4：

图 16.4：插入模块

在模块内编写代码

要在模块内编写代码，请按照以下步骤进行：

1. 双击要在其中编写过程代码的模块，如下所示的图 16.5（a）。

2. 编写您的过程代码，如下所示的图 16.5（b）。

参考以下图 16.5：

图 16.5：在模块内编写代码

子过程

子过程是一系列由 Sub 和 End Sub 语句包围的 Visual Basic 语句，执行操作但不返回值。

子过程可以接受参数，如常量、变量或由调用过程传递的表达式。

如果一个子过程没有参数，子语句必须包含一对空括号

宏

宏如下所示：

Sub HelloWorld()MsgBOx "Hello World"End Sub

图 16.6 包含一个宏：

图 16.6：宏

函数过程

函数过程是一系列由 Function 和 End Function 语句包围的 Visual Basic 语句。

函数过程类似于子过程，但函数还可以返回一个值。函数过程可以接受由调用过程传递给它的参数。

如果一个函数过程没有参数，其函数语句必须包含一对空括号。函数通过在过程的一个或多个语句中为其名称赋值来返回一个值。

例如，

Function Celsius (fDegrees)Celsius = (fDegrees - 32) * 5 / 9End Function

参考以下图 16.7：

图 16.7：用户定义函数

编写函数的语法

Function name_of_function( argument1 , argument2 , …)processing the arguments name_of_function= ResultEnd Function

注意：参数是您希望从最终用户那里获得以计算结果的输入。

两个过程可能有或没有参数：

通过值传递 Æ：如果通过值传递参数，则被调用过程仅接收从调用过程传递的变量的副本。如果被调用过程���改值，则更改仅影响副本而不影响调用过程中的变量。

通过引用传递 Æ：如果在调用过程时通过引用传递参数，则过程可以访问内存中的实际变量。因此，过程可以更改变量的值。默认情况下，参数是通过引用传递的。

场景 9

编写一个函数来计算利润，其中利润是销售价格和成本价格的差额。利润函数需要两个参数，即成本价格和销售价格。

参考 Training File5.xls

Function Profit(CP, SP)Profit = SP - CPEnd Function

分支过程

如果你想根据条件的值运行一段代码块，可以使用以下决策结构。

• ¾ 如果...那么...Endif

• ¾ 如果...那么...否则...Endif

• ¾ 如果...那么...否则如果...那么...否则...Endif

• ¾ 选择 Case …End Select

使用 如果...那么...Endif

单个条件并运行单个语句或一组语句。

使用 如果...那么...否则...Endif

单个条件，但根据条件的结果运行两个不同的语句或语句块。

使用 If...Then...Elseif…Then…Else…Endif 或 Select Case…End

选择多个条件并运行多个语句块中的一个。

场景 10

编写一个函数来检查人是否有资格投票。为了检查资格，Vote 函数需要年龄作为参数。

参考培训文件 5.xls

函数 Vote(Age)If Age >= 18 ThenVote = "有资格"ElseVote = "不符合资格"EndIfEnd Function

场景 11

编写一个函数根据基本工资找到员工的等级（使用 If Elseif）：

等级工资

D <8000

C 8000 – 15000

B 15000 – 25000

A >=25000

参考培训文件 5.xls

函数 Grade (salary)If salary<8000 ThenGrade = "D"ElseIf salary<15000 ThenGrade = "C"ElseIf salary<25000 ThenGrade = "B"ElseGrade ="A"End IfEnd Function

场景 12

编写一个函数根据等级找到奖金（使用 Select Case）

等级奖金

A 25000

B 20000

C 15000

D 10000

参考培训文件 5.xls

函数 bonus(grade)Select Case gradeCase "a", "A"bonus = 25000Case "b", "B"bonus = 20000Case "c", "C"bonus = 15000Case Elsebonus = 10000End SelectEnd Function

场景 13

编写一个函数来根据地区计算津贴。如果地区是东部或西部，则津贴为 5%，否则为 10%。使用 OR 运算符来检查多个条件。

OR 运算符

OR 运算符用于对两个表达式执行逻辑合取。如果任一表达式结果为真，则结果为真。

函数 CalcDa(Region, sal)If Region = "east" Or Region = "west" ThenCalcDa = sal * 0.05ElseCalcDa = sal * 0.1EndIfEnd Function

场景 14

编写一个函数来根据地区计算津贴。如果地区是东部且工资>10000，则为 5%，否则为 10%。

使用 AND 运算符来检查多个条件。

AND 运算符

用于对两个表达式执行逻辑合取。AND 如果所有表达式结果为真，则结果为真。

函数 CalcDa(Region, sal)If Region = "east" And sal > 10000 ThenCalcDa = sal * 0.05ElseCalcDa = sal * 0.1EndIf

结论

函数和子程序是 VBA 中强大的工具，帮助您自动化任务并在 Excel 中执行计算。通过了解如何编写过程，使用 Visual Basic Editor，并应用分支技术，您可以增强您的 VBA 编程技能，并创建更高效和动态的 Excel 应用程序。

练习

1. 编写一个函数来计算矩形的面积，给定其长度和宽度。

2. 创建一个子程序来根据特定条件格式化一系列单元格，例如突出显示值高于某个阈值的单元格。

3. 开发一个将华氏温度转换为摄氏温度的函数。

4. 编写一个子程序，将数据列按升序排序。

5. 创建一个函数来计算给定数字的阶乘。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布内容以及与作者的交流：

discord.bpbonline.com

第十七章 VBA 中的条件语句

介绍

在 Visual Basic for Applications（VBA）中，条件语句用于根据特定条件在代码中做出决策。它们允许您根据逻辑表达式的结果执行不同的代码块。在本章中，我们将重点介绍 VBA 中两种常用的条件语句：选择情况和如果…那么结束如果。

结构

在本章中，我们将讨论以下主题：

• 如果…那么结束如果

• 选择情况

• 选择情况 Vs. 如果…那么结束如果

目标

通过本章的学习，读者将了解 VBA 中选择情况和如果…那么结束如果语句之间的区别，并学会在不同场景中正确使用它们。

如果…那么结束如果

在 VBA 中，“如果…那么结束如果”语句用于评估条件并在条件为真时执行一段代码块。它还可以与“否则如果”和“否则”子句结合使用以处理多个条件。以下是“如果…那么结束如果”语句的基本语法示例：

如果 条件 1 那么' 如果条件 1 为真，则执行的代码块否则如果 条件 2 那么' 如果条件 2 为真且条件 1 为假，则执行的代码块否则' 如果前面的条件都不为真，则执行的代码块结束如果

上述示例中的“条件”和“条件 2”是布尔表达式，其结果为真或假。

需要注意的是，“如果…那么结束如果”语句可以嵌套在其他控制结构中，如循环中，并且可以与其他语句和关键字结合使用，以在您的 VBA 代码中创建更复杂的逻辑。

示例

如果分数等于或大于 90，则显示“等级：A”。如果在 80 到 89 之间，则显示“等级：B”，依此类推。如果分数不符合任何指定条件，则显示“等级：F”。

子 评分评估()如分数为整数' 提示用户输入分数分数 = 输入框("输入分数：")' 评估分数并提供相应等级如果 分数 >= 90 则消息框 "等级：A"否则如果 分数 >= 80 则消息框 "等级：B"否则如果 分数 >= 70 则消息框 "等级：C"否则如果 分数 >= 60 则消息框 "等级：D"否则消息框 "等级：F"结束 如果结束 子

选择情况

VBA 中的选择情况语句提供了一种简洁和结构化的方式来处理多个条件，并根据单个表达式的值执行不同的代码块。以下是其语法和用法的全面解释：

选择情况 表达式情况 值 1' 如果表达式匹配值 1，则执行的代码情况 值 2' 如果表达式匹配值 2，则执行的代码情况 其他' 如果表达式与任何先前情况都不匹配，则执行的代码结束选择

示例

在这个示例中，程序提示用户输入一个水果名称。然后，Select Case 语句评估输入并根据所选水果显示相应的消息。如果水果是“苹果”，它会显示有关其健康选择的消息。如果是“香蕉”，它会提到其钾含量。如果是“橙子”或“柑橘”，它会突出柑橘的好处。对于任何其他输入，它会显示一个通用消息。

Sub FruitSelection()Dim fruit As String' 提示用户输入一个水果名称 fruit = InputBox("请输入一个水果名称：")' 评估水果名称并显示相应的消息 Select Case fruitCase "apple"MsgBox "你选择了一个苹果。这是一个健康的选择！"Case "banana"MsgBox "你选择了一个香蕉。它是钾的良好来源。"Case "orange", "mandarin"MsgBox "你选择了一个橙子或柑橘。享受柑橘的好处！"Case ElseMsgBox "这是一个有趣的选择！"End SelectEnd Sub

Select Case Vs. If … End If

当将一个表达式与几个不同的值进行比较时，使用 Select Case 语句作为 If...Then...Else 语句中 ElseIf 的替代方法。

虽然 If...Then...Else 语句可以为每个 ElseIf 语句评估不同的表达式，但 Select Case 语句仅在控制结构的顶部评估一次表达式。

结论

在本章中，我们探讨了 VBA 中 Select Case 和 If...End If 语句之间的区别。我们了解到，当您有多个条件要评估针对单个表达式时，Select Case 是一个有用的替代方案。它简化了您的代码并提高了可读性。然而，当您有不同且无关的条件要考虑时，If...End If 语句更合适。

练习

1. 编写一个 VBA 程序，询问用户输入一周中的一天（从 1 到 7 的数字），并显示相应的星期几名称。使用 Select Case 和 If...End If 语句来实现该程序。

2. 编写一个 VBA 程序，询问用户输入一个数字，并确定它是正数、负数还是零。使用 Select Case 和 If...End If 语句来实现该程序。

加入我们书籍的 Discord 空间

加入本书的 Discord 工作空间，了解最新更新、优惠、世界各地的技术动态、新发布和作者的会议：

discord.bpbonline.com

第十八章 VBA 中的变量和数据类型

介绍

在本章中，我们将探讨 VBA（Visual Basic for Applications）中变量和数据类型的基础知识。变量是编程中的基本元素，它们在程序执行过程中存储和操作数据，而数据类型定义了变量中存储的数据的性质。理解变量和数据类型对于编写高效和有效的 VBA 代码至关重要。我们将涵盖声明变量和常量、指定数据类型、使用消息框和输入框、选择单元格、行和列以及与工作表、工作簿和应用程序对象一起工作等主题。

结构

在本章中，我们将涵盖以下主题：

• 变量和常量

• 声明变量和常量

• 变量和常量的数据类型

• 消息框和输入框

• 选择和激活单元格

• 选择和激活行和列

• 与工作表一起工作

• 与工作簿一起工作

• 与应用程序对象一起工作

目标

通过本章结束时，读者将能够理解 VBA 中变量和常量的概念，并学会如何声明它们，熟悉 VBA 中可用的不同数据类型及其各自的范围，并探索消息框和输入框用于用户交互的用法。此外，读者还将学习在 Excel 中选择和激活单元格、行和列的技巧，并了解在 VBA 中使用工作表、工作簿和应用程序对象的知识。

变量和常量

现在让我们学习关于变量和常量。

变量

变量的特点如下：

• 变量是一个命名的存储位置，其中包含在程序执行过程中可以修改的数据。

• 每个变量都有一个名称，用于在其范围内唯一标识它。

• 可以指定或不指定数据类型。

• 变量名称：

  • 必须以字母字符开头，

  • 在相同范围内必须是唯一的，

  • 不能超过 255 个字符，并且

  • 包含嵌入的句点或类型声明字符。

常量

在程序执行过程中保持恒定值的命名项目。常量可以是字符串或数字文字。

声明变量和常量

声明变量的语法是：

DIM name_of_variable AS type_of_variable

例如

Dim strName As String

Dim intX As Integer

Dim intX，intYAs Integer

声明常量的语法是：

Const name_of_variable AS type_of_variable = constant value

例如

Const conAge As Integer = 34

在声明变量时，使用 Dim 语句，而对于常量使用 Const 语句。

声明语句可以放置在过程中以创建过程级变量。或者它可以放置在模块的顶部，在声明部分中，以创建模块级变量。

变量和常量的数据类型

表 18.1 显示了数据类型的各种范围：

数据类型	范围
字节	0 到 255。
整型	-32,768 到 32,767。
长整型	-2,147,483,648 到 2,147,483,647。
单精度	-3.402823E38 到 -1.401298E-45（负值）。
-1.401298E-45 到 3.402823E38（正值）。
双精度	-1.7200369313486231E308 到-4.94065645841247E-324（负值）。4.94065645841247E-324 到 1.7200369313486231E308（正值）。
货币	-922,337,203,685,477.5808 到 922,337,203,685,477.5807。
字符串	零到大约两十亿个字符。
变体	日期值：公元 100 年 1 月 1 日到公元 9999 年 12 月 31 日。
数值：与双精度浮点数相同的范围。
字符串值：与字符串相同的范围。
也可以包含错误或空值。
布尔	真或假。
日期	公元 100 年 1 月 1 日到公元 9999 年 12 月 31 日。
对象	任何对象引用。

表 18.1：数据类型

使用 Option Explicit 语句

使用 Option Explicit 强制声明变量。它必须出现在任何过程之前的模块中。如果不使用，未声明的变量将是 Variant 类型。

消息框和输入框

Msgbox 函数在对话框中显示消息，等待用户点击按钮，然后返回一个整数，指示用户点击了哪个按钮。

InputBox 函数在对话框中显示提示，等待用户输入文本或点击按钮，然后返回一个包含文本框内容的字符串。

例如：

子过程问候()消息框 "你好 " & InputBOx("你叫什么名字？")结束子过程

选择和激活单元格

当你使用 Microsoft Excel 时，通常会选择一个单元格或多个单元格，然后执行操作，比如格式化单元格或在其中输入值。

参考表 18.2，编写各种操作的代码：

要做这个	写下这段代码
选择单元格 A1	Range("A1").select 或 Cells(1,1).select
选择范围 A1:B5	Range("A1:b5").select
选择范围 A1:A5 和 C2:C10	Range("A1:A5 , C2:C10").select
选择当前单元格	Activecell.select
从当前单元格到 b6 选择范围	Range(Activecell , "b6").select
选择活动单元格的当前区域	Activecell.currentregion.select
从活动单元格按 Ctrl + Shift+ 下箭头	Range(ActivecellActivecell.End(X lDown)).select
从单元格 A2 按 Ctrl + Shift + 下箭头	Range("A2" , Activecell.End(XlDown)).select

表 18.2：各种操作的代码

选择和激活行和列

有时您需要选择特定的行和列，然后执行操作。

要做这个，请写下面表 18.3 中显示的代码：

要做这个	写这个代码
选择一行	Rows("2:2").select
选择从第 2 行到第 5 行	Rows("2:5").select
从活动单元格选择 3 行	Activecell.entirerow.Range("1:3"). select
选择一列	Columns("A:A").select
选择从 B 到 E 的列	Columns("B:E").select
从活动单元格选择 3 列	Activecell.entirecolumn.Range("A:C").select
选择当前行	Activecell.entirerow.select
选择当前列	Activecell.entirecolumn.select

表 18.3：各种操作的代码

与工作表一起工作

许多时候，您需要选择特定的工作表，或插入新工作表，重命名工作表等。请参考表 18.4：

要做这个	写这个代码
通过索引号选择任何工作表	Sheets(2).selectWorksheets(2).select
通过名称选择任何工作表	Sheets("Sheet1").selectWorksheets("Sheet1").select
重命名工作表	Sheets("Sheet1").name="新名称"
新名称	Worksheets("Sheet1").name=Activesheet.name
删除工作表	Sheets("Sheet1").deleteWorksheets("Sheet1").deleteActivesheet.delete
插入工作表	Sheets.add before:= sheets("Sheet1")Worksheets.add before:=sheets("Sheet1")

表 18.4：各种操作的代码

与工作簿一起工作

有时您需要处理不同的工作簿。请参考表 18.5：

要做这个	写这个代码
打开一个工作簿	Workbooks.open filename:="带路径的文件名"
打开包含自动宏的工作簿	Workbooks.openfilename:="Activeworkbook.runautomacros"
关闭工作簿	Workbooks(2).close
添加新工作簿	Workbooks.add

表 18.5：各种操作的代码

与应用程序对象一起工作

有时为了忽略不同的 Excel 消息，您需要使用应用程序对象，如下所示的表 18.6：

要做这个	写这个代码
关闭消息显示	Application.DisplayAlert = False
停止屏幕闪烁	Application.ScreenUpdating = False
停止复制/剪切模式	Application.CutCopyMode = False
计算	Application.Calculate

表 18.6：各种操作的代码

场景 15

创建一个宏，应接受人员的姓名和城市，并将其存储在 Excel 工作表的单元格 A1 和 B1 中。如果用户输入 Mumbai 作为城市，则字体颜色必须为红色。使用 InputBox 函数从用户那里获取输入。使用 MsgBox 函数显示结果。

Sub Accept_Details()Dim e_Name , e_City As StringName = InputBox("输入您的姓名")City = InputBox("输入您的城市")MsgBox "您的姓名是 " & Name & "，城市是 " & CityCells(1, 1).Value = e_Name Cells(1, 2).Value = e_CityIf Cells(1, 2).Value = "mumbai" ThenCells(1, 2).Font.ColorIndex = 3ElseCells(1, 2).Font.ColorIndex = 0EndIfEnd Sub

场景 16

创建一个名为 Data_Entry 的宏。接受一个人的员工编号、姓名、入职日期和工资。将这些值插入“数据库”工作表中。每条新记录必须存储在最后一条记录之后。

Sub Data_Entry()Dim EmpCode As integer, Next_Row as integerDim EmpName As StringDim doj As DateDim Salary As CurrencyEmpCode = InputBox("Enter Employee Code")EmpName = InputBox("Enter Employee Name")doj = InputBox("enter Date of Joining mm/dd/yy")Salary = InputBox("Enter Salary of Employee")Range("a65536").selectSelection.end(xlup).select Next_Row= activecell.row+1Cells(Next_Row, 1).Value = EmpCode Cells(Next_Row, 2).Value = EmpNameCells(Next_Row, 3).Value = Format(doj, "MMM DD YYYY")Cells(Next_Row, 4).Value = SalaryEnd Sub

结论

总之，本章全面介绍了 VBA 中的变量和数据类型。它涵盖了变量和常量的声明，解释了不同数据类型及其范围，演示了消息框和输入框的使用，并探讨了在 Excel 中选择和操作单元格、行和列的技巧。本章还涉及与工作表、工作簿和应用程序对象的工作。通过理解这些基础知识，读者可以编写高效且有效的 VBA 代码。

练习

1. 编写一个 VBA 宏，提示用户使用输入框输入他们的姓名、年龄和喜欢的颜色。该宏应将这些值分别存储在活动工作表的单元格 A1、B1 和 C1 中。此外，如果用户的年龄大于或等于 18 岁，则相应单元格的字体颜色应设置为他们喜欢的颜色。通过运行宏并输入不同的值来测试宏。

加入我们书籍的 Discord 空间

加入本书的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第十九章 VBA 中的循环结构

介绍

在本章中，我们将深入探讨 Visual Basic for Applications（VBA）中的循环结构主题。循环是强大的工具，可以使代码重复执行，提高效率并自动化任务。本章探讨了不同类型的循环，如 Do...Loop、For...Next 和 For Each...Next 循环，以及基于特定事件运行的自动执行的宏。

结构

在本章中，我们将讨论以下主题：

• 使用循环（重复动作）

• 使用 Do…Loop 语句

• 使用 For…Next 语句

• 使用 For Each…Next 语句

• 自动执行的宏

目标

通过本章结束时，读者将学习 VBA 中的循环结构，如 Do...Loop、For...Next 和 For Each...Next，以及实际示例的实现。

使用循环（重复动作）

循环允许您重复运行一组语句。一些循环重复语句直到条件为 False；其他循环重复语句直到条件为 True。还有一些循环重复语句特定次数或对集合中的每个对象重复。

选择要使用的循环

有各种循环可以使用，例如：

• Do…Loop：在条件为 True 时循环。

• For…Next：使用计数器运行指定次数的语句。

• For Each…Next：为集合中的每个对象重复一组语句。

使用 Do…Loop 语句

您可以使用 Do...Loop 语句无限次运行一组语句。这些语句在条件为 True 时重复，或者直到条件变为 True。

语法：

Do [{While | Until} 条件]

[语句]

[退出 Do]

[语句]

循环

在条件为真时重复语句

在 Do...Loop 语句中，有两种使用 While 关键字检查条件的方式¾您可以在进入循环之前检查条件，或者¾您可以在循环至少运行一次后检查条件。

在进入循环之前检查条件

在进入循环之前检查条件的语法是：

DO WHILE (条件)

要重复的代码

循环

在循环至少运行一次后检查条件

在循环至少运行一次后检查条件的语法是：

DO

要重复的代码

LOOP WHILE (条件)

场景 17

编写一个接受和验证用户名的代码。不应允许空名称。参考培训文件 5.xls：

参考图 19.1：

图 19.1：场景 17

Sub validate_name()Dim name As Stringname = InputBox("输入您的姓名")Do While Trim(name) = ""MsgBox "姓名不能为空"name = InputBox("输入您的姓名")LoopEnd Sub

注意：Trim 函数会删除单词开头和结尾的空格。

使用 For…Next 语句

• 您可以使用 For...Next 语句重复执行一组语句特定次数。

• For 循环使用一个计数器变量，其值在每次循环内部重复时增加或减少。

语法：

对于计数变量=初始值到最终值 STEP 步长值

要重复的代码

下一个

注意：数据类型越小，更新所需的时间越短。

场景 18

创建一个名为 fill_series 的宏，以显示从 1 到 10 的数字（从单元格 A1 开始）。

Sub fill_series()

声明 fill_val 为整数

Range("A1").Select

对于 fill_val = 1 到 10

ActiveCell.Value = fill_val

ActiveCell.Offset(1, 0).Select Next

End Sub

Offset 函数()用于指向或引用对象的上、下、左或右。

语法：

OFFSET(行，列)

示例

1. Activecell.Offset(1,0).select：这将选择 Activecell 下面 1 行，右边 0 列的单元格，

2. Activecell.Offset(0,1).select：这将选择 Activecell 右边 0 行，1 列的单元格，

3. Activecell.Offset(-1,0).select：这将选择 Activecell 上面 1 行，右边 0 列的单元格，

4. Activecell.Offset(0,-1).select：这将选择 Activecell 下面 0 行，左边 1 列的单元格。

使用 ForEach…Next 语句

For Each...Next 语句为集合中的每个对象或数组中的元素重复执行一组语句。

Visual Basic 每次循环运行时都会自动设置一个变量。

可以在循环中的任何位置放置任意数量的 Exit For 语句作为退出的替代方式。

语法：

对于每个元素在组中

[语句]

[退出 For]

[语句]

下一个[element]

必需：用于迭代集合或数组元素的变量。对于集合，元素只能是 Variant 变量、通用对象变量或任何特定对象变量。对于数组，元素只能是 Variant 变量。

组：必需。对象集合或数组的名称

语句

可选。在组中的每个项目上执行的一个或多个语句。

场景 19

创建一个名为 UPPER_CASE 的宏，将数据转换为大写字母。使用 Ucase()函数将大小写转换为大写字母

参考以下图 19.2：

图 19.2：场景 19

Sub UPPER_CASE()Dim wscell As RangeFor Each wscell In Selectionwscell.Value = UCase(wscell.Value)NextEnd Sub

场景 20

创建一个名为 lower_case 的宏，将数据转换为小写字母。使用 lcase()函数将大小写转换为小写字母

参考图 19.3：

图 19.3：场景 20

Sub lower_case()Dim wscell As RangeFor Each wscell In Selectionwscell.Value = LCase(wscell.Value)NextEnd Sub

场景 21

创建一个名为 Proper_case 的宏，将数据转换为标题大小写字母。使用 WorksheetFunction 对象在 VBA 中使用 Excel 中的任何函数。

参考图 19.4：

图 19.4：场景 21

Sub Proper_Case()Dim wscell As RangeFor Each wscell In Selectionwscell.Value = Application.WorksheetFunction.Proper(wscell.Value) NextEnd Sub

场景 22

打开场景 22 并进行修改。在记录宏后，您应该询问用户是否继续，并根据用户的响应运行。如果用户点击确定，则应继续数据输入。如果用户点击取消，则显示“谢谢”并结束宏。参考图 19.5：

图 19.5：场景 22

Sub Data_Entry1()Dim EmpCode As Integer, next_row As IntegerDim EmpName As StringDim doj As DateDim Salary As CurrencyWorksheets("database").SelectRange("a65536").SelectSelection.End(xlUp).Selectnext_row = ActiveCell.Row + 1DoEmpCode = InputBox("输入员工编号")EmpName = InputBox("输入员工姓名")doj = InputBox("输入入职日期 mm/dd/yy")Salary = InputBox("输入员工工资")Cells(next_row , 1).Value = EmpCodeCells(next_row , 2).Value = EmpNameCells(next_row , 3).Value = Format(doj, "MMM DD YYYY")Cells(next_row , 4).Value = Salarynext_row =next_row + 1Loop While (MsgBox("是否继续？", vbOKCancel) = vbOK)MsgBox "谢谢"End Sub

场景 23

创建一个宏，将为每个员工计算以下内容

• 住房津贴（工资的 75%）

• DA（工资的 60%）

• 总计（工资+住房津贴+DA）

参考图 19.6：

图 19.6：场景 23

解决这个问题可能有两种方法

参考培训文件 6.xls

1. 通过宏，您可以将公式放入单元格中：

   Sub Gross_Salary()‘用户将选择范围 H2:H101 单元格作为范围。对于选择中的每个 wscellwscell.Offset(0, 1).Value = "=rc[-1]75%"wscell.Offset(0, 2).Value = "=rc[-2]60%"wscell.Offset(0, 3).Value = "=sum(rc[-1]:rc[-3])"NextEnd Sub

2. 在您的宏中计算并仅将结果放入单元格中：

   Sub Gross_Salary()用户将选择范围 H2:H101 Dim wscell As RangeFor Each wscell In Selectionwscell.Offset(0, 1).Value= wscell.Value * .75wscell.Offset(0, 2).Value = wscell.Value * 60%wscell.Offset(0, 3).Value = wscell.value + wscell.Offset(0, 1).Value + wscell.Offset(0, 2).ValueNextEnd Sub

场景 24

创建一个宏来显示当前工作簿中工作表名称的列表。参考图 19.7：

图 19.7：场景 24

Sub list_sheets()„ 声明一个工作表对象变量 Dim sht As WorksheetFor Each sht In WorksheetsActiveCell.Value = sht.nameActiveCell.Offset(1, 0).SelectNextEnd Sub

自动执行的宏

语法是：

fydyr

参考以下表 19.1：

到	使用
在工作簿打开时运行宏	Sub auto_open()End Sub
在工作簿关闭时运行宏	Sub auto_close()End Sub

表 19.1：自动执行的宏

练习 3

编写一个名为“Search_sheet”的函数来检查任何工作表是否存在。

Function Search_sheet(newSht)Dim sht As WorksheetFor Each sht In WorksheetsIf UCase(sht.name) = UCase(newSht) ThenSearch_sheet = "工作表(" & newSht & ") 存在"Exit FunctionEnd IfNextSearch_sheet = "工作表(" & newSht & ") 不存在"End Function

练习 4

编写一个宏，为每位员工增加 2000 的工资。

场景 25

创建一个宏，使用数据透视表生成按区域和部门工资总和以及员工数量的总计。修改代码，使每次都在当前数据上生成数据透视表。

参考培训文件 6.xls

Sub Pivot_Summary()Range("A2").SelectActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ Range("a2").CurrentRegion).CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)ActiveSheet.Cells(3, 1).SelectActiveSheet.PivotTables("PivotTable2").AddFields RowFields:=Array("Region", _ "Dept", "Data")With ActiveSheet.PivotTables("PivotTable2").PivotFields("salary").Orientation = xlDataField.Position = 1End WithWith ActiveSheet.PivotTables("PivotTable2").PivotFields("Empcode").Orientation = xlDataField.Caption = "Count of Empcode".Function = xlCountEnd WithRange("C3").SelectWith ActiveSheet.PivotTables("PivotTable2").DataPivotField.Orientation = xlColumnField.Position = 1End WithEnd Sub

场景 26

编写一段代码，如果“master”工作表中存在，则从“daily”工作表中删除重复记录。

（使用嵌套循环）

解决方案 26

参考培训文件 7.xls

Sub duplicates()Dim wscell As Range, tcell As RangeWorksheets("master").SelectRange("a2").SelectRange(ActiveCell, ActiveCell.End(xlDown)).SelectFor Each wscell In SelectionWorksheets("daily").SelectRange("a2").SelectRange(ActiveCell, ActiveCell.End(xlDown)).SelectFor Each tcell In SelectionIf tcell.Value = wscell.Value Thentcell.EntireRow.Delete End IfNextNextActiveCell.SelectEnd Sub

可以使用查找命令编写相同的代码如下

Sub duplicates_With_find()Worksheets("master").SelectRangec("a2").SelectRange(ActiveCell, ActiveCell.End(xlDown)).SelectFor Each tcell In SelectionWorksheets("daily").SelectSet c = Cells.Find(What:=tcell.Value)If Not c Is Nothing Then Rows(c.Row).DeleteEnd IfNextEnd Sub

场景 27

创建一个名为 Merging_Sheets 的宏，将所有工作表的数据复制到一个工作表中

你的宏应该生成一个按区域销售数量总计和员工代码总计的数据透视表。

解决方案

Sub Merging_Sheets()'Scenario27'在末尾添加一个工作表，并将其命名为 consolidate 并创建标题 Worksheets.Add After:=Worksheets(Worksheets.Count)ActiveSheet.Name = "consolidate"ActiveSheet.Range("a1").SelectRange("a1").Value = "Product"Range("b1").Value = "Sales"将每个工作表的数据复制到 consolidate 工作表 For Index = 1 To Worksheets.Count - 1Worksheets(Index).SelectRange("a2").SelectRange(Selection, Selection.End(xlDown)).SelectRange(Selection, Selection.End(xlToRight)).SelectSelection.CopyWorksheets("consolidate").SelectCells(Range("a65536").End(xlUp).Row + 1, 1).SelectActiveSheet.PasteNext,生成一个关于合并数据的数据透视表 Sheets("consolidate").SelectRange("A1").SelectApplication.CutCopyMode = FalseActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _Range("a1").CurrentRegion).CreatePivotTable TableDestination:="", TableName _:="PivotTable1", DefaultVersion:=xlPivotTableVersion10ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).SelectActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Product"ActiveSheet.PivotTables("PivotTable1").PivotFields("Sales").Orientation = _ xlDataFieldEnd Sub

结论

精通循环结构对于有效的 VBA 编程至关重要。通过利用循环，您可以自动化重复任务，处理大量数据，并提高 VBA 程序的整体效率。本章全面介绍了循环及其在 VBA 中的应用，为您提供了编写简洁而强大的代码的技能，节省时间和精力。

练习

1. 编写一个名为“PrintNumbers”的 VBA 宏，将数字从 1 打印到 100 在即时窗口中。

2. 创建一个名为“CalculateSum”的 VBA 宏，用于计算从 1 到 10 的数字的总和，并在消息框中显示结果。

3. 编写一个名为“EvenNumbers”的 VBA 宏，在即时窗口中打印从 1 到 20 的所有偶数。

4. 创建一个名为“Factorial”的 VBA 宏，用于计算给定数字的阶乘。该宏应提示用户输入一个数字，然后在消息框中显示阶乘结果。

5. 编写一个名为“ReverseString”的 VBA 宏，提示用户输入一个字符串，然后在即时窗口中打印字符串的反转。

6. 创建一个名为“TableOfSquares”的 VBA 宏，生成从 1 到 10 的平方表。该宏应在新工作表中的不同列中显示数字及其平方。

7. 编写一个���为“CountCharacters”的 VBA 宏，计算给定字符串中的字符数。该宏应提示用户输入一个字符串，然后在消息框中显示计数。

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第二十章 VBA 中的数组和集合

介绍

数组和集合是 VBA 编程中的基本组件，可以有效地存储和操作多个值。在本章中，我们将探讨它们的概念，学习如何声明和使用它们，了解数组索引和动态数组，并研究它们在 VBA 中应用的实际示例。

结构

在本章中，我们将讨论以下主题：

• 数组

• 声明数组

• 使用数组

• 数组索引

• 声明动态数组

• 调整动态数组的大小

目标

在本章结束时，读者将学习在 VBA 编程中使用数组和集合，学习如何声明、调整大小和高效使用数组，并探讨集合相对于数组在高级数据操作任务中的优势。

数组

数组是具有相同内在数据类型的一组按顺序索引的元素。数组的每个元素都有一个唯一的标识索引号。

对数组的一个元素进行更改不会影响其他元素。

不同类型的数组如下：

• 指定大小的数组是固定大小的数组。

• 在程序运行时可以更改大小的数组是动态数组。

• 单维数组：只有行

• 多维数组：带有行和列

声明数组

数组的声明方式与其他变量相同。

语法：

Dim name_Of_array(Size) As Data_Type

示例

单维数组：声明一个可以存储整数项目的大小为 10 行的单维数组变量。

Dim Myarray(10) As Integer

多维数组（最多 60 维）：声明一个可以存储 15 个整数项目的 3 行 5 列的多维数组变量。

Dim Myarray(3 , 5) As Integer

使用数组

使用数组可以通过以下示例解释：为了存储每个月每天的日常开支，您可以声明一个具有 31 个元素的数组变量，而不是声明 31 个变量。

数组中的每个元素包含一个值。

Sub Single_array()Dim curExpense(31) As CurrencyDim intI As IntegerFor intI = 0 to 31curExpense(intI) = 20NextEnd Sub

注意：在上面的示例中，数组索引将从 0 开始

数组索引

所有数组索引都从零开始。数组是从 0 还是 1 开始索引取决于 Option Base 语句的设置。

如果未指定 Option Base 1，则所有数组索引都从零开始。

示例

选项基础 1Sub Single_array()Dim curExpense(31) As CurrencyDim intI As IntegerFor intI = 1 to 31curExpense(intI) = 20NextEnd Sub

注意：在上面的示例中，数组索引将从 1 开始。

声明动态数组

通过声明动态数组，您可以在代码运行时调整数组的大小。

使用 Dim 语句声明一个数组，括号留空。

语法：

Dim Name_Of_Array() As Data_Type

调整动态数组大小

使用 ReDim 语句在过程内隐式声明一个数组。

在使用 ReDim 语句时，小心不要拼错数组的名称。

数组示例

Option Base 1 ' 初始化数组索引 1Sub Searchdata()Dim mycell_array() As String ' 声明动态数组 Dim a, i As Longi = 1Sheets(1).Selecta = Range("a65536").End(xlUp).Row - 1ReDim mycell_array(a) ' 重新声明数组大小 Range("a2", Range("a2").End(xlDown)).SelectFor Each mycell In Selection mycell_array(i) = mycelli = i + 1NextSheets("database").SelectRange("a2", Range("a2").End(xlDown)).Selecti = 1For i = 1 To aFor Each mycell In SelectionIf mycell_array(i) = mycell Thenmycell.EntireRow.Delete 'mycell.Offset(0, 1).Value = "found"End IfNextEnd Sub

结论

数组和集合是 VBA 编程中强大的工具，有助于管理数据集并增强代码效率。掌握这些概念，可以优化 VBA 代码，提高可读性，并有效处理复杂的数据结构。将数组和集合纳入编程技能范围，将扩展您的能力，使您能够处理更广泛的 VBA 项目。

练习

1. 声明一个动态数组，用于存储 5 名员工的日常开支。

2. 使用循环输入每位员工在指定天数内的开支。

3. 计算每位员工的总开支并显示结果。

4. 确定总开支最高的员工，并打印其姓名和相应金额。

5. 计算整个团队每天的平均开支并显示结果。

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第二十一章 VBA 中的调试和错误处理

介绍

在本章中，我们将探讨在 Visual Basic for Applications（VBA）中调试和错误处理的重要概念。调试是识别和解决 VBA 代码中的运行时错误和逻辑错误的过程。错误处理涉及实施策略来处理和管理在代码执行过程中发生的错误。通过理解这些概念并利用适当的技术，我们可以创建更健壮和可靠的 VBA 宏。

结构

在本章中，我们将讨论以下主题：

• 错误

• 错误处理

• 错误编号

• 调试宏

目标

通过本章，读者将了解有关错误、错误处理和错误编号的内容，这些内容与调试一起在 VBA 中至关重要。

错误

如果语句失败，将生成一个错误。有三种类型的错误：

• 逻辑：当宏没有给出预期结果时。这些错误可以通过改变逻辑和试错方法来处理

• 技术：当运行时出现任何语句失败时。使用 On Error 语句来处理这些错误。

• 语法：这些包括拼写错误的关键字、括号不匹配以及各种其他错误。Excel 会标记您的语法错误，直到它们被纠正之前，您无法执行您的代码。

错误处理

处理错误有三种方式：

• 每当遇到错误时，将控制转移到特定的标签或代码行。

  • On Error GoTo line / label On Error

• 立即继续执行导致运行时错误的语句后面的语句。

  • On Error Resume Next

• 禁用当前过程中的任何已启用的错误处理程序。

  • On Error GoTo 0

注意：错误处理例程不是一个子过程或函数过程。它是由一行标签或行号标记的代码部分。如果不使用 On Error 语句，任何发生的运行时错误都是致命的；也就是说，会显示错误消息并停止执行。

场景 28

打开场景 22。如果数据库工作表不存在，将会生成一个错误。修改代码以处理此错误，也就是说，你的程序应该在数据库工作表不存在时添加一个新的工作表。

注意：使用 On Error GoTo line / label

解决方案

Sub Error_handling1()On Error GoTo err_handlerDim EmpCode As Integer, next_row As IntegerDim EmpName As StringDim doj As DateDim Salary As CurrencyWorksheets("database").SelectRange("a65536").SelectSelection.End(xlUp).Selectnext_row = ActiveCell.Row + 1DoEmpCode = InputBox("输入员工代码")EmpName = InputBox("输入员工姓名")doj = InputBox("输入入职日期 mm/dd/yy")Salary = InputBox("输入员工薪水")Cells(next_row , 1).Value = EmpCodeCells(next_row , 2).Value = EmpNameCells(next_row , 3).Value = Format(doj, "MMM DD YYYY")Cells(next_row , 4).Value = Salarynext_row =next_row + 1Loop While (MsgBox("是否继续?", vbOKCancel) = vbOK)MsgBox "谢谢"Exit Suberr_handler: Worksheets.AddActiveSheet.name ="database"Range("a1").Value = "员工代码"Range("b1").Value = "员工姓名"Range("c1").Value = "入职日期"Range("d1").Value = "薪水"Resume NextEnd Sub

错误编号

每个运行时错误都有一个编号。如果您知道编号，可以通过编号捕获错误。例如，请参考以下表 21.1：

错误编号	描述
61	磁盘已满
4	应用程序定义或对象定义错误
7	内存不足
9	下标超出范围

表 21.1: 错误编号及其相应描述

尝试以下代码以查看特定错误编号的错误

Sub Show_Error():Dim ErrorNumber   For ErrorNumber = 61 To 64 ' 循环值为 61 – 64。       Msgbox Error(ErrorNumber)   Next ErrorNumberEnd Sub

场景 29

打开场景 28。如果留空输入框，您的宏将生成一个错误。按照以下方式修改代码（使用 On error resume next）

Sub Error_handling2()Dim EmpCode As Integer, next_row As IntegerDim EmpName As StringDim doj As DateDim Salary As Currency' 如果有任何错误，应继续下一行 On Error resume NextWorksheets("database").SelectRange("a65536").SelectSelection.End(xlUp).Select next_row= ActiveCell.Row + 1DoEmpCode = InputBox("输入员工代码")EmpName = InputBox("输入员工姓名")doj = InputBox("输入入职日期 mm/dd/yy")Salary = InputBox("输入员工薪水")Cells(next_row , 1).Value = EmpCode Cells(next_row , 2).Value = EmpNameCells(next_row , 3).Value = Format(doj, "MMM DD YYYY")Cells(next_row , 4).Value = Salary next_row =next_row + 1Loop While (MsgBox("是否继续?", vbOKCancel) = vbOK) MsgBox "谢谢"End Sub

调试宏

调试是找到和纠正运行时错误和逻辑错误的过程。按 F8 逐行执行代码。

调试工具栏可在以下图 21.1 中看到：

图 21.1: 调试工具栏

这里是各种工具栏按钮：

• 设计模式：打开或关闭设计模式。

• 运行子程序/用户窗体或运行宏：如果光标在过程中，则运行当前过程，如果当前活动的是用户窗体，则运行用户窗体，如果既不是代码窗口也不是用户窗体，则运行宏。

• 中断：在程序运行时停止执行并切换到中断模式。

• 重置：清除执行堆栈和模块级变量，并重置项目。

• 切换断点：在当前行设置或移除断点。

• 逐步执行：逐条执行代码。

• 逐步跳过：在代码窗口中逐个过程或语句执行代码。

• 跳出：执行当前执行点所在过程的剩余行。

• 本地窗口：显示本地窗口。

• 立即窗口：显示立即窗口。

• 观察窗口：显示观察窗口。

• 快速监视：显示当前选定表达式的当前值的快速监视对话框。

• 调用堆栈：显示调用对话框，列出当前活动的过程调用（应用程序中已启动但尚未完成的过程）。

这里有一些提示，将帮助您将错误最小化：

• 使用 Option Explicit：这将强制您为每个使用的变量定义数据类型。这将避免常见的拼写变量名错误。

• 使用缩进格式化您的代码：如果您有几个嵌套的 For...Next 循环，例如，一致的缩进将使跟踪它们变得更加容易。

• 谨慎使用 On Error Resume Next：此语句导致 Excel 忽略任何错误并继续。在某些情况下，使用此语句将导致 Excel 忽略不应被忽略的错误。您可能会有错误而自己都没有意识到。

• 使用注释：养成写注释的习惯，这样当您重新访问您的代码时，您可以理解逻辑。添加一些描述您逻辑的注释可以节省您大量的时间。

• 保持你的子程序和函数简单：将代码编写成较小的模块，每个模块都有一个明确定义的目的。

• 使用宏录制器识别属性和方法：如果您不记得属性或方法的名称或语法，录制一个宏并查看录制的代码。

• 考虑采用不同的方法：如果您在使特定例程正常工作方面遇到困难，您可能希望放弃这个想法，尝试完全不同的方法。在大多数情况下，Excel 提供了几种实现相同目标的替代方法。

• 使用调试工具栏

结论

调试和错误处理是 VBA 开发人员必备的技能。通过有效地管理错误和调试我们的代码，我们可以创建更健壮和无错误的宏。应用最佳实践，如适当的代码格式化、注释和使用可用的调试工具，将帮助我们高效地预防和解决错误。

练习

1. 打开一个新的 Excel 工作簿并导航到 Visual Basic 编辑器（VBE）。

2. 在 VBE 中，插入一个新模块来编写 VBA 代码。

3. 创建一个名为“CalculateStatistics”的子例程，不带任何参数。

4. 声明用于存储输入值和计算统计数据的必要变量。

5. 使用高级错误处理技术（如“On Error GoTo”、“On Error Resume Next”和“Err.Raise”）实现错误处理。

6. 提示用户使用“Application.InputBox”方法从工作表输入一系列数字。

7. 使用“WorksheetFunction”对象在所选范围上执行以下计算：

   1. 计算数字的总和。

   2. 计算数字的平均值。

   3. 计算范围内的最大值。

   4. 计算范围内的最小值。

8. 在单独的消息框中显示计算出的统计数据。

9. 包括全面的错误处理，以处理诸如无效输入、空选择、范围内的非数字值或除零错误等情况。

10. 通过使用不同的输入执行宏来测试它，并验证它有效地处理错误并提供准确的结果。

加入我们书籍的 Discord 空间

加入书籍的 Discord Workspace，获取最新更新、优惠、全球技术动态、新发布和与作者的会话：

discord.bpbonline.com

第二十二章 VBS 中的用户表单和用户输入

介绍

用户表单和用户输入是在 Visual Basic Scripting（VBS）中创建交互式和用户友好应用程序的重要方面。用户表单允许开发人员设计直观的界面，并捕获用户输入以实现各种目的。通过利用诸如标签、文本框、按钮等控件，VBS 开发人员可以创建动态、响应迅速的表单，从而增强整体用户体验。

在本章中，我们将探讨在 VBS 中创建用户表单的过程，包括设计表单布局、添加控件和处理事件。我们还将讨论如何将用户输入整合到您的脚本中，使您的应用程序能够响应用户操作并执行相关任务。

结构

在本章中，我们将介绍以下主题：

• 用户表单

• 创建用户表单

• 添加其他控件

• 处理控件的事件

目标

本章结束时，读者将了解用户表单以及如何创建它们，添加其他控件以及处理控件的事件。了解 VBS 中的用户表单和用户输入将使您能够构建满足用户特定需求的交互式和功能性应用程序。

用户表单

用户表单用于创建自定义对话框。参考以下图 22.1：

图 22.1：用户表单

创建用户表单

要创建用户表单，请按照以下步骤操作：

1. 点击插入菜单

2. 选择 UserForm，如图 22.2（a）所示。

3. 这将在您的工作簿中添加一个对象 UserForm1，如图 22.2（b）所示。

4. 使用属性窗口更改表单的名称、行为和外观。例如，要更改表单上的标题，设置 Caption 属性，如图 22.2（c）所示

参考图 22.2：

图 22.2：创建用户表单

添加其他控件

要添加其他控件，请按照以下步骤操作：

1. 点击工具栏中的工具箱，如图 22.3（a）所示。

2. 将控件拖放到表单上，如图 22.3（b）所示。

3. 从属性窗口更改属性，如名称、字体等，如图 22.3（c）所示

参考图 22.3：

图 22.3：添加其他控件

处理控件的事件

要处理控件的事件，请按照以下步骤操作：

1. 打开表单的代码窗口。

2. 选择表单控件。

3. 从项目资源管理器窗口中点击查看代码工具，如图 22.4（a）所示。

4. 从图 22.4（b）中选择您在表单中放置的控件。

5. 选择您控件的事件，如图 22.4（c）所示。

6. 按照 图 22.4（d）中所示编写事件代码。

图 22.4：处理控件事件

场景 30

创建一个用户表单，用于接受参与者的信息以报名培训。该表单应包含“插入”和“取消”命令按钮。单击“插入”按钮时，表单中输入的信息必须进入 Excel。单击“取消”按钮时，表单应关闭。

解决方案

按照给定的步骤：

1. 插入一个用户表单。

2. 从属性窗口更改名称和标题。

3. 表单的名称应为 USR_enrol。

4. 标题应为培训报名表。

5. 设计如 图 22.5 所示的表单：

图 22.5：场景 30 解决方案

在这里，

• 标签：显示描述性文本。

• 文本框：文本框是用户输入信息最常用的控件。

• ListBox：显示值列表并允许选择一个或多个。

• ComboBox：结合了 ListBox 和 TextBox 的功能。用户可以像使用 TextBox 一样输入新值，或者像使用 ListBox 一样选择现有值。

• 框架：创建一个功能性和视觉控制组。

• 属性：属性（��性或变量）。

• 方法：对象将执行的操作与属性。

参考以下 表 22.1：

控件	名称	属性	值
标签	LBL_ 标题	标题	Ritu Arora 学院。
标签	LBL_name	标题	姓名
标签	LBL_address	标题	地址
标签	LBL_contactno	标题	联系电话
标签	LBL_ 电子邮件	标题	电子邮件地址
标签	LBL_ 主题	标题	主题
文本框	TXT_name
文本框	TXT_address
文本框	TXT_contactno
文本框	TXT_email
文本框	TXT_companyname	文本	输入您的公司名称
框架	FRM_ 详细信息	标题	其他详细信息
组合框	CBO_subject	标题
选项按钮	OPT_company	标题	公司
选项按钮	OPT_Personal	标题	个人
命令按钮	CMD_insert	标题	插入
命令按钮	CMD_cancel	标题	取消

表 22.1：员工用户表单

结论

总之，用户表单和用户输入是 Visual Basic Scripting（VBS）的基本组成部分，允许开发人员创建交互式和用户友好的应用程序。通过利用用户表单，开发人员可以设计直观的界面，捕获用户输入，并增强整体用户体验。整合用户表单并有效管理用户输入可以极大地提高 VBS 应用程序的功能性和可用性。

练习

1. 创建一个名为“RegistrationForm”的用户表单，用于捕获活动注册的用户信息。该表单应包括以下控件：

   1. 标签：“姓名：”，“电子邮件：”，“电话：”，“活动：”，“付款方式：”

   2. 文本框：用户输入他们的姓名、电子邮件和电话号码。

   3. 组合框：包含用户可选择的事件列表。

   4. 选项按钮：用户选择他们偏好的付款方式（例如，“信用卡”，“PayPal”，“现金”）。

   5. 命令按钮：“提交”和“清除”。

你的任务是设计具有适当控件的用户表单，设置它们的属性，并处理提交和清除按钮的事件。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第二十三章高级 VBA 技术和最佳实践

介绍

在本章中，我们深入探讨了高级 VBA 技术和最佳实践，以增强 Excel 应用程序的功能和效率。我们探讨了初始化控件值、使用选项按钮、创建自定义按钮和用户表单、利用 Add-ins、实现大小写转换 Add-In 以及通过代码创建菜单等主题。通过掌握这些高级技术，读者将更深入地了解 VBA 编程，并能够构建更强大和用户友好的 Excel 应用程序。

结构

在本章中，我们将讨论以下主题：

• 给控件初始值的代码

• 选项按钮的代码

• 插入按钮的代码

• 显示用户表单的代码

• Add-ins

• 更改大小写表单的代码

• 通过代码创建菜单

目标

通过本章结束，读者将学习到关于高级 VBA 技术和最佳实践以增强其 Excel 应用程序的知识。

给控件初始值的代码

要给控件初始值，按照以下步骤进行：

1. 从项目资源管理器中选择查看代码表单。

2. 选择 UserForm 对象。

3. 选择初始化事件。

参考以下图 23.1：

图 23.1：给控件初始值

代码如下：

私有子过程 UserForm_Initialize()TXT_name.Value = ""TXT_address.Value = ""TXT_contact_no.Value = ""TXT_company_name.Value = ""CBO_subject.AddItem "MS Excel"CBO_subject.AddItem "VBA with Excel"CBO_subject.AddItem "MS Word"CBO_subject.AddItem "MS Powerpoint"CBO_subject.AddItem "MS Office"CBO_subject.AddItem "MS Access"CBO_subject.AddItem "MS Project" OPT_company.Value = TrueEnd Sub

选项按钮的代码

要应用选项按钮，请按照以下步骤进行：

1. 双击 OPT_company

   私有子过程 OPT_company_Click()

   '当用户选择此选项时，txt_companyname 文本框将可见。

   TXT_companyname.Visible = True

   结束子程序

2. 双击 OPT_personal

   私有子过程 OPT_personal_Click()

   '当用户选择此选项时，txt_companyname 文本框将不可见

   TXT_companyname.Visible = False

   结束子程序

参考以下图 23.2：

图 23.2：添加选项按钮

插入按钮的代码

参考以下图 23.3：

图 23.3：添加插入按钮

双击插入按钮

Private Sub CMD_insert_Click()' 代码查找下一个空单元格 ActiveWorkbook.Sheets("Training Enrollment").ActivateRange("a1").SelectDoIf IsEmpty(ActiveCell) = False ThenActiveCell.Offset(1, 0).SelectEnd IfLoop Until IsEmpty(ActiveCell) = True' 代码将表单中的值放入 ExcelActiveCell.Value = TXT_name.ValueActiveCell.Offset(0, 1).Value = TXT_address.ValueActiveCell.Offset(0, 2).Value = TXT_contactno.ValueActiveCell.Offset(0, 3).Value = TXT_email.ValueActiveCell.Offset(0, 4).Value = CBO_subject.Value 如果 OPT_personal.Value = True ThenActiveCell.Offset(0, 5).Value = "个人" ElseActiveCell.Offset(0, 5).Value = TXT_companyname.ValueEnd IfTXT_name.Value = ""TXT_address.Value = ""TXT_contact_no.Value = ""TXT_email.Value = ""TXT_companyname.Value = "输入您的公司名称"TXT_companyname.Visible = False CBO_subject.Value = ""OPT_company.Value = TrueEnd Sub

在插入值后清除表单，请按照以下步骤操作：

1. 双击取消按钮。

2. 然后编写以下代码：

   Private Sub CMD_cancel_Click() 卸载 me

   End Sub

显示用户表单的代码

要从 Excel 运行用户表单，插入一个模块并编写一个宏：

Sub Enrol_form()USR_enrol.ShowEnd Sub

插件

插件是单独的实用程序。它们为软件提供一些额外的功能。插件的扩展名是.XLAM。在 Excel 中，我们有一些现成的插件，如求解器、分析工具包、条件求和向导等。

场景 31

创建一个插件，根据用户选择的选项将大小写转换为大写/小写/首字母大写。

解决方案

按照给定的步骤操作：

1. 设计一个插件表单，如图 23.4 所示：

   

   图 23.4：设计插件表单

2. 打开一个新的 Excel 工作簿。

3. 打开 Visual Basic 编辑器。

4. 插入一个用户表单，并将其命名为 Changecase。

5. 给标题改变大小写。

6. 在表单上拖动对象。

7. 设计如下所示的表 23.1：

   对象	名称	属性	值
   引用编辑	Refselect
   选项按钮	Optupper	标题	大写
   选项按钮	Optlower	标题	小写
   选项按钮	Optproper	标题	首字母大写
   命令按钮	Cmdexit	标题	退出

   表 23.1：选项按钮

8. 打开 Changecase 表单的代码窗口

9. 为不同的控件编写代码

更改大小写表单的代码

双击 OPTupper 控件。

Private Sub OPTupper_Click()'当用户选择此选项时，将转换为大写 Dim rng As Range, wscell As RangeSet rng = Range(refselect)For Each wscell In rngwscell.Value = UCase(wscell.Value)NextEnd Sub

参考以下图 23.5：

图 23.5：小写

现在，双击 OPTlower 控件。

Private Sub optlower_Click()'当用户选择此选项时，将转换为大写 Dim rng As Range, wscell As RangeSet rng = Range(refselect)For Each wscell In rngwscell.Value = LCase(wscell.Value)NextEnd Sub

参考以下图 23.6：

图 23.6：ProperCase

双击 opt_upper 控件并编写以下代码

Private Sub optproper_Click()'当用户选择此选项时，将转换为大写 Dim rng As Range, wscell As RangeSet rng = Range(refselect)For Each wscell In rngwscell.Value = Application.WorksheetFunction.Proper(wscell.Value)NextEnd Sub

双击 cmdexit 控件

Private Sub cmdExit_Click()EndEnd Sub

插入一个模块并编写一个宏来显示 Changecase 表单

Sub show()Changecase.showEnd Sub

通过代码创建菜��

参考以下代码：

Sub auto_open()'此代码将在现有菜单栏中创建一个新菜单，其中将有一个菜单项，即 Changecase Add-InsDim newmenu As CommandBarPopupDim menuitem As CommandBarButtonSet newmenu = CommandBars(1).Controls.Add(Type:=msoControlPopup, before:=CommandBars(1).Controls("help").Index)newmenu.Caption = "E&xtras"'当选择 Changecase 菜单项时，应显示 changecase 表单 Set menuitem = newmenu.Controls.Add(Type:=msoControlButton) menuitem.Caption = "&Change Case" menuitem.OnAction = "show"End SubSub auto_close()'当从 Excel 中移除加载项时，将删除菜单 CommandBars(1).Controls("extras").DeleteEnd Sub

在这里，

• Auto_Open(): 这是在打开工作簿时触发的第一个事件。

• Auto_Close(): 这是在打开工作簿时触发的最后一个事件。

结论

在本章中，我们探讨了可以显著改进 Excel 应用程序功能和用户体验的高级 VBA 技术和最佳实践。从初始化控件值到处理选项按钮，创建自定义按钮和用户表单，利用加载项，并通过代码创建菜单，我们涵盖了广泛的主题。通过应用这些技术并遵循概述的最佳实践，读者可以创建更强大、高效和用户友好的 Excel 应用程序。掌握这些高级技术后，读者将能够将他们的 VBA 技能提升到更高水平。

练习

让我们考虑一个基于示例的练习，结合了本章讨论的几种高级 VBA 技术。

场景：您正在 Excel 中开发一个项目管理工具。该工具允许用户输入项目细节，跟踪进度并生成报告。您的任务是通过实现以下功能来增强该工具：

1. 添加一个名为“TaskForm”的用户表单，允许用户输入任务细节，包括任务名称、分配人员、开始日期、结束日期和状态（例如，“未开始”，“进行中”，“已完成”）。

2. 在 TaskForm 中实现验证检查，确保所有必填字段都填写完整，并且结束日期不早于开始日期。如果任何验证失败，显示适当的错误消息。

3. 在 TaskForm 上创建一个自定义的“插入”按钮，将输入的任务详情添加到指定的工作表，如“ProjectTasks”。确保每个新任务都插入到新行，并在成功插入后清空输入字段。

4. 使用 VBA 代码实现一个菜单系统。在 Excel 菜单栏中创建一个名为“项目管理”的新菜单项。在“项目管理”菜单下，添加选项以打开 TaskForm，显示所有任务列表，并生成一个汇总报告。

5. 开发一个汇总报告功能，计算并显示任务总数、每个状态类别中的任务数以及已完成任务的百分比。汇总报告应显示在名为“TaskSummary”的新工作表中。

6. 实现一个名为“TaskUtilities”的插件，提供额外的任务管理功能，如按名称或日期对任务进行排序，按状态筛选任务，并生成专门的报告。通过在不同的工作簿中使用该插件来测试它，并验证其功能。

7. 在整个项目中应用最佳的代码组织、错误处理和优化实践，以确保清晰高效的 VBA 代码。

加入我们书籍的 Discord 空间

加入书籍的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：

discord.bpbonline.com

第二十四章 使用 VBA 构建自定义插件

介绍

在本章中，我们将探讨在 Excel 中使用 Visual Basic for Applications（VBA）构建自定义插件的过程。插件是可以集成到 Excel 中以增强其功能并简化工作流程的附加功能或工具。我们将涵盖诸如使用密码保护插件和有效使用插件等主题。

结构

在本章中，我们将讨论以下主题：

• 使用密码保护您的插件

• 使用插件

目标

在本章结束时，读者将学会如何使用密码保护他们的插件以确保安全性，以及如何有效利用插件来增强 Excel 的功能。

使用密码保护您的插件

要使用密码保护您的插件，请按照以下步骤操作：

1. 折叠项目的所有对象（插入表单和模块以用于插件的 Excel 文件）。

2. 右键单击该项目。

3. 选择 VBA 项目属性。

4. 选择“保护”选项卡。

5. 选择“锁定以查看”。

6. 设置密码。

7. 点击“确定”。

参考图 24.1：

图 24.1：添加密码

1. 一旦您准备好添加插件的表单和模块，切换到 Excel 环境。

2. 将当前文件保存为插件类型（.xlam 扩展名），如图 24.2 所示：

   

   图 24.2：保存当前文件

使用插件

要使用插件，请按照以下步骤操作：

1. 点击“Office”按钮。

2. 点击“Excel 选项”。

3. 选择“插件”。

4. 点击“前往…”按钮，如图 24.3 所示：

   

   图 24.3：选择插件

5. 浏览以找到您的插件，如图 24.4 所示：

   

   图 24.4：定位您的插件

结论

总之，使用 VBA 构建自定义插件使用户能够扩展 Excel 的功能并简化其工作流程。通过密码保护插件可以确保其安全性。有效利用插件可以提高生产力，使用户能够在 Excel 中访问额外的功能和工具。

练习

1. 打开 Microsoft Excel 并创建一个新的工作簿。

2. 创建一个简单的 VBA 宏，用特定字体、字体大小和背景颜色格式化所选单元格。

3. 将 VBA 宏保存为具有.xlam 扩展名的插件。

4. 使用密码保护插件以确保其安全性。

5. 通过在 Excel 中安装并使用插件来格式化不同工作表中的单元格来测试插件。

加入我们书籍的 Discord 空间

加入本书的 Discord Workspace，获取最新更新、优惠、全球技术动态、新发布和与作者的交流：

discord.bpbonline.com

第二十五章 ChatGPT 与 Excel

介绍

在本章中，我们将探讨如何将 ChatGPT 与 Excel 集成。Excel 是一个强大的数据组织和分析工具，而 ChatGPT 是一个可以在各种 Excel 相关任务中提供帮助的 AI 语言模型。通过结合这些工具，您可以利用两者的功能来增强您的 Excel 体验。

结构

在本章中，我们将讨论以下主题：

• 使用 ChatGPT 与 Excel

目标

通过本章结束时，读者将学会如何将 ChatGPT 与 Excel 集成，以实现各种目的，进一步发挥 Excel 的功能，并提高在数据分析、公式故障排除和格式设置等任务中的效率。

使用 ChatGPT 与 Excel

Excel 是一个强大的组织和分析数据的工具，另一方面，ChatGPT 是一个可以在包括 Excel 在内的各个领域提供帮助的 AI 语言模型。以下是您可以一起使用 Excel 和 ChatGPT 的一些方式：

• 请求 Excel 函数和公式的帮助：您可以向 ChatGPT 请求帮助解决您遇到困难的特定 Excel 函数或公式。只需描述问题或提供示例，ChatGPT 可以提供解决方案或提供逐步指南，如图 25.1 所示：

  

  图 25.1：向 ChatGPT 请求 Excel 函数和公式的帮助

• 获取使用 Excel 的技巧和窍门：ChatGPT 可以为更有效地使用 Excel 提供技巧和窍门。例如，您可以要求快捷方式、格式设置技巧或自动化任务的方法，如图 25.2 所示：

  

  图 25.2：向 ChatGPT 询问如何使用 Excel 的技巧和窍门

• 从数据分析中获取见解：Excel 可用于分析数据并生成见解，如图 25.3 所示。ChatGPT 可以帮助您解释分析结果或建议新的查看数据的方式，如图 25.4 所示：

  

  图 25.3：Excel 上的数据

转到 ChatGPT，在聊天中写下如何分析这些数据。回应如图 25.4 所示：

图 25.4：ChatGPT 如何分析这些数据

• 在 Excel 电子表格上进行协作：您可以使用 ChatGPT 与他人在 Excel 电子表格上进行协作。例如，您可以要求 ChatGPT 建议对电子表格进行更改或改进，或者就数据的特定方面提供反馈。图 25.5 展示了一个带有数据的电子表格：

  

  图 25.5：带有数据的电子表格

请求 ChatGPT 对图 25.5 中显示的数据透视表提供反馈，生成以下回应：

图 25.6：ChatGPT 的回应

• 对于在 Excel 中编码：您可以使用 ChatGPT 生成任何代码；例如，使用 VBA 在 Excel 中将数字转换为单词。

要在 Excel 中使用 VBA 将单词转换为卢比数字，您可以创建一个自定义函数，该函数使用 Microsoft Speech 对象库将文本转换为语音，然后从所说的文本中提取数字。以下是您可以执行此操作的示例：

1. 打开一个新的 Excel 工作簿，按 Alt + F11 打开 VBA 编辑器。

2. 在编辑器中，转到插入 | 模块，创建一个新模块。

3. 在新模块中，复制并粘贴图 25.7 中显示的代码：

   

   图 25.7：要复制到新模块的代码

4. 保存模块并返回到您的 Excel 工作表。

5. 在您想要将单词转换为卢比数字的单元格中，输入以下公式：

   =WordsToNumbers(“仅两千五百卢比五十派仅”)

6. 按 Enter 键计算结果。

7. 此公式将单词“仅两千五百卢比五十派仅”转换为数字 2500.50。您可以将示例文本替换为您要转换的实际文本。

总的来说，Excel 和 ChatGPT 的结合可以帮助您更高效、更有效地处理数据，并为您的工作提供新的见解和视角。假设您有一张包含公司销售数据的电子表格。您想要计算每个月的总销售额，并创建一个图表来可视化数据。为此，您需要按照以下步骤操作：

1. 首先，您可以使用 Excel 创建一个新列，并使用 MONTH 函数从销售数据中提取日期的月份。

2. 然后，您可以使用 SUMIFS 函数计算每个月的总销售额。您需要为函数指定条件，以便对每个月的销售数据进行求和。

3. 一旦您有了总数，您可以创建一个图表来可视化数据。选择您的数据，转到插入选项卡，并选择您想要创建的图表类型。

4. 如果您不确定要使用哪种图表类型或如何格式化图表，可以向 ChatGPT 寻求建议。例如，您可以询问“用于按月可视化销售数据的最佳图表类型是什么？”或“如何使我的图表更具视觉吸引力？”

5. ChatGPT 可以根据最佳实践和设计原则提供建议。如果您不确定如何使用 Excel 中的特定功能或设置，也可以向 ChatGPT 询问。参考图 25.8 作为示例：

   

   图 25.8：向 ChatGPT 寻求有关 Excel 中特定功能或设置的帮助

总的来说，使用 Excel 和 ChatGPT 一起可以帮助您更高效、更有效地处理数据，并为您的工作提供新的见解和视角。

结论

总之，将 ChatGPT 与 Excel 集成为用户提供了有价值的帮助和指导。通过利用 Excel 函数和公式的强大功能，再加上 ChatGPT 的能力，用户可以提高在数据分析、公式故障排除和格式设置等任务中的效率。ChatGPT 还可以提供有关更有效使用 Excel 的技巧、窍门和见解。此外，与 ChatGPT 合作在电子表格上进行协作并从 ChatGPT 获取反馈，可以增强使用 Excel 的整体体验。通过结合这些工具，用户可以优化其数据管理和分析工作流程，从而提高生产力和做出更好的决策。

练习

1. 打开 Microsoft Excel 并创建一个新工作簿。

2. 在工作表中输入以下示例销售数据

   日期	销售额
   01-01-2023	$500
   05-01-2023	$300
   10-02-2023	$750
   15-02-2023	$600
   03-03-2023	$900
   07-03-2023	$400

表 25.1：示例销售数据

创建一个执行以下任务的 VBA 宏：

1. 计算每个月的总销售额。

2. 确定销售额最高的月份。

3. 在消息框中显示计算出的总额和销售额最高的月份。

加入我们书籍的 Discord 空间

加入该书的 Discord 工作区，获取最新更新、优惠、全球科技动态、新发布内容以及与作者的交流：

discord.bpbonline.com

标签：语句,精通,Sub,代码,Excel,VBA,Value,ChatGPT
From： https://www.cnblogs.com/apachecn/p/18114988