在本章中，我们将探讨在Excel中将按钮分配给宏的过程。通过在快速访问工具栏和Excel工作表上创建按钮，我们可以自动化任务，提高效率并简化杂操作。加入我们，深入探索Excel自动化的世界，并发现按钮-宏集成的力量。
结构
在本章中，我们将讨论以下主题：
在快速访问工具栏上创建按钮
修改菜单或按钮
在Excel工作表上创建按钮
编辑记录的宏
目标
通过本章结束时，读者将了解如何在快速访问工具栏上创建按钮以快速访问宏，并学习如何修改菜单或按钮以自定义其外观和功能。读者还将能够探索在Excel工作表上直接创建用于特定任务的按钮的过程，并熟练地编辑记录的宏以增强自动化并根据特定要求进行定制。
要在快速访问工具栏上创建按钮，请按照以下步骤操作：
点击Office按钮。
点击Excel选项。
转到自定义选项卡。
在“从下拉框中选择命令”下，选择宏。
将您的宏添加到自定义快速访问工具栏。
参考以下图15.1：
图15.1：在快速访问工具栏上创建按钮
每当您想要创建新按钮，编辑现有按钮或从快速访问工具栏中删除任何按钮时，请按照以下步骤操作：
点击修改…按钮。
选择您喜欢的图标。
提及显示名称。
点击确定。
参考以下图15.2：
图15.2：修改菜单或按钮
场景4
创建一个宏，该宏将提取按区域划分的薪水总和（使用“薪水工作表”）。单击“小计”按钮后，应在新工作表上添加按区域划分的小计，如下所示图15.3：
图15.3：场景4
要解决此场景，请按照以下步骤操作：
开始录制（命名为Subtotal_Macro）
选择薪水表（源数据）
选择A1单元格（数据库从A1开始）
按照图15.4中显示的方式对区域进行排序：
图15.4：场景4解决方案
注意：排序必须按照小计的要求进行。错误的排序将导致错误的小计。
按照图15.5中显示的方式对区域进行小计，并按照给定的步骤操作：
在数据选项卡上，转到大纲组。
点击小计按钮。
也选择区域。
选择求和函数（根据要求可以选择其他函数）
选择“工资”字段。
点击“确定”。
参考图15.5：
图15.5：场景4解决方案
点击大纲的第二级（只显示小计）
选择数据。
按下Alt+;（分号）键组合以从选择中选择可见单元格。
复制所选内容（Ctrl+C）。
插入新工作表（Shift+F11）。
粘贴复制的数据（Ctrl+V）。
转到工资表（源数据）。
删除小计，使用以下步骤：
转到“数据”选项卡。点击“小计”。
点击“RemoveAll”按钮。
激活上一个工作表（Ctrl+PageUp）。
停止录制
参考以下图15.6：
图15.6：场景4解决方案
在Excel工作表上创建一个按钮，请按照以下步骤进行：
激活工资表。
转到“控件”组。
选择按钮（表单控件），如图15.7（b）所示
分配小计宏，如图15.7（c）所示。
更改标题（右键单击并编辑文本），如图15.7（d）所示。
参考以下图15.7：
图15.7：在Excel工作表上创建按钮
场景5
创建一个宏，将从不同部门和区域提取记录。用户将输入所需的部门和区域，然后点击筛选按钮，如图15.8所示：
图15.8：场景5
注意：上述宏将自动化高级筛选的工作。
按照以下步骤进行：
为高级筛选创建条件范围。
开始录制。
给予名称“Filter_Macro”。
存储在本工作簿中。
点击数据库的标题（单元格A5）。
进行高级筛选。
点击“数据”选项卡。
点击“高级”。
参考图15.9：
图15.9：场景5解决方案
提供列表范围，即数据库范围，如图15.10所示。
提供条件范围。
停止录制。
从“开发人员”选项卡|“控件”组创建一个按钮。
将筛选宏分配给此按钮。
参考图15.10：
图15.10：场景5解决方案
有时记录的宏可能无法完全自动化。然后你需要编辑你记录的宏。或者，你可能想在记录的宏中添加一些操作，然后你可以编辑你的宏。
例如，在场景5中，不要在Excel中更改条件，而是让你的宏询问区域和部门。用户将输入然后点击“确定”。然后，宏将执行筛选。
要编辑你记录的宏，请按照以下步骤进行：
转到“工具”。
点击“宏”。
打开VisualBasic编辑器。
打开你的宏的代码窗口。
场景6
打开场景5并修改代码以执行以下操作。用户应该获得一个InputBox来输入所需的部门和地区，然后单击筛选按钮。参考图15.11：
图15.11：场景6
要编辑您录制的宏，请执行以下步骤：
转到工具。
选择宏。
选择Filter_Macro。
点击编辑，如图15.12所示。
打开您的宏的代码窗口。
参考图15.12：
图15.12：场景6解决方案
参考TrainingFile3.xls。
宏定义以关键字Sub开头，并以EndSub结束。
Inputbox是一个用于从最终用户获取输入的函数。
范围是一个类。
注意：我们将稍后详细讨论编写过程。
参考图15.13：
图15.13：场景6解决方案
场景7
创建一个宏来从txt文件（sales.txt）导入数据并设计一个数据透视表，显示按产品和月份总销售额。此宏将自动化从txt文件导入数据并创建一个数据透视表报告（参考TrainingFile4.xls）：
表15.1：示例数据
要解决这种情况，请按照以下步骤操作：
开始录制（命名为import_txt）
点击数据菜单。
选择导入外部数据|导入数据。
选择sales.txt。
选择数据类型为分隔符，如图15.14所示（1）。
点击下一步
选择分隔符（逗号），如图15.14所示（2）。
点击下一步。
图15.14：场景7解决方案
选择每个数据的类型，如图15.15所示。
点击完成。
选择现有工作表选项，如图15.15所示。
现在使用导入的数据创建一个数据透视表，并将其放在新工作表中。
图15.15：场景7解决方案
场景8
参考TrainingFile4.xls：
场景-8
练习1
创建一个宏（Report_title），它将在现有工作簿的单元格地址单元格内容中添加一个新工作表，并包含以下详细信息。
A2：您的公司名称
A3：标题为“每日报告”
A4：今天的日期
A6：序号
B6：产品
C6：销售数量
按照给定的步骤解决问题：
点击“工具”|“宏”。
选择记录新宏。
将宏的名称命名为“报告标题”。
分配快捷键。
选择存储位置。
在描述框中添加详细信息。
添加一个空白工作表（Shift+F11或插入菜单|工作表）
点击A2并输入你公司的名称。
点击A3，输入标题为“每日报告”。
点击A4，输入“=today()”。
从A6到C6输入“序号”、“产品”和“销售数量”
练习2
创建一个宏，添加签名，即您的姓名和职务在数据末尾。
注意：假设工作表的格式是练习1。
这个宏总是在当前工作表之前添加一个工作表。修改该宏，使其在当前工作表之后添加新工作表。
将宏的名称命名为“汇总”。
分配一个快捷键。
选择存储宏的位置。
点击单元格A6。
使用Ctrl+↓到达数据末尾。
点击“相对引用”。
现在向下移动3行，输入你的姓名和职务。
关闭相对引用。
修改语句“Sheets.add”为“sheets.addafter:=activesheet”。
结论
在本章中，我们讨论了在Excel中为宏分配按钮的主题。我们学习了如何在快速访问工具栏上创建按钮，并通过修改菜单或按钮来自定义它们。此外，我们探讨了直接在Excel工作表上创建按钮的过程。我们还讨论了编辑记录的宏以增强其功能的过程。
练习
创建一个宏，提取按地区汇总的工资总和，并将其添加到新工作表中。
创建一个宏，根据部门和地区筛选记录。
从文本文件导入数据并创建数据透视表报告。
修改现有宏中的文件选择和变量长度数据的代码。
练习创建一个宏，在工作表中添加每日报告标题。
练习创建一个宏，在数据末尾添加签名。
加入我们书籍的Discord空间
加入书籍的DiscordWorkspace，获取最新更新、优惠、全球技术动态、新发布和作者会话：
本章将介绍ExcelVBA编程中函数和子例程的概念。它解释了两者之间的区别，然后深入探讨在模块中使用VisualBasicEditor编写代码。本章还涵盖了用于控制过程内代码执行流程的分支技术。
编写过程
VisualBasicEditor
插入模块
在模块内编写代码
子过程
函数过程
分支过程
学习完本章后，读者将能够编写过程并了解VisualBasicEditor，以便插入模块、编写代码等。读者还将了解子过程、函数过程以及分支过程。
您可以为记录的每个操作编写代码。所有过程都写在一个模块内。
要编写代码，您需要打开VisualBasicEditor。打开VisualBasicEditor的快捷键是Alt+F11。
模块是一组过程。有两种类型的过程：
子过程：子过程用于自动化Excel操作。子过程是在Sub和EndSub块之间封装的代码单元。没有任何参数的子过程是一个宏。
函数过程：函数用于自动化任何复杂计算。函数过程在Function和EndFunction块之间封装。
子过程和函数过程之间的区别在下表表16.1中有所探讨：
表16.1：过程之间的区别
这里有一些你可以记住的要点：
两种过程可能有或没有参数。
没有参数的子过程是一个宏。
所有宏都是过程，但并非所有过程都是宏。
下图图16.1显示了VisualBasicEditor中的代码窗口：
图16.1：VisualBasicEditor中的代码窗口
VisualBasicEditor中代码窗口的不同部分如下：
项目资源管理器：它显示了项目（Excel工作簿）的分层列表以及每个项目包含和引用的所有项目。
属性窗口：它列出了所选对象的设计时属性及其当前设置。您可以在设计时更改这些属性。当选择多个控件时，属性窗口包含所有所选控件共有的属性列表。
代码窗口：使用代码窗口编写、显示和编辑VisualBasic代码。您可以打开与模块数量相同的代码窗口，以便轻松查看不同形式或模块中的代码，并在它们之间复制和粘贴。
图16.2更详细地探讨了项目资源管理器：
查看对象：显示所选项目的对象窗口，一个现有文档或用户表单。
切换文件夹：隐藏和显示对象文件夹，同时仍显示其中包含的各个项目。
列表窗口：列出所有加载的项目及每个项目中包含的项目。
属性是任何对象的特征。属性窗口显示了所选对象的属性，如图16.2所示：
图16.2：项目资源管理器和属性窗口
现在让我们来看看代码窗口的不同部分（参考图16.3）：
对象框：显示当前项目中对象的列表。
过程窗口：包含当前模块的所有过程或所选对象的事件。
过程视图：一次只显示一个过程。
完整模块视图：显示当前模块中的所有过程。
参考以下图16.3：
图16.3：代码窗口
项目资源管理器键盘快捷键
现在让我们来看看各种键盘快捷键：
回车+：打开列表中的所选文件，或展开和折叠列表以显示其子条目。
SHIFT+ENTER：打开所选文件的代码窗口。
F7+：打开所选文件的代码窗口。
SHIFT+F10：查看快捷菜单。
HOME+：选择列表中的第一个文件。
END+：选择列表中的最后一个文件。
右箭头+：展开列表，然后每次按下时选择列表中的子条目。
左箭头+：选择列表中的子条目，然后每次按下时上移动列表，直到子条目列表折叠为文件夹。
上箭头+：逐个向上移动列表中的条目。
下箭头+：逐个向下移动列表中的条目。
要插入模块，请按照给定步骤进行：
选择要存储过程的书籍，如图16.4（1）所示。
选择“插入”菜单，然后选择“模块”，如图16.4（2）所示。
已添加Module1。你可以通过属性窗口更改名称，如图16.4（3）所示。
参考图16.4：
图16.4：插入模块
要在模块内编写代码，请按照以下步骤进行：
双击要在其中编写过程代码的模块，如下所示的图16.5（a）。
编写您的过程代码，如下所示的图16.5（b）。
参考以下图16.5：
图16.5：在模块内编写代码
子过程是一系列由Sub和EndSub语句包围的VisualBasic语句，执行操作但不返回值。
子过程可以接受参数，如常量、变量或由调用过程传递的表达式。
如果一个子过程没有参数，子语句必须包含一对空括号
宏
宏如下所示：
图16.6包含一个宏：
图16.6：宏
函数过程是一系列由Function和EndFunction语句包围的VisualBasic语句。
函数过程类似于子过程，但函数还可以返回一个值。函数过程可以接受由调用过程传递给它的参数。
如果一个函数过程没有参数，其函数语句必须包含一对空括号。函数通过在过程的一个或多个语句中为其名称赋值来返回一个值。
例如，
参考以下图16.7：
图16.7：用户定义函数
编写函数的语法
注意：参数是您希望从最终用户那里获得以计算结果的输入。
两个过程可能有或没有参数：
通过值传递：如果通过值传递参数，则被调用过程仅接收从调用过程传递的变量的副本。如果被调用过程改值，则更改仅影响副本而不影响调用过程中的变量。
通过引用传递：如果在调用过程时通过引用传递参数，则过程可以访问内存中的实际变量。因此，过程可以更改变量的值。默认情况下，参数是通过引用传递的。
场景9
编写一个函数来计算利润，其中利润是销售价格和成本价格的差额。利润函数需要两个参数，即成本价格和销售价格。
参考TrainingFile5.xls
如果你想根据条件的值运行一段代码块，可以使用以下决策结构。
如果...那么...Endif
如果...那么...否则...Endif
如果...那么...否则如果...那么...否则...Endif
选择Case…EndSelect
使用如果...那么...Endif
单个条件并运行单个语句或一组语句。
使用如果...那么...否则...Endif
单个条件，但根据条件的结果运行两个不同的语句或语句块。
使用If...Then...Elseif…Then…Else…Endif或SelectCase…End
选择多个条件并运行多个语句块中的一个。
场景10
编写一个函数来检查人是否有资格投票。为了检查资格，Vote函数需要年龄作为参数。
参考培训文件5.xls
场景11
编写一个函数根据基本工资找到员工的等级（使用IfElseif）：
等级工资
C8000–15000
B15000–25000
A>=25000
场景12
编写一个函数根据等级找到奖金（使用SelectCase）
等级奖金
A25000
B20000
C15000
D10000
场景13
编写一个函数来根据地区计算津贴。如果地区是东部或西部，则津贴为5%，否则为10%。使用OR运算符来检查多个条件。
OR运算符
OR运算符用于对两个表达式执行逻辑合取。如果任一表达式结果为真，则结果为真。
场景14
编写一个函数来根据地区计算津贴。如果地区是东部且工资>10000，则为5%，否则为10%。
使用AND运算符来检查多个条件。
AND运算符
用于对两个表达式执行逻辑合取。AND如果所有表达式结果为真，则结果为真。
函数和子程序是VBA中强大的工具，帮助您自动化任务并在Excel中执行计算。通过了解如何编写过程，使用VisualBasicEditor，并应用分支技术，您可以增强您的VBA编程技能，并创建更高效和动态的Excel应用程序。
编写一个函数来计算矩形的面积，给定其长度和宽度。
创建一个子程序来根据特定条件格式化一系列单元格，例如突出显示值高于某个阈值的单元格。
开发一个将华氏温度转换为摄氏温度的函数。
编写一个子程序，将数据列按升序排序。
创建一个函数来计算给定数字的阶乘。
加入书籍的Discord工作区，获取最新更新、优惠、全球科技动态、新发布内容以及与作者的交流：
在VisualBasicforApplications（VBA）中，条件语句用于根据特定条件在代码中做出决策。它们允许您根据逻辑表达式的结果执行不同的代码块。在本章中，我们将重点介绍VBA中两种常用的条件语句：选择情况和如果…那么结束如果。
如果…那么结束如果
选择情况
选择情况Vs.如果…那么结束如果
通过本章的学习，读者将了解VBA中选择情况和如果…那么结束如果语句之间的区别，并学会在不同场景中正确使用它们。
在VBA中，“如果…那么结束如果”语句用于评估条件并在条件为真时执行一段代码块。它还可以与“否则如果”和“否则”子句结合使用以处理多个条件。以下是“如果…那么结束如果”语句的基本语法示例：
上述示例中的“条件”和“条件2”是布尔表达式，其结果为真或假。
需要注意的是，“如果…那么结束如果”语句可以嵌套在其他控制结构中，如循环中，并且可以与其他语句和关键字结合使用，以在您的VBA代码中创建更复杂的逻辑。
示例
如果分数等于或大于90，则显示“等级：A”。如果在80到89之间，则显示“等级：B”，依此类推。如果分数不符合任何指定条件，则显示“等级：F”。
VBA中的选择情况语句提供了一种简洁和结构化的方式来处理多个条件，并根据单个表达式的值执行不同的代码块。以下是其语法和用法的全面解释：
在这个示例中，程序提示用户输入一个水果名称。然后，SelectCase语句评估输入并根据所选水果显示相应的消息。如果水果是“苹果”，它会显示有关其健康选择的消息。如果是“香蕉”，它会提到其钾含量。如果是“橙子”或“柑橘”，它会突出柑橘的好处。对于任何其他输入，它会显示一个通用消息。
SelectCaseVs.If…EndIf
当将一个表达式与几个不同的值进行比较时，使用SelectCase语句作为If...Then...Else语句中ElseIf的替代方法。
虽然If...Then...Else语句可以为每个ElseIf语句评估不同的表达式，但SelectCase语句仅在控制结构的顶部评估一次表达式。
在本章中，我们探讨了VBA中SelectCase和If...EndIf语句之间的区别。我们了解到，当您有多个条件要评估针对单个表达式时，SelectCase是一个有用的替代方案。它简化了您的代码并提高了可读性。然而，当您有不同且无关的条件要考虑时，If...EndIf语句更合适。
编写一个VBA程序，询问用户输入一周中的一天（从1到7的数字），并显示相应的星期几名称。使用SelectCase和If...EndIf语句来实现该程序。
编写一个VBA程序，询问用户输入一个数字，并确定它是正数、负数还是零。使用SelectCase和If...EndIf语句来实现该程序。
加入本书的Discord工作空间，了解最新更新、优惠、世界各地的技术动态、新发布和作者的会议：
在本章中，我们将涵盖以下主题：
变量和常量
变量和常量的数据类型
消息框和输入框
选择和激活单元格
选择和激活行和列
与工作表一起工作
与工作簿一起工作
与应用程序对象一起工作
现在让我们学习关于变量和常量。
变量
变量的特点如下：
变量是一个命名的存储位置，其中包含在程序执行过程中可以修改的数据。
每个变量都有一个名称，用于在其范围内唯一标识它。
可以指定或不指定数据类型。
变量名称：
必须以字母字符开头，
在相同范围内必须是唯一的，
不能超过255个字符，并且
常量
在程序执行过程中保持恒定值的命名项目。常量可以是字符串或数字文字。
DIMname_of_variableAStype_of_variable
例如
DimstrNameAsString
DimintXAsInteger
DimintX，intYAsInteger
Constname_of_variableAStype_of_variable=constantvalue
ConstconAgeAsInteger=34
表18.1显示了数据类型的各种范围：
表18.1：数据类型
使用OptionExplicit语句
Msgbox函数在对话框中显示消息，等待用户点击按钮，然后返回一个整数，指示用户点击了哪个按钮。
InputBox函数在对话框中显示提示，等待用户输入文本或点击按钮，然后返回一个包含文本框内容的字符串。
例如：
当你使用MicrosoftExcel时，通常会选择一个单元格或多个单元格，然后执行操作，比如格式化单元格或在其中输入值。
参考表18.2，编写各种操作的代码：
表18.2：各种操作的代码
有时您需要选择特定的行和列，然后执行操作。
要做这个，请写下面表18.3中显示的代码：
表18.3：各种操作的代码
许多时候，您需要选择特定的工作表，或插入新工作表，重命名工作表等。请参考表18.4：
表18.4：各种操作的代码
有时您需要处理不同的工作簿。请参考表18.5：
表18.5：各种操作的代码
有时为了忽略不同的Excel消息，您需要使用应用程序对象，如下所示的表18.6：
表18.6：各种操作的代码
场景15
创建一个宏，应接受人员的姓名和城市，并将其存储在Excel工作表的单元格A1和B1中。如果用户输入Mumbai作为城市，则字体颜色必须为红色。使用InputBox函数从用户那里获取输入。使用MsgBox函数显示结果。
场景16
创建一个名为Data_Entry的宏。接受一个人的员工编号、姓名、入职日期和工资。将这些值插入“数据库”工作表中。每条新记录必须存储在最后一条记录之后。
加入本书的Discord工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：
在本章中，我们将深入探讨VisualBasicforApplications（VBA）中的循环结构主题。循环是强大的工具，可以使代码重复执行，提高效率并自动化任务。本章探讨了不同类型的循环，如Do...Loop、For...Next和ForEach...Next循环，以及基于特定事件运行的自动执行的宏。
使用循环（重复动作）
使用Do…Loop语句
使用For…Next语句
使用ForEach…Next语句
自动执行的宏
通过本章结束时，读者将学习VBA中的循环结构，如Do...Loop、For...Next和ForEach...Next，以及实际示例的实现。
循环允许您重复运行一组语句。一些循环重复语句直到条件为False；其他循环重复语句直到条件为True。还有一些循环重复语句特定次数或对集合中的每个对象重复。
选择要使用的循环
有各种循环可以使用，例如：
Do…Loop：在条件为True时循环。
For…Next：使用计数器运行指定次数的语句。
ForEach…Next：为集合中的每个对象重复一组语句。
您可以使用Do...Loop语句无限次运行一组语句。这些语句在条件为True时重复，或者直到条件变为True。
语法：
Do[{While|Until}条件]
[语句]
[退出Do]
循环
在条件为真时重复语句
在Do...Loop语句中，有两种使用While关键字检查条件的方式您可以在进入循环之前检查条件，或者您可以在循环至少运行一次后检查条件。
在进入循环之前检查条件
在进入循环之前检查条件的语法是：
DOWHILE(条件)
要重复的代码
在循环至少运行一次后检查条件
在循环至少运行一次后检查条件的语法是：
DO
LOOPWHILE(条件)
场景17
编写一个接受和验证用户名的代码。不应允许空名称。参考培训文件5.xls：
参考图19.1：
图19.1：场景17
注意：Trim函数会删除单词开头和结尾的空格。
您可以使用For...Next语句重复执行一组语句特定次数。
For循环使用一个计数器变量，其值在每次循环内部重复时增加或减少。
对于计数变量=初始值到最终值STEP步长值
下一个
场景18
创建一个名为fill_series的宏，以显示从1到10的数字（从单元格A1开始）。
Subfill_series()
Range("A1").Select
对于fill_val=1到10
ActiveCell.Value=fill_val
ActiveCell.Offset(1,0).SelectNext
EndSub
Offset函数()用于指向或引用对象的上、下、左或右。
OFFSET(行，列)
Activecell.Offset(1,0).select：这将选择Activecell下面1行，右边0列的单元格，
Activecell.Offset(0,1).select：这将选择Activecell右边0行，1列的单元格，
Activecell.Offset(-1,0).select：这将选择Activecell上面1行，右边0列的单元格，
Activecell.Offset(0,-1).select：这将选择Activecell下面0行，左边1列的单元格。
ForEach...Next语句为集合中的每个对象或数组中的元素重复执行一组语句。
VisualBasic每次循环运行时都会自动设置一个变量。
可以在循环中的任何位置放置任意数量的ExitFor语句作为退出的替代方式。
对于每个元素在组中
[退出For]
下一个[element]
必需：用于迭代集合或数组元素的变量。对于集合，元素只能是Variant变量、通用对象变量或任何特定对象变量。对于数组，元素只能是Variant变量。
组：必需。对象集合或数组的名称
语句
可选。在组中的每个项目上执行的一个或多个语句。
场景19
创建一个名为UPPER_CASE的宏，将数据转换为大写字母。使用Ucase()函数将大小写转换为大写字母
参考以下图19.2：
图19.2：场景19
场景20
创建一个名为lower_case的宏，将数据转换为小写字母。使用lcase()函数将大小写转换为小写字母
参考图19.3：
图19.3：场景20
场景21
创建一个名为Proper_case的宏，将数据转换为标题大小写字母。使用WorksheetFunction对象在VBA中使用Excel中的任何函数。
参考图19.4：
图19.4：场景21
场景22
打开场景22并进行修改。在记录宏后，您应该询问用户是否继续，并根据用户的响应运行。如果用户点击确定，则应继续数据输入。如果用户点击取消，则显示“谢谢”并结束宏。参考图19.5：
图19.5：场景22
场景23
创建一个宏，将为每个员工计算以下内容
住房津贴（工资的75%）
DA（工资的60%）
总计（工资+住房津贴+DA）
参考图19.6：
图19.6：场景23
解决这个问题可能有两种方法
参考培训文件6.xls
通过宏，您可以将公式放入单元格中：
在您的宏中计算并仅将结果放入单元格中：
场景24
创建一个宏来显示当前工作簿中工作表名称的列表。参考图19.7：
图19.7：场景24
语法是：
参考以下表19.1：
表19.1：自动执行的宏
练习3
编写一个名为“Search_sheet”的函数来检查任何工作表是否存在。
练习4
编写一个宏，为每位员工增加2000的工资。
场景25
创建一个宏，使用数据透视表生成按区域和部门工资总和以及员工数量的总计。修改代码，使每次都在当前数据上生成数据透视表。
场景26
编写一段代码，如果“master”工作表中存在，则从“daily”工作表中删除重复记录。
（使用嵌套循环）
解决方案26
参考培训文件7.xls
可以使用查找命令编写相同的代码如下
场景27
创建一个名为Merging_Sheets的宏，将所有工作表的数据复制到一个工作表中
你的宏应该生成一个按区域销售数量总计和员工代码总计的数据透视表。
解决方案
编写一个名为“PrintNumbers”的VBA宏，将数字从1打印到100在即时窗口中。
创建一个名为“CalculateSum”的VBA宏，用于计算从1到10的数字的总和，并在消息框中显示结果。
编写一个名为“EvenNumbers”的VBA宏，在即时窗口中打印从1到20的所有偶数。
创建一个名为“Factorial”的VBA宏，用于计算给定数字的阶乘。该宏应提示用户输入一个数字，然后在消息框中显示阶乘结果。
编写一个名为“ReverseString”的VBA宏，提示用户输入一个字符串，然后在即时窗口中打印字符串的反转。
创建一个名为“TableOfSquares”的VBA宏，生成从1到10的平方表。该宏应在新工作表中的不同列中显示数字及其平方。
编写一个为“CountCharacters”的VBA宏，计算给定字符串中的字符数。该宏应提示用户输入一个字符串，然后在消息框中显示计数。
加入书籍的DiscordWorkspace，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：
数组
使用数组
数组索引
调整动态数组的大小
数组是具有相同内在数据类型的一组按顺序索引的元素。数组的每个元素都有一个唯一的标识索引号。
对数组的一个元素进行更改不会影响其他元素。
不同类型的数组如下：
指定大小的数组是固定大小的数组。
在程序运行时可以更改大小的数组是动态数组。
单维数组：只有行
多维数组：带有行和列
Dimname_Of_array(Size)AsData_Type
DimMyarray(10)AsInteger
DimMyarray(3,5)AsInteger
数组中的每个元素包含一个值。
注意：在上面的示例中，数组索引将从0开始
所有数组索引都从零开始。数组是从0还是1开始索引取决于OptionBase语句的设置。
如果未指定OptionBase1，则所有数组索引都从零开始。
注意：在上面的示例中，数组索引将从1开始。
DimName_Of_Array()AsData_Type
调整动态数组大小
在使用ReDim语句时，小心不要拼错数组的名称。
数组示例
数组和集合是VBA编程中强大的工具，有助于管理数据集并增强代码效率。掌握这些概念，可以优化VBA代码，提高可读性，并有效处理复杂的数据结构。将数组和集合纳入编程技能范围，将扩展您的能力，使您能够处理更广泛的VBA项目。
使用循环输入每位员工在指定天数内的开支。
计算每位员工的总开支并显示结果。
确定总开支最高的员工，并打印其姓名和相应金额。
计算整个团队每天的平均开支并显示结果。
在本章中，我们将探讨在VisualBasicforApplications（VBA）中调试和错误处理的重要概念。调试是识别和解决VBA代码中的运行时错误和逻辑错误的过程。错误处理涉及实施策略来处理和管理在代码执行过程中发生的错误。通过理解这些概念并利用适当的技术，我们可以创建更健壮和可靠的VBA宏。
错误
错误处理
错误编号
调试宏
通过本章，读者将了解有关错误、错误处理和错误编号的内容，这些内容与调试一起在VBA中至关重要。
如果语句失败，将生成一个错误。有三种类型的错误：
逻辑：当宏没有给出预期结果时。这些错误可以通过改变逻辑和试错方法来处理
技术：当运行时出现任何语句失败时。使用OnError语句来处理这些错误。
语法：这些包括拼写错误的关键字、括号不匹配以及各种其他错误。Excel会标记您的语法错误，直到它们被纠正之前，您无法执行您的代码。
处理错误有三种方式：
每当遇到错误时，将控制转移到特定的标签或代码行。
立即继续执行导致运行时错误的语句后面的语句。
禁用当前过程中的任何已启用的错误处理程序。
注意：错误处理例程不是一个子过程或函数过程。它是由一行标签或行号标记的代码部分。如果不使用OnError语句，任何发生的运行时错误都是致命的；也就是说，会显示错误消息并停止执行。
场景28
打开场景22。如果数据库工作表不存在，将会生成一个错误。修改代码以处理此错误，也就是说，你的程序应该在数据库工作表不存在时添加一个新的工作表。
注意：使用OnErrorGoToline/label
每个运行时错误都有一个编号。如果您知道编号，可以通过编号捕获错误。例如，请参考以下表21.1：
表21.1:错误编号及其相应描述
尝试以下代码以查看特定错误编号的错误
场景29
打开场景28。如果留空输入框，您的宏将生成一个错误。按照以下方式修改代码（使用Onerrorresumenext）
调试是找到和纠正运行时错误和逻辑错误的过程。按F8逐行执行代码。
调试工具栏可在以下图21.1中看到：
图21.1:调试工具栏
这里是各种工具栏按钮：
设计模式：打开或关闭设计模式。
运行子程序/用户窗体或运行宏：如果光标在过程中，则运行当前过程，如果当前活动的是用户窗体，则运行用户窗体，如果既不是代码窗口也不是用户窗体，则运行宏。
中断：在程序运行时停止执行并切换到中断模式。
重置：清除执行堆栈和模块级变量，并重置项目。
切换断点：在当前行设置或移除断点。
逐步执行：逐条执行代码。
逐步跳过：在代码窗口中逐个过程或语句执行代码。
跳出：执行当前执行点所在过程的剩余行。
本地窗口：显示本地窗口。
立即窗口：显示立即窗口。
观察窗口：显示观察窗口。
快速监视：显示当前选定表达式的当前值的快速监视对话框。
调用堆栈：显示调用对话框，列出当前活动的过程调用（应用程序中已启动但尚未完成的过程）。
这里有一些提示，将帮助您将错误最小化：
使用OptionExplicit：这将强制您为每个使用的变量定义数据类型。这将避免常见的拼写变量名错误。
使用缩进格式化您的代码：如果您有几个嵌套的For...Next循环，例如，一致的缩进将使跟踪它们变得更加容易。
谨慎使用OnErrorResumeNext：此语句导致Excel忽略任何错误并继续。在某些情况下，使用此语句将导致Excel忽略不应被忽略的错误。您可能会有错误而自己都没有意识到。
保持你的子程序和函数简单：将代码编写成较小的模块，每个模块都有一个明确定义的目的。
使用宏录制器识别属性和方法：如果您不记得属性或方法的名称或语法，录制一个宏并查看录制的代码。
考虑采用不同的方法：如果您在使特定例程正常工作方面遇到困难，您可能希望放弃这个想法，尝试完全不同的方法。在大多数情况下，Excel提供了几种实现相同目标的替代方法。
使用调试工具栏
调试和错误处理是VBA开发人员必备的技能。通过有效地管理错误和调试我们的代码，我们可以创建更健壮和无错误的宏。应用最佳实践，如适当的代码格式化、注释和使用可用的调试工具，将帮助我们高效地预防和解决错误。
打开一个新的Excel工作簿并导航到VisualBasic编辑器（VBE）。
在VBE中，插入一个新模块来编写VBA代码。
创建一个名为“CalculateStatistics”的子例程，不带任何参数。
使用高级错误处理技术（如“OnErrorGoTo”、“OnErrorResumeNext”和“Err.Raise”）实现错误处理。
提示用户使用“Application.InputBox”方法从工作表输入一系列数字。
使用“WorksheetFunction”对象在所选范围上执行以下计算：
计算数字的总和。
计算数字的平均值。
计算范围内的最大值。
计算范围内的最小值。
在单独的消息框中显示计算出的统计数据。
包括全面的错误处理，以处理诸如无效输入、空选择、范围内的非数字值或除零错误等情况。
通过使用不同的输入执行宏来测试它，并验证它有效地处理错误并提供准确的结果。
加入书籍的DiscordWorkspace，获取最新更新、优惠、全球技术动态、新发布和与作者的会话：
用户表单和用户输入是在VisualBasicScripting（VBS）中创建交互式和用户友好应用程序的重要方面。用户表单允许开发人员设计直观的界面，并捕获用户输入以实现各种目的。通过利用诸如标签、文本框、按钮等控件，VBS开发人员可以创建动态、响应迅速的表单，从而增强整体用户体验。
在本章中，我们将介绍以下主题：
用户表单
创建用户表单
添加其他控件
处理控件的事件
本章结束时，读者将了解用户表单以及如何创建它们，添加其他控件以及处理控件的事件。了解VBS中的用户表单和用户输入将使您能够构建满足用户特定需求的交互式和功能性应用程序。
用户表单用于创建自定义对话框。参考以下图22.1：
图22.1：用户表单
要创建用户表单，请按照以下步骤操作：
点击插入菜单
选择UserForm，如图22.2（a）所示。
这将在您的工作簿中添加一个对象UserForm1，如图22.2（b）所示。
使用属性窗口更改表单的名称、行为和外观。例如，要更改表单上的标题，设置Caption属性，如图22.2（c）所示
参考图22.2：
图22.2：创建用户表单
要添加其他控件，请按照以下步骤操作：
点击工具栏中的工具箱，如图22.3（a）所示。
将控件拖放到表单上，如图22.3（b）所示。
从属性窗口更改属性，如名称、字体等，如图22.3（c）所示
参考图22.3：
图22.3：添加其他控件
要处理控件的事件，请按照以下步骤操作：
打开表单的代码窗口。
选择表单控件。
从项目资源管理器窗口中点击查看代码工具，如图22.4（a）所示。
从图22.4（b）中选择您在表单中放置的控件。
选择您控件的事件，如图22.4（c）所示。
按照图22.4（d）中所示编写事件代码。
图22.4：处理控件事件
场景30
创建一个用户表单，用于接受参与者的信息以报名培训。该表单应包含“插入”和“取消”命令按钮。单击“插入”按钮时，表单中输入的信息必须进入Excel。单击“取消”按钮时，表单应关闭。
按照给定的步骤：
插入一个用户表单。
从属性窗口更改名称和标题。
表单的名称应为USR_enrol。
标题应为培训报名表。
设计如图22.5所示的表单：
图22.5：场景30解决方案
在这里，
标签：显示描述性文本。
文本框：文本框是用户输入信息最常用的控件。
ListBox：显示值列表并允许选择一个或多个。
ComboBox：结合了ListBox和TextBox的功能。用户可以像使用TextBox一样输入新值，或者像使用ListBox一样选择现有值。
框架：创建一个功能性和视觉控制组。
属性：属性（性或变量）。
方法：对象将执行的操作与属性。
参考以下表22.1：
表22.1：员工用户表单
总之，用户表单和用户输入是VisualBasicScripting（VBS）的基本组成部分，允许开发人员创建交互式和用户友好的应用程序。通过利用用户表单，开发人员可以设计直观的界面，捕获用户输入，并增强整体用户体验。整合用户表单并有效管理用户输入可以极大地提高VBS应用程序的功能性和可用性。
创建一个名为“RegistrationForm”的用户表单，用于捕获活动注册的用户信息。该表单应包括以下控件：
组合框：包含用户可选择的事件列表。
选项按钮：用户选择他们偏好的付款方式（例如，“信用卡”，“PayPal”，“现金”）。
命令按钮：“提交”和“清除”。
你的任务是设计具有适当控件的用户表单，设置它们的属性，并处理提交和清除按钮的事件。
加入书籍的Discord工作区，获取最新更新、优惠、全球科技动态、新发布和与作者的交流：
在本章中，我们深入探讨了高级VBA技术和最佳实践，以增强Excel应用程序的功能和效率。我们探讨了初始化控件值、使用选项按钮、创建自定义按钮和用户表单、利用Add-ins、实现大小写转换Add-In以及通过代码创建菜单等主题。通过掌握这些高级技术，读者将更深入地了解VBA编程，并能够构建更强大和用户友好的Excel应用程序。
给控件初始值的代码
选项按钮的代码
插入按钮的代码
显示用户表单的代码
Add-ins
更改大小写表单的代码
通过代码创建菜单
通过本章结束，读者将学习到关于高级VBA技术和最佳实践以增强其Excel应用程序的知识。
要给控件初始值，按照以下步骤进行：
从项目资源管理器中选择查看代码表单。
选择UserForm对象。
选择初始化事件。
参考以下图23.1：
图23.1：给控件初始值
代码如下：
要应用选项按钮，请按照以下步骤进行：
双击OPT_company
私有子过程OPT_company_Click()
'当用户选择此选项时，txt_companyname文本框将可见。
TXT_companyname.Visible=True
结束子程序
双击OPT_personal
私有子过程OPT_personal_Click()
'当用户选择此选项时，txt_companyname文本框将不可见
TXT_companyname.Visible=False
参考以下图23.2：
图23.2：添加选项按钮
参考以下图23.3：
图23.3：添加插入按钮
双击插入按钮
在插入值后清除表单，请按照以下步骤操作：
双击取消按钮。
然后编写以下代码：
PrivateSubCMD_cancel_Click()卸载me
要从Excel运行用户表单，插入一个模块并编写一个宏：
插件
插件是单独的实用程序。它们为软件提供一些额外的功能。插件的扩展名是.XLAM。在Excel中，我们有一些现成的插件，如求解器、分析工具包、条件求和向导等。
场景31
创建一个插件，根据用户选择的选项将大小写转换为大写/小写/首字母大写。
按照给定的步骤操作：
设计一个插件表单，如图23.4所示：
图23.4：设计插件表单
打开一个新的Excel工作簿。
插入一个用户表单，并将其命名为Changecase。
给标题改变大小写。
在表单上拖动对象。
设计如下所示的表23.1：
表23.1：选项按钮
打开Changecase表单的代码窗口
为不同的控件编写代码
双击OPTupper控件。
参考以下图23.5：
图23.5：小写
现在，双击OPTlower控件。
参考以下图23.6：
图23.6：ProperCase
双击opt_upper控件并编写以下代码
双击cmdexit控件
插入一个模块并编写一个宏来显示Changecase表单
通过代码创建菜
参考以下代码：
Auto_Open():这是在打开工作簿时触发的第一个事件。
Auto_Close():这是在打开工作簿时触发的最后一个事件。
在本章中，我们探讨了可以显著改进Excel应用程序功能和用户体验的高级VBA技术和最佳实践。从初始化控件值到处理选项按钮，创建自定义按钮和用户表单，利用加载项，并通过代码创建菜单，我们涵盖了广泛的主题。通过应用这些技术并遵循概述的最佳实践，读者可以创建更强大、高效和用户友好的Excel应用程序。掌握这些高级技术后，读者将能够将他们的VBA技能提升到更高水平。
让我们考虑一个基于示例的练习，结合了本章讨论的几种高级VBA技术。
场景：您正在Excel中开发一个项目管理工具。该工具允许用户输入项目细节，跟踪进度并生成报告。您的任务是通过实现以下功能来增强该工具：
添加一个名为“TaskForm”的用户表单，允许用户输入任务细节，包括任务名称、分配人员、开始日期、结束日期和状态（例如，“未开始”，“进行中”，“已完成”）。
在TaskForm中实现验证检查，确保所有必填字段都填写完整，并且结束日期不早于开始日期。如果任何验证失败，显示适当的错误消息。
在TaskForm上创建一个自定义的“插入”按钮，将输入的任务详情添加到指定的工作表，如“ProjectTasks”。确保每个新任务都插入到新行，并在成功插入后清空输入字段。
使用VBA代码实现一个菜单系统。在Excel菜单栏中创建一个名为“项目管理”的新菜单项。在“项目管理”菜单下，添加选项以打开TaskForm，显示所有任务列表，并生成一个汇总报告。
开发一个汇总报告功能，计算并显示任务总数、每个状态类别中的任务数以及已完成任务的百分比。汇总报告应显示在名为“TaskSummary”的新工作表中。
实现一个名为“TaskUtilities”的插件，提供额外的任务管理功能，如按名称或日期对任务进行排序，按状态筛选任务，并生成专门的报告。通过在不同的工作簿中使用该插件来测试它，并验证其功能。
在整个项目中应用最佳的代码组织、错误处理和优化实践，以确保清晰高效的VBA代码。
在本章中，我们将探讨在Excel中使用VisualBasicforApplications（VBA）构建自定义插件的过程。插件是可以集成到Excel中以增强其功能并简化工作流程的附加功能或工具。我们将涵盖诸如使用密码保护插件和有效使用插件等主题。
使用密码保护您的插件
使用插件
在本章结束时，读者将学会如何使用密码保护他们的插件以确保安全性，以及如何有效利用插件来增强Excel的功能。
要使用密码保护您的插件，请按照以下步骤操作：
折叠项目的所有对象（插入表单和模块以用于插件的Excel文件）。
右键单击该项目。
选择VBA项目属性。
选择“保护”选项卡。
选择“锁定以查看”。
设置密码。
参考图24.1：
图24.1：添加密码
一旦您准备好添加插件的表单和模块，切换到Excel环境。
将当前文件保存为插件类型（.xlam扩展名），如图24.2所示：
图24.2：保存当前文件
要使用插件，请按照以下步骤操作：
点击“Office”按钮。
点击“Excel选项”。
选择“插件”。
点击“前往…”按钮，如图24.3所示：
图24.3：选择插件
浏览以找到您的插件，如图24.4所示：
图24.4：定位您的插件
总之，使用VBA构建自定义插件使用户能够扩展Excel的功能并简化其工作流程。通过密码保护插件可以确保其安全性。有效利用插件可以提高生产力，使用户能够在Excel中访问额外的功能和工具。
打开MicrosoftExcel并创建一个新的工作簿。
创建一个简单的VBA宏，用特定字体、字体大小和背景颜色格式化所选单元格。
将VBA宏保存为具有.xlam扩展名的插件。
使用密码保护插件以确保其安全性。
通过在Excel中安装并使用插件来格式化不同工作表中的单元格来测试插件。
加入本书的DiscordWorkspace，获取最新更新、优惠、全球技术动态、新发布和与作者的交流：
通过本章结束时，读者将学会如何将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中使用VBA将单词转换为卢比数字，您可以创建一个自定义函数，该函数使用MicrosoftSpeech对象库将文本转换为语音，然后从所说的文本中提取数字。以下是您可以执行此操作的示例：
打开一个新的Excel工作簿，按Alt+F11打开VBA编辑器。
在编辑器中，转到插入|模块，创建一个新模块。
在新模块中，复制并粘贴图25.7中显示的代码：
图25.7：要复制到新模块的代码
保存模块并返回到您的Excel工作表。
在您想要将单词转换为卢比数字的单元格中，输入以下公式：
=WordsToNumbers(“仅两千五百卢比五十派仅”)
按Enter键计算结果。
此公式将单词“仅两千五百卢比五十派仅”转换为数字2500.50。您可以将示例文本替换为您要转换的实际文本。
总的来说，Excel和ChatGPT的结合可以帮助您更高效、更有效地处理数据，并为您的工作提供新的见解和视角。假设您有一张包含公司销售数据的电子表格。您想要计算每个月的总销售额，并创建一个图表来可视化数据。为此，您需要按照以下步骤操作：
首先，您可以使用Excel创建一个新列，并使用MONTH函数从销售数据中提取日期的月份。
然后，您可以使用SUMIFS函数计算每个月的总销售额。您需要为函数指定条件，以便对每个月的销售数据进行求和。
一旦您有了总数，您可以创建一个图表来可视化数据。选择您的数据，转到插入选项卡，并选择您想要创建的图表类型。
如果您不确定要使用哪种图表类型或如何格式化图表，可以向ChatGPT寻求建议。例如，您可以询问“用于按月可视化销售数据的最佳图表类型是什么？”或“如何使我的图表更具视觉吸引力？”
ChatGPT可以根据最佳实践和设计原则提供建议。如果您不确定如何使用Excel中的特定功能或设置，也可以向ChatGPT询问。参考图25.8作为示例：
图25.8：向ChatGPT寻求有关Excel中特定功能或设置的帮助
总的来说，使用Excel和ChatGPT一起可以帮助您更高效、更有效地处理数据，并为您的工作提供新的见解和视角。
总之，将ChatGPT与Excel集成为用户提供了有价值的帮助和指导。通过利用Excel函数和公式的强大功能，再加上ChatGPT的能力，用户可以提高在数据分析、公式故障排除和格式设置等任务中的效率。ChatGPT还可以提供有关更有效使用Excel的技巧、窍门和见解。此外，与ChatGPT合作在电子表格上进行协作并从ChatGPT获取反馈，可以增强使用Excel的整体体验。通过结合这些工具，用户可以优化其数据管理和分析工作流程，从而提高生产力和做出更好的决策。
打开MicrosoftExcel并创建一个新工作簿。
在工作表中输入以下示例销售数据
表25.1：示例销售数据
创建一个执行以下任务的VBA宏：
计算每个月的总销售额。
确定销售额最高的月份。
在消息框中显示计算出的总额和销售额最高的月份。
加入该书的Discord工作区，获取最新更新、优惠、全球科技动态、新发布内容以及与作者的交流：