收费网站建设视频教程免费下载,苏州网络营销及网站推广,烟台装修公司网站建设,商城分销怎么做注#xff1a;本文为 “VBA API 概述 | 宏编程 | 执行速度慢” 相关文章合辑。 VBA API 详解
Office 二次开发于 2020-12-17 22:27:10 发布
Office 版本变动
在 Office 2010 之前#xff0c;微软仅提供 32-bit 版本的 Office。而自 Office 2010 起#xff0c;出现了 32-b…注本文为 “VBA API 概述 | 宏编程 | 执行速度慢” 相关文章合辑。 VBA API 详解
Office 二次开发于 2020-12-17 22:27:10 发布
Office 版本变动
在 Office 2010 之前微软仅提供 32-bit 版本的 Office。而自 Office 2010 起出现了 32-bit 和 64-bit 这两个版本。64-bit 版本的 Office 具备一些优势比如能够处理更大的数据量并且 VBA 代码的运行速度也有所提升。不过其代码的兼容性变得复杂了尤其是在 API 的声明方面。
Office 2010 的 64 位版本引入了 VBA 7它能够同时在 32 位和 64 位这两个 Office 版本中运行。在安装 Office 2010 时默认会安装 32 位版本若用户需要安装 64 位版本则要手动进行相应选择。
对于 VBA 7 而言若要让以前版本的 API 在 64 位版本中正常运行必须重新对其进行声明更新语句里所使用的地址指针以及窗口句柄的声明。
Windows 系统版本
64 位的 offic 能引用更大的内存地址空间比以往使用更多的物理内存。除了引用应用程序用于存储数据或存储代码指令指针外还可以用地址来引用显示的窗口句柄。根据使用是 32 位或 64 位系统来决定指针或句柄的大小以字节位单位。
运行 64 位的 Office 时将面临两个基本问题
在 office 中的本机 64 位进程不能加载 32 位二进制文件。当使用 ActiveX 控件和现有插件时将不兼容。VBA 7 以前没有提供指针数据类型如果开发人员使用 32 位变量来存储指针或句柄时使用 Declare 定义的 API 返回的 64 位的值时部分值被丢弃。
VBA7 代码变化
VBA7 是一个新的代码库它取代了早期的 VBA 版本它适用于 32 位和 64 位的 Office。它提供了两个条件编译常数VBA7 和 Win64。
VBA7 常数 判断应用程序是否使用 VBA7 或者以前版本的 VBA确保代码的向后兼容性。
Win64 常数 判断代码是 32 位或 64 位的形式运行。
ActiveX Control 和 COM Add-in 兼容性
现有的 32 位 ActiveX 控件包括第三方和微软提供的与 64 位版本的 Office 不兼容。对于 ActiveX 控件和 Com 对象有三种可能的解决方法
如果有源代码可以生成 64 位版本。联系供应商更新。寻找其它解决方案。
Office 中本机 64 位进程不能加载 32 位二进制文件。包括 MSComCtl 通用控件TabStrip, Toolbar, StatusBar, ProgressBar, TreeView, ListViews, ImageList, Slider, ImageComboBox和 MSComCt2 控件Animation, UpDown, MonthView, DateTimePicker, FlatScrollBar这些控件由以前 Office 或现在 32 位 office 安装当迁移到 64 位 Office 中时必须替换现有的控件。64 位 Office 不提供 64 位通用的控件。
Windows API 接口兼容性
VBA 类型库提供了许多功能但是有时候必须直接与计算机的操作系统和其它组件通信时例如管理内存和进程时或使用用户界面时或修改注册表时在这些使用场景最好的选择是嵌入动态链接库DLL的到出函数。在 VBA 中使用 Delcare 语句导入 Dll 函数。
Delare 语句语法结构如下看是否有返回类型
Public/Private Declare Sub SubName Lib LibName Alias AliasName(argument list)Public/Private Declare Function FunctionName Lib Libname alias aliasname(argument list) As Type
SubName 函数或 FunctionName 函数替换 DLL 中的导出函数名如果要确定调用 ASCII 或 Unicode 版本的 API, 可以指定别名AliasName),Lib 后面紧跟到入函数所在的 Dll 名称。参数列表必须包含传递给 DLL 中的导出函数一致。 下面的 API 函数在 Windows 注册表中打开一个子健并替换它的值。
Declare Function RegOpenKeyA Lib advapi32.dll (ByVal Key As Long, ByVal SubKey As String, NewKey As Long) As Long
该函数在 Windows API 动态链接库 Advapi32.dll 中其函数名称为 RegOpenKeyW。其原型定义如下
LSTATUS RegOpenKeyW(HKEY hKey,LPCWSTR lpSubKey,PHKEY phkResult);
在 C 和 C 中该 API 可以针对 32 和 64 位进行编译。这是因为 HKEY 被定义为一个指针它能正确反映编译代码所在平台的内存大小。
在早期的 VBA 版本中没有特定的指针数据类型所以使用 Long 数据类型而且 Long 类型是 32 位的它在 64 位内存中的系统上使用时这种情况就被中断因为 32 位可能会被截断或覆盖其他内存地址。这些情况可能会导致不可预测的行为或系统奔溃。
为了解决这个问题VBA 现在包含了一个真正的指针数据类型 LongPtr。这个新的数据类型可以正确的编写 API 导入语句。
Declare PtrSafe Function RegOpenKeyW Lib advapi32.dll (ByVal hKey as LongPtr,ByVal lpSubKey as string,Byval phkResult as LongPtr) as Long
LongPtr 数据类型和 PtrSafe 特性可以在 32 位和 64 位系统上正确声明 API 导入函数。PtrSafe 特性向 VBA 编译器表明声明语句是真的 64 位 Office 的。如果没有这个特性在 64 位系统中 Delcare 语句将导致编译错误。注意 PtrSafe 语句在 32 位本质的 Office 中是可选的。下表提供了一些函数和数据类型数说明。
TypeItemDescription修饰符PtrSafe指示声明 API 与 64 位兼容在 64 位系统中是必须的。Data TypeLongPtr不是一个真实的数据类型在 32 位版本上是 4 个字节数据类型Long在 64 位版本上是 8 个字节的数据类型 (LongLong)。这是 VBA 7 以后声明指针和句柄推荐的方法它只在 32 位和 64 位的 VBA7 中得到支持。Data TypeLongLong8 个字节的数据类型仅在 64 位 Office 中可用。Conversion OperatorCLngPtr将表达式转换为 LongPtr 类型。Conversion OperatorCLngLng将表达式转换为 LongLong 类型。FunctionVarPtr变体类型地址转换在 64 位中返回 LongPtr, 在 32 位中返回 Long 数据类型。FunctionObjPtr对象地址转换在 64 位中返回 LongPtr, 在 32 位中返回 Long 数据类型。FunctionStrPtr字符串地址地址转换在 64 位中返回 LongPtr, 在 32 位中返回 Long 数据类型。
注意没有 PtrSafe 特性的 API 声明语句被认为与 64 位的 Office 不兼容。
综上所述有两个条件编译常量VBA7 和 Win64。为了确保与以前版本的 Office 向后兼容可以使用 VBA7 常量来防止早期版本中使用 64 位代码。对于 32 位和 64 位版本间不同代码例如一个 API, 它在 64 位版本中使用 LongLong, 在 32 位版本中使用 Long, 则可以使用 Win64 常量下面举例使用这个两个常量
#if Win64 ThenDeclare PtrSafe Function MyTestFunc Lib DllName(Byval N as LongLong) as LongLong
#elseDeclare MyTestFunc Lib DllName(Byval N as Long) as Long
#End if#if VBA7 thenDeclare PtrSafe Sub MessageBeep Lib User32(Byval N as Long)
#elseDeclare Sub MessageBeep Lib User32(Byval N as Long)
#end if
总之如果在写 64 位代码且希望与早期 Office 兼容那么使用 VBA7 条件编译常量。如果在编写 32 位 Office 代码那么代码工作原理与以前版本 Office 一样不需要条件编译常量。如果想编写兼容 32 位和 64 位 Office那么使用 Win64 编译常量。
使用 StrPtr、VarPtr、ObjPtr
用这些函数来返回字符串类型、变体类型和对象类型的的指针内存地址。
动态链接库基础知识
A dynamic-link library (DLL) 是一个包含函数和数据的模块可以被另外一个模块或应用程序使用。 DLL 定义两种函数导出函数和内部函数。导出函数可以被其它应用程序使用内部函数仅在内部使用。 Windows API 就是一组 Dll 的集合各 Dll 模块导出了不同的函数供外部模块使用。 每个加载 Dll 的进程都将 Dll 映射到它的虚拟内存地址空间中在进程将 Dll 加载到它的虚拟地址之后改进程就可以调用 Dll 的导出函数。
VBA 声明 API
先在微软官网上找到 API 函数原型然后根据 VBA 语法规则导入函数。如下函数
int MessageBox(HWND hWnd,LPCTSTR lpText,LPCTSTR lpCaption,UINT uType
);
API 函数都是 C 语言格式定义的如果是指针类型则定义为 VBA 的长整数如果是二级指针则定义为 Any 类型。在 office 2010 以后为了兼容 32 位和 64 位指针类型定义位 LongPtr。 API 函数一般又 ASCII 和 Unicode 两个版本别名指定一个确定的版本建议用 Unicode 版本W 结尾的 API 函数。
Office 2010 以后定义方式选择 VBA 变量和 C 语言字节一致的变量如 C 的 int 是 4Byte 等同于 VBA 的 long。VBA7 以后多了一个 LongPtr 类型在 32 位为 Long 类型在 64 位为 LongLong 类型。
#if VBA7 Thenpublic declare ptrSafe Function MessageBox lib user32.dll alias MessageBoxW _
(byval hWnd as LongPtr,byval lpText as string,byval lpCaption as string, byval uType as long) as long#elsepublic declare Function MessageBox lib user32.dll alias MessageBoxW _
(byval hWnd as Long,byval lpText as string,byval lpCaption as string, byval uType as long) as long#end if上面定义一个 Unicode 版本的 API, 同时能兼容各个版本的 Office。 【数据分析】Excel 中使用 VBA 进行宏编程
STARBLOCKSHADOW 已于 2024-05-19 19:35:55 修改
Microsoft Excel 是广泛用于数据处理和分析的工具而 VBAVisual Basic for Applications是一种基于 Microsoft Visual Basic 的编程语言可用于在 Excel 中创建自定义宏和自动化任务控制 Excel 工作簿、工作表和数据执行各种操作。这篇博客将介绍如何使用 VBA 进行 Excel 宏编程以提高工作效率和自动化重复任务。
0 准备工作
Excel 中主选项卡默认是没有开发工具选项的需要选择主选项卡 文件→选项→自定义功能区打开 Excel 中的开发工具面板 这样在 Excel 的主选项卡中就有开发工具选项了 在开发工具选项卡中选择 Visual Basic进入 VBA 编辑器或使用快捷键 AltF11 创建新的模块Module并开始编写第一个宏 如在 Excel 中显示消息框 Sub 显示消息框 ()MsgBox 欢迎使用 VBA 宏编程
End Sub 编写完成后保存使用 F5 运行或者在开发工具选项卡中选择 “运行” 来执行宏 运行结果 1 VBA 简介
1.1 Excel VBA 应用程序的构成
从开发者角度看Excel VBA 应用程序由工作表、用户窗体、模块和类模块等部分构成。 工作表用于保存和显示程序的数据是程序的主体部分。一般先在工作表中制作出特定表格的格式并设置好样式再通过 VBA 代码获取表格中的数据经过加工处理后将其填写入相应的单元格供用户进行查看、打印输出等操作。 用户窗体在 Excel VBA 应用程序中除了可使用工作表与用户进行交互外还可向程序添加用户窗体用来与用户进行交互操作。使用用户窗体可将用户与工作表中的数据进行隔离防止数据被意外修改并隐藏工作表中的敏感数据使限制权限的用户只看到应该操作的数据。 模块在模块中可保存程序的通用过程供其他过程调用。例如录制宏的代码就保存在模块中。 类模块在 Excel VBA 中除了可使用系统提供的对象外还可通过自定义类来创建自定义的对象自定义的类必须保存在 “类模块” 中。大多数应用程序都不使用 “类模块”。
1.2 事件驱动
VBA 是运行在 Microsoft Office 软件之上包括 Excel、Word、PPT、Outlook 等可以用来编写非软件自带的功能的编程语言不同的是每一个软件具有自己独有的对象例如 Excel 有单元格对象Word 有段落对象PPT 有幻灯片对象。
Office 软件提供丰富的功能接口VBA 可以调用它们实现自定义的需求。基本上能用鼠标和键盘能做的事情VBA 都能做它采用了与 Windows 相似的事件驱动编程方式。
在这种模式下Windows 监视窗口活动或事件信号这些事件可以是用户鼠标点击或按键操作也可以是程序控制或其他窗口的操作引发的。通过 VBA 的事件驱动机制开发人员可以编写事件过程来处理系统产生的事件实现特定的功能如在鼠标点击时打开一个窗口。
与传统的过程化应用程序不同事件驱动的应用程序中代码执行路径不是按照预定顺序进行的。相反代码在响应不同事件时执行不同的片段。这些事件可以由用户、操作系统、其他应用程序的消息触发甚至是应用程序自身的消息触发。
由于事件顺序无法预测代码必须对各种执行状态做出假设。为确保这些假设在执行时有效应用程序的结构需要组织良好。在 Excel 中使用 VBA 开发应用程序实质上是编写处理各对象不同事件的代码。
1.3 宏
简单的说宏就是一段可以运行的 VBA 代码片段。Excel 宏使用 VBA 语言进行编写通过 VBA 编写的宏可控制 Excel对 Excel 的功能进行扩充。
1.3.1 创建宏
Excel 提供了两种创建宏的方法一种方法是利用 Excel 操作环境中的宏录制器录制用户的操作另一种方法是使用 Visual Basic 编辑器编写自己的宏代码。
利用宏录制器可记录用户在 Excel 中的操作动作以便自动创建需要的宏这在不太了解宏命令时是非常方便的。
使用 Visual Basic 编辑器可以打开已录制的宏修改其中的命令也可以在 Visual Basic 编辑器中直接输入命令创建宏。对于很多无法录制的命令如创建新的窗体等使用 Visual Basic 编辑器创建宏是唯一的方法。
启用录制宏的两种方式 注宏名不但可以包含字母、数字和下画线还可以使用中文但不能包含空格。但名称开头需为字母或下划线且不能与已有宏重名。 注在宏中定义的快捷键将覆盖任何对等的默认的 Excel 快捷键。
在创建宏之后可以将宏分配给对象如按钮、图形、控件和快捷键等这样执行宏就像单击按钮或按快捷键一样简单。正是由于这种操作方便的特性使用宏可以方便地扩展 Excel 的功能。如果不再需要使用宏可以将其删除。
如果将录制的宏名称设置为 “Auto_Open”则每次打开包含此宏的工作簿时该宏都会运行。另一种在打开工作簿时自动运行宏的方法是使用 Visual Basic 编辑器VBE在工作簿的 Open 事件中编写 VBA 过程。Open 事件是一个内置的工作簿事件它会在每次打开该工作簿时都运行自己的宏代码。如在用户打开工作簿时显示一个 “欢迎” 对话框可新建一个模块在其中输入以下代码 Sub Auto_Open ()MsgBox (欢迎使用 Excel!)End Sub
保存并关闭该文件若提示不能保存则选择 “否” 后将文件保存为.xlsm 类型即可。 再次打开该文件时将会显示 “欢迎使用 Excel” 的对话框。
1.3.2 宏安全
从 Office 软件支持宏开始宏病毒也随之出现。许多病毒经过专门设计可以利用 VBA 宏对系统和数据文件进行恶意操作。因此宏的安全性越来越受到用户的重视。
按照如下步骤设置宏安全性
Excel 选项卡选择 文件→选项→信任中心 或者选项卡选择 开发工具→宏安全 2. 设置宏的安全性为 禁用所有宏并发出通知 四种类型的宏设置
“禁用所有宏并且不通知”如果不信任宏则使用此设置。文档中的所有宏及有关宏的安全警报都被禁用。如果文档具有信任的未签名的宏则可以将这些文档放在受信任的位置上。受信任的位置中的文档可直接运行不会由信任中心安全系统进行检查。“禁用所有宏并发出通知”这是默认设置。如果想禁用宏但又希望在存在宏时收到安全警报则应使用此选项。这样可以根据具体情况选择何时启用这些宏。“禁用无数字签署的所有宏”此设置与 “禁用所有宏并发出通知” 选项相同。但下面这种情况除外在宏已由受信任的发行者进行了数字签名时如果信任发行者则可以运行宏如果不信任发行者将发出通知。这样可以选择启用那些签名的宏或信任发行者的宏。所有未签名的宏都被禁用且不发出通知。“启用所有宏不推荐可能会运行有潜在危险的代码”可以暂时使用此设置以便允许运行所有宏。因为此设置容易使计算机受到恶意代码的攻击所以不建议永久使用此设置。
2 VBA 基础
2.1 注释
VBA 中的注释以英文单引号 开头后面接注释的内容。从单引号开始的部分不会被执行。 我是一行注释
2.2 数据类型
Excel 单元格中可以保存处理多种类型的数据包括数值、日期 / 时间、文本、货币等。VBA 中除了提供这些数据类型之外还提供字节、布尔和变体数据等类型。
2.2.1 基本数据类型
常见的 VBA 基本数据类型如下表
数据类型含义精度范围Byte字节型0 ~ 255Integer整型-32,768 ~ 32,767Long长整型-2,147,483,648 ~ 2,147,483,647String字符串变长字符串0 ~ 20 亿 (231) 个字符 定长字符串1~ 约 64K (216) 个字符Single单精度浮点型在表示负数时 -3.402823E38 ~ -1.401298E-45 在表示正数时 1.401298E-45 ~ 3.402823E38Double双精度浮点型在表示负数时 -1.79769313486231E308 ~ -4.94065645841247E-324 在表示正数时 4.94065645841247E-324 ~ 1.79769313486231E308Decimal定点数未放置定点数 /- 79,228,162,514,264,337,593,543,950,335 放置定点数 /- 7.9228162514264337593543950335Currency货币型-922,337,203,685,477.5808 ~ 922,337,203,685,477.5807Date日期型时间00:00:00 至 23:59:59 日期 100-1-1 至 9999-12-31Boolean逻辑值True 或 FalseVariant变体型不限Object对象型VBA 和 Excel 对象
2.2.2 枚举类型
枚举就是将变量的值逐一列举出来属于该枚举型的变量只能取列举的某一个值。当一个变量只有几种可能的值时可以定义为枚举类型Enum。
枚举型举例
Public Enum WorkDays星期日星期一星期二星期三星期四星期五星期六
End Enum
2.2.3 用户自定义数据类型
在 VBA 中还可以使用 Type 语句定义自己的数据类型其格式如下
Type 数据类型名数据类型元素名 As 数据类型数据类型元素名 As 数据类型...
End Type
注自定义数据类型的定义必须放在模块模块和类模块的声明部分中。在使用记录类型之前必须用 Type 语句进行定义。一般情况下记录类型在模块中定义其变量可以出现在工程的任何地方。
2.2 变量
变量是存储数据的一种表达方式。在程序开始可以声明一个变量指定变量的类型数字、文本、逻辑值等并给变量赋值。在程序的其他地方就可以使用该变量用其存储的值参与运算。
2.2.1 声明变量
使用变量前需要先声明变量即通过以下格式指定变量名和变量的数据类型
Dim [变量名] As [数据类型]
2.2.2 命名变量
在 VBA 中变量名称为标识符定义标识符名称时应遵循以下规则
第一个字符必须使用英文字母中文版 Excel 支持中文字符作标识符名称。不能在标识符中使用空格、句点.、感叹号!或者 、、$、# 等字符。标识符的长度不能超过 255 个字符。通常标识符不能与 VBA 本身的 Function 过程、语句及方法的名称相同。必须谨慎使用与程序语言的关键字相同的名称。若所使用的内在语言函数、语句或方法与所指定的名称相冲突则必须显式地识别它。一般会在内置函数、语句或方法的名称之前加上关联的类型库的名称。例如如果有一个名为 Left 的变量则只能用 VBA.Left 来调用 Left 函数。不能在同一范围的相同层次中使用重复的名称。例如不能在同一过程中声明两个命名为 age 的变量。但可以在同一模块中声明一个私有的命名为 age 的变量和过程级别的命名为 age 的变量。
在实际开发过程建议按驼峰法命名。即第一个单词以小写字母开始从第二个单词开始以后的每个单词的首字母都采用大写字母。如myFirstName、myLastName。
2.3 常量
在程序运行过程中值不发生变化的量称为常数又称常量常数的值在程序执行之前就已经确定执行过程中不能改变。由于其值的数据类型不同常量也具有数据类型。VBA 中常量的类型有三种分别是直接常数、符号常数和系统常数。
2.3.1 直接常数
在 VBA 程序代码中直接书写的量称为直接常数如
area r * r * 3.14
上述代码中的数值 3.14 就是直接常数。
直接常数也有数据类型的区别其数据类型由它本身所表示的数据形式决定。根据数据类型的不同直接常数分为数值常数、字符串常数、日期 / 时间常数和布尔常数。
数值常数数值常数是由数字、小数点和正负符号所构成的量。一个数值常数有时可能存在多种数据类型的解释。例如 6.18 可解释为单精度型也可为双精度型。VBA 将使用占用内存少的那种类型即单精度型。字符串常数字符串常数是由数字、英文字母、特殊符号和汉字等可见字符构成的在书写时必须使用双引号作定界符如VBA。如果字符串常数中包含双引号则需要在有双引号的地方输入两次双引号如Milko 说:VBA 程序很简单 注意最后用了 3 个双引号前 2 个双引号将输出为一个引号最后 1 个双引号为字符串的定界符。日期 / 时间常数日期 / 时间常数用来表示某一天或某一具体时间使用 # 作为定界符日期时间的意义要正确例如 #8/8/2008# 是正确的而 #2/30/2008# 是错误的因为二月份没有 30 日。布尔常数布尔常数也称为逻辑常数只有两个值True真False假。
2.3.2 符号常数
如果在程序中需反复地使用某一个常数可为该常数命名在需要使用该常数的地方引用其常数名则可。
使用符号常数有如下优点
提高程序可读性。符号常数是有意义的名字可提高程序的可读性。快速修改程序。如果需要在程序中修改常数的具体值只需要在定义符号常数处修改即可。减少出错率。如果反复在程序中输入同一数据有可能在某处输入错误导致计算结果不同不好查错。使用符号常数则只需要定义一次就可引用。
在程序运行时不能对符号常数进行赋值和修改因此符号常数在程序运行前必须有确定的值。
声明符号常数与声明变量类似需要指明符号常数名和数据类型。不同在于符号常数的值在声明时就需要指定。
声明符号常数的格式如下
Const [常量名] As [数据类型] [值]
2.3.3 系统常数
系统常数就是 VBA 系统内部提供的一系列各种不同用途的符号常数。例如色彩常数用 “vbBlack” 表示黑色比用数值 “0x0” 更直观易用。这些常数可与应用程序的对象、方法和属性一起使用。
在 VBA 中系统常数名采用大小写混合的格式其前缀表示定义常数的对象库名。在 Excel 中的系统常数名通常都是以小写的 xl如 xlWindowType 的成员包括 xlWorkbook 等几个作为前缀而 VB 中的系统常数名通常都是以小写的 vb 作为前缀。要查询某个系统常数的具体名称及其确切值可通过 视图→对象浏览器 查询。 2.4 运算符
运算符是介于操作数间的运算符号VBA 中运算符可以分为赋值运算符、算术运算符、比较运算符、逻辑运算符、连接运算符和其他运算符这 6 类。
2.4.1 赋值运算符
给变量赋值会使用到赋值运算符语法如下
[变量名] [数据]
赋值语法核心是 (赋值运算符)它是右结合的阅读时从右至左阅读时从右至左即将数据的值赋值给变量。
其中[数据] 的形式可以是直接书写的内容例如 4、“好”、True 等也可以是另外一个变量。
变量赋值完成后在后续的程序中就可以使用变量参与各类的计算了。
2.4.2 算术运算符
算数运算符即常用的数学运算符包括加减乘除等。
VBA 中的算术运算符如下表所示。
算术运算符含义两数相加-两数相减 或 表示数值表达式的负值*两数相乘/两数相除\两数相除取整Mod两数相除取余^幂运算
2.4.3 比较运算符
比较运算符用来表示两个或多个值或表达式之间的关系比较结果只能为 True 或 False。
VBA 中的比较运算符如下表所示。
比较运算符含义比较两个值是否相等大于大于等于小于小于等于不等于
2.4.4 逻辑运算符
逻辑运算符是指连接表达式进行逻辑运算的运算符逻辑运算结果只有 True 和 False 两种可能。
逻辑运算符含义And逻辑与若两个表达式都为真则返回 TrueOr逻辑或若两个表达式至少有一个为真则返回 TrueNot逻辑非对逻辑表达式取否Xor逻辑异或若两个表达式不相同则返回 TrueEqv逻辑等价若两个表达式两在逻辑上等效则返回 TrueImp逻辑蕴含若第一个表达式蕴含第二个表达式则返回 True
2.4.5 连接运算符
VBA 中的连接运算符用于连接 2 个或多个文本。
连接运算符含义强制两个表达式做字符串连接。如果表达式的结果不是字符串则将其转换成字符串用于两个字符串的连接只有运算符两边的表达式都是字符串时才进行连接运算
注在使用 “” 运算符时有可能无法确定是做加法还是做字符串连接。因此为避免混淆一般使用 “” 运算符进行连接。
2.4.6 其他运算符
其他运算符含义_将一行代码分解成两行:将两行代码放置在一行
2.5 常用语句
2.5.1 语句
一行代码就是一条语句。在默认情况下在 VBA 编辑器中输入语句后VBA 编辑器将自动进行语法检查。
2.5.1.1 自动格式化
输入 VBA 语句后VBA 编辑器将按一定的规则进行简单的格式化处理。例如将关键字的首字母大写在运算符前后加入空格删除各部分多余的空格等。
在输入 VBA 关键字时可以不区分大小写。例如输入 MsgBox 时无论输入的是 Msgbox、msgbox 还是 MSGBOX当输入完该函数的参数并按回车键后VBA 编辑器会自动将其变为 MsgBox。
为了提高程序的可读性VBA 代码中应加上适当的空格。当按回车键完成语句的输入后各关键字之间无论插入多少空格VBA 编辑器都将其自动调整为一个空格。
2.5.1.2 复合语句
一般情况下要求程序中每个语句占一行。但在 VBA 中也可以把几个语句放在一行中构成复合语句。复合语句中各语句之间用冒号:分隔例如 Selection.Font.Bold True: Selection.Font.Size 20与以下两个语句的功能相同 Selection.Font.Bold TrueSelection.Font.Size 202.5.1.3 语句断行
在 VBA 编辑器的代码窗口中每行 VBA 代码可包含 1023 个字符。但是为了使程序便于阅读建议读者将一条长的语句打断为若干行。VBA 中使用空格后接着一个下画线 —— 续行符可将一行代码延伸成两行以上。例如以下语句 ActiveWorkbook.Protect Password:abc, Structure:True, Windows:False等价于 ActiveWorkbook.Protect _Password:abc, _Structure:True, _Windows:False通过续行符 _ 可创建长的逻辑行。一个逻辑行最多可包含 24 个连续的续行字符也就是最多可以包含 25 个物理行。这样逻辑行的字符总量可达 10230 字符。如果超过了字符总量必须将该行分为若干语句或指定一些表达式为中间变量。
2.5.2 输入 / 输出语句
计算机程序一般分为三部分首先接收用户输入的数据再按一定的算法对数据进行加工处理最后输出程序处理的结果。在程序中输入 / 输出语句占有很大的比例。在 Excel 中可从工作表、用户窗体等多处获取数据并可将数据输出到这些对象中。数据输入 / 输出的相关内容将在后续章节中进行介绍本节主要介绍 VBA 中标准的输入 / 输出方法。
2.5.2.1 输入语句
为了实现数据输入VBA 提供了 InputBox 函数。该函数将打开一个对话框作为输入数据的界面等待用户输入数据并返回所输入的内容。语法格式如下 InputBox (prompt [,title][,default][,xpos][,ypos][,helpfile,context])其中InputBox 函数的 7 个参数意义分别如下
Prompt对话框消息出现的字符串表达式最大长度为 1 024 个字符。如果需要在对话框中显示多行数据则可在各行之间用回车换行符来分隔一般使用 VBA 的常数 vbCrLf 代表回车换行符。Title对话框标题栏中的字符串。如果省略该参数则把应用程序名放入标题栏中。Default显示在文本框中的字符串。如果省略该参数则文本框为空。Xpos和 Ypos 成对出现指定对话框的左边与屏幕左边的水平距离。如果省略该参数则对话框会在水平方向居中。Ypos和 Xpos 成对出现指定对话框的上边与屏幕上边的距离。如果省略该参数则对话框被放置在屏幕垂直方向距下边界大约三分之一的位置。Helpfile设置对话框的帮助文件可省略。Context设置对话框的帮助主题编号可省略。
例如接收用户的输入示例如下 Sub 使用 InputBox 函数 ()Dim strPrompt As StringDim strTitle As StringDim strDefault As StringDim strReturn As StringstrPrompt 请输入用户姓名strTitle 输入对话框strDefault MilkostrReturn InputBox (strPrompt, strTitle, strDefault)Debug.Print strReturnEnd Sub
运行上述代码会弹出如下的对话框 在文本框中输入新的姓名单击 “确定” 按钮程序将把用户输入的内容输出到 “立即窗口” 中。
使用 InputBox 函数时需注意
在默认情况下InputBox 函数的返回值是一个字符串类型而不是变体类型。如果需要使用该函数输入数值则需要使用 Val 函数或其他的转换函数将返回值转换为相应类型的数值。如果用户单击 “取消” 按钮或按 “Esc” 键则表示不使用当前输入的值函数将返回一个空字符串。根据这一特性可以判断用户是否把数据输入到对话框中。执行一次 InputBox 函数只能返回一个值如果需要输入多个值则必须多次调用该函数。
2.5.2.2 输出语句
1.Print 方法
上述接收用户输入的代码中用到了 Print 方法用于输出信息信息将被输出到 “立即窗口”视图→立即窗口或使用快捷键 CtrlG中。 Print 方法的语法如下
object.Print [outputlist]
在 VBA 中object 只能为 Debug 对象表示向 “立即窗口” 输出内容。
参数 outputlist 是要打印的表达式或表达式的列表。如果省略则打印一个空白行。
Print 首先计算表达式的值然后输出计算的结果。在 outputlist 参数中还可以使用分隔符以格式化输出的数据。格式化分隔符有以下 4 种。
Spc (n)插入 n 个空格到输出数据之间。Tab (n)移动光标到适当位置n 为移动的列数。分号表示前后两个数据项连在一起输出。逗号以 14 个字符为一个输出区每个数据输出到对应的输出区。
2.MsgBox 函数
使用 MsgBox 函数可打开一个对话框在对话框中显示一个提示信息并让用户单击对话框中的按钮使程序继续执行。MsgBox 有语句和函数两种格式语句格式如下
MsgBox prompt [,buttons][,title][,helpfile,context]
函数格式如下
ValueMsgBox (prompt [,buttons][,title][ ,helpfile,context])
通过函数返回值获得用户单击的按钮并可根据按钮的不同而选择不同的程序段来执行。
该函数或语句共有 5 个参数除第一个参数是对话框展示的提示外其余参数都可省略。各参数的意义与 Inputbox 函数参数的意义相同不同的是多了一个 buttons 参数。buttons 参数用来指定显示按钮的数目及形式、使用提示图标样式、默认按钮以及消息框的强制响应等。其常数值如下表所示。
常量值说明vbOkOnly0只显示 “确定”(Ok) 按钮vbOkCancel1显示 “确定”(Ok) 及 “取消”(Cancel) 按钮vbAbortRetrylgnore2显示 “异常终止”(Abort)、“重试”(Retry) 及 “忽略”(Ignore) 按钮vbYesNoCancel3显示 “是”(Yes)“否”(No) 及 “取消”(Cancel) 按钮vbYesNo4显示 “是”(Yes) 及 “否”(No) 按钮vbRetryCancel5显示 “重试”(Retry) 及 “取消”(Cancel) 按钮vbCritical16显示 CriticalMessage 图标vbQuestion32显示 WarningQuery 图标vbExclamation48显示 WarningMessage 图标vbInformation64显示 InformationMessage 图标vbDefaultButton10以第一个按钮为默认按钮vbDefaultButton2256以第二个按钮为默认按钮vbDefaultButton3512以第三个按钮为默认按钮vbDefaultButton4768以第四个按钮为默认按钮vbApplicationModal0进入该消息框当前应用程序暂停vbSystemModal4096进入该消息框所有应用程序暂停
表中的数值或常数可分为 4 组
第一组值05决定对话框中按钮的类型与数量。第二组值16324864决定对话框中显示的图标。第三组值0256512768设置对话框的默认活动按钮。活动按钮中文字的周围有虚线按 “Enter” 键可执行该按钮的单击事件代码。第四组值04096决定消息框的强制响应性。
buttons 参数可由上面 4 组数值组成其组成原则是从每一类中选择一个值把这几个值累加在一起就是 buttons 参数的值大部分时间里都只使用前三组数值的组合不同的组合可得到不同的结果。
如询问用户是否退出系统 Sub 使用 msgbox 函数退出系统 ()Dim intReturn As IntegerintReturn MsgBox (真的退出系统吗, vbYesNo vbQuestion, 提示)If intReturn vbYes Then Application.QuitEnd Sub
运行以上代码会弹出如下对话框 如果用户单击 “是” 按钮将执行 Quit 方法退出 Excel如果用户单击 “否” 按钮将返回应用程序。
2.5.3 暂停 / 退出语句
程序在运行过程中如果需要查看变量的运算中间值可在代码中加入暂停语句使程序暂停运行然后在立即窗口中查看各变量的值。当满足一定条件时可使用退出语句结束程序的运行。
2.5.3.1 暂停语句
在需要暂停程序执行的地方放置 Stop 语句即可让程序暂停运行。用 Stop 语句就相当于在程序代码中设置断点。
Stop 语句会暂停程序的执行但是它不像 End 语句因为 Stop 不会关闭任何文件或清除变量。暂停的程序又可以接着执行后续的代码。
Stop 语句主要用在调试程序阶段在应用程序发布之前需要将程序中的 Stop 语句删除或注释掉。
下面的代码使用 Stop 语句来暂停 “For…Next” 循环里的每一次完成。 Sub 暂停程序的执行 ()Dim i As IntegerFor i 1 To 10 开始 For...Next 循环Debug.Print i 将变量 i 的值输出到 “立即” 窗口Stop 每一次的完成都会在此暂停NextEnd Sub
运行以上代码每点击一次 “运行” 按钮立即窗口都会显示一个递增的值 2.5.3.2 退出语句
所谓的 “退出” 有两种意义一是退出正在执行的 VBA 代码返回到 VBA 的编辑环境中另一种是退出 Excel 系统。
1.End 语句
使用 End 语句可结束程序的运行返回到 VBA 的编辑环境中。执行 End 语句会重置所有模块级别变量和所有模块的静态局部变量。若要保留这些变量的值需改为使用 Stop 语句可以在保留这些变量值的基础上恢复执行。
End 语句提供了一种强迫终止程序的方法。VBA 程序正常结束应该卸载所有的窗体。只要没有其他程序引用该程序公共类模块创建的对象并且无代码执行程序将立即关闭。
注End 语句只是强制性地终止代码执行窗体和类模块中的 Unload、QueryUnload 和 Terminate 事件代码并未被执行。同时类模块创建的对象被破坏由 Open 语句打开的文件被关闭并且释放程序所占用的内存。其他程序的对象引用无效。
2.Quit 方法
使用 Application 对象的 Quit 方法将退出 Excel
Application.Quit
使用此方法时如果未保存的工作簿处于打开状态则 Excel 将显示一个对话框询问是否要保存所作更改。
2.6 程序结构
程序结构表示程序的运行方式。VBA 正是因为存在多种代码结构才能实现各类复杂的数据计算。常用的代码结构有顺序结构、选择结构和循环结构。
2.6.1 顺序结构
顺序结构即按照语句的书写顺序从上到下、逐条语句地执行程序。执行时排在前面的代码先执行排在后面的代码后执行执行过程中没有任何分支。顺序结构是最普遍的结构形式也是选择结构和循环结构的基础。
2.6.2 选择结构
选择结构又称为分支结构是根据 “条件” 来选择执行哪一分支中的语句包括二分支和多分支以及分支的嵌套。 2.6.2.1 If … Then 语句
选择结构中If Then 结构是最基础的一个。它只有条件表达式真时执行的代码。
用 If...Then 结构有条件地执行一个或多个语句有两种语法形式。
单行结构条件语句
单行结构条件语句是最基本的条件语句其语法为
If 条件表达式 Then 语句
条件表达式即可以任何计算数值的表达式VBA 将这个值解释为 True 或 False为零的数值为 False而任何非零数值都被看作 True。
该语句的功能为若逻辑表达式的值是 True则执行 Then 后的语句若逻辑表达式的值是 False则不执行 Then 后的语句而执行下一条语句。
2块结构条件语句
在 If…Then 语句中如果条件成立时需要执行多个操作可将多个语句写在 Then 后面并用冒号 : 分隔。If…Then 语句还提供另外一种块结构的方法可使执行多行代码的过程更清晰其语法如下 If 条件表达式 Then语句 1语句 2……End If
2.6.2.2 If … Then … Else 语句
在 If … Then 语句中当条件为 False 时不执行任何语句。若要求在条件为 False 时要执行另一段代码可用 If...Then…Else 语句。If...Then...Else 语句也有两种格式单行和多行。单行的格式为
If 条件表达式 Then 语句 1 Else 语句 2当 “逻辑表达式” 的值为 True 时执行关键字 Then 后面的 “语句 1”当 “逻辑表达式” 的值为 False 时执行关键字 Else 后面的 “语句 2”。
多行条件语句将根据条件表达式的值来判断并执行其中一个语句块。语法格式如下 If 逻辑表达式 Then语句序列 1Else语句序列 2End If
2.6.2.3 IIf 函数
IIf 函数可用来执行简单的条件判断操作它是 If…Then…Else 结构的简写版本其语法格式如下
resultIIf (条件表达式true 部分false 部分)
变量 result 保存函数的返回值。“条件表达式” 为判断的条件当条件为 True 时IIf 函数返回 “true 部分”当条件为 False 时IIf 函数返回 “false 部分”。
2.6.2.4 If … Then … ElseIf 语句
在很多情况下可能需要判断几个不同的条件并根据不同条件执行不同的语句。这时可使用 If…Then…ElseIf 语句来对多个不同条件进行判断并在多个语句块中选择执行其中的一个其语法格式如下 If 条件表达式 1 Then语句序列 1ElseIf 条件表达式 2 Then语句序列 2ElseIf 条件表达式 3 Then语句序列 3……Else语句序列 nEnd If
在以上结构中可以包括任意数量的 ElseIf 子句和条件ElseIf 子句总是出现在 Else 子句之前。
VBA 首先判断 “条件表达式 1” 的值。如果它为 False再判断 “条件表达式 2” 的值以此类推当找到一个为 True 的条件就会执行相应的语句块然后执行 End If 后面的代码。如要所有 “逻辑表达式” 都为 False且包含 Else 语句块则执行 Else 语句块。
2.6.2.5 Select Case 语句
在 If...Then 分支语句中总是可以添加更多的 ElseIf 块但是当每个 ElseIf 块都将相同的表达式比作不同的数值时这个结构编写起来很乏味也不易阅读。在这种情况下可以用多分支选择结构 Select Case 语句。
Select Case 语句的功能与 If...Then...Else 语句类似但对多重选择的情况Select Case 语句可使代码更易读。
Select Case 在结构的开始处理一个测试表达式并只计算一次然后VBA 将表达式的值与结构中的每个 Case 的值进行比较如果相等就执行与该 Case 相关联的语句块执行完毕再跳转到 End Select 语句后执行。其语法格式如下 Select Case 测试表达式Case 表达式列表 1语句序列 1Case 表达式列表 2语句序列 2……Case Else语句序列 nEnd Select
其中 “测试表达式” 可以是数值型或字符型的表达式通常是一个数值型或字符型的变量。表达式列表可以是一个或几个值的列表。如果在一个列表中有多个值就用逗号将各个值分隔开。每一个语句序列中含有零个或多个语句。如果不止有一个 Case 与测试表达式相匹配则只对第一个匹配的 Case 执行与之相关联的语句块如果表达式列表中没有一个值与测试表达式相匹配则 VBA 执行 Case Else 子句此项可选中的语句。
2.6.2.6 分支结构的嵌套
在一个分支结构语句中还可以包含另一个分支结构语句称为分支结构的嵌套。
2.6.3 循环结构
在 VBA 中循环结构用于多次重复执行同一段代码。重复次数通过特定数字或特定条件控制。
通过控制循环过程中特定变量循环结构可执行复杂的重复任务。 2.6.3.1 Do … Loop 循环
用 Do 循环重复执行一语句块且重复次数不定。Do…Loop 语句有 4 种演变形式但每种都需要计算条件表达式的值以决定是否继续执行。在 Do 循环中可以使用 Exit Do 语句中途退出该循环。
1.Do While…Loop 循环 Do While 语句属于先测试循环条件的 Do…Loop 语句其语法格式如下 Do While 条件表达式语句序列 1[Exit Do][语句序列 2]Loop
在 Do While 和 Loop 之间的语句称为循环体。
当 VBA 执行这个 Do 循环时首先判断条件表达式如果为 False或零则跳过所有语句执行 Loop 的下一条语句如果为 True或非零则执行循环体当执行到 Loop 语句后又跳回到 Do While 语句再次判断条件。在循环体中如果包含有 Exit Do 语句则当执行到 Exit Do 语句时马上跳出循环执行 Loop 的下一条语句。
这种形式的循环体可能执行零次或多次。只要条件表达式为 True 或非零循环就会重复执行。如果逻辑表达式最初就为 False则不会执行循环语句。
2.Do…Loop While 循环 Do...Loop While 语句属于后测试循环条件的 Do…Loop 语句该结构先执行循环体中的语句然后再进行条件判断。这种形式的循环体至少执行一次其语法格式如下 Do语句序列 1[Exit Do][语句序列 2]Loop While 条件表达式
3.Do Until…Loop 循环
Do Until…Loop 语句为先测试结束条件的 Do…Loop 语句其语法形式如下 Do Until 条件表达式语句序列 1[Exit Do][语句序列 2]Loop
这种形式与 Do While…Loop 类似不同的是当条件表达式的值为 False 时才执行循环体否则退出循环。这种形式的循环体可能执行零次或多次。
4.Do…Loop Until 循环
Do…Loop Until 语句是后测试结束条件的 Do…Loop 语句其语法形式如下 Do语句序列 1[Exit Do][语句序列 2]Loop Until 条件表达式
这种形式与 “Do…Loop While” 类似不同的是当条件表达式的值为 False 时才执行循环体否则退出循环。这种形式的循环体至少能被执行一次。
2.6.3.2 While … Wend 循环
While…Wend 循环语句的功能与 Do…While 循环相同是从 Basic 的早期版本中保留下来的语句VBA 保留它是为了向后兼容其语法格式如下 While 条件表达式循环体……Wend
如果 “条件表达式” 为 True则所有的 “循环体” 语句都会执行一直执行到 Wend 语句然后再回到 While 语句并再一次检查 “逻辑表达式” 的值如果还是为 True则重复执行如果不为 True则程序会从 Wend 语句之后的语句继续执行。
2.6.3.3 For … Next 循环
For…Next 语句以指定次数来重复执行循环体。与 Do 循环不同For 循环使用一个叫做计数器的变量每重复一次循环之后计数器变量的值就会增加或者减少。在 For 循环中可以使用 Exit For 语句随时退出该循环。For 循环的语法格式如下 For 循环变量 初始值 To 终值 [Step 步长值]语句序列 1[Exit For][语句序列 2]Next [循环变量]
其中步长值可正可负。如果步长值为正则初始值必须小于或等于终值才能执行循环体否则退出循环如果步长值为负则初始值必须大于或等于终值这样才能执行循环体。如果没有设置 Step则步长值默认为 1。
VBA 执行 For 循环时的过程如下所述。 将初始值赋值给循环变量。 判断循环变量是否超过终值若为真True退出循环执行 Next 的下一语句。这里的 “超过终值” 有两种意思若步长值为负数时超过就是循环变量的值小于终值而当步长值为正数时超过就是循环变量的值大于终值。 执行循环体。 循环体执行完后到达 Next 语句时循环变量累加上步长值。 重复步骤 2) 到步骤 4)。
For 循环一般都可计算出循环体的执行次数计算公式如下
循环次数 [(终值 - 初值)/ 步长值]1
中括号 [ ] 表示取整。
2.6.3.4 循环的嵌套
与分支结构类似循环结构也可进行嵌套即将一个循环放置在另一个循环中。VBA 允许在同一过程里嵌套多种类型的循环。
2.6.3.5 Goto 语句
使用 Goto 语句可无条件地将程序代码跳转到指定的行去执行。VBA 中保留 Goto 语句是为了保持与早期的 Basic 版本兼容。Goto 语句的语法格式如下 GoTo 行号 / 标号
注Go To 只能跳到它所在过程中的行。
要使用 Goto 语句首先需用了解 VBA 中语句的行号和标号。在早期的 Basic 语言中每一行程序都有一个行号行号按从小到大的顺序排列例如 Sub 使用 Do 循环删除为 0 的行 ()10 Dim i As Integer20 i 130 With Sheets (sheet2)40 Do While i 10050 If .Cells (i, 2) 0 Then60 .Cells (i, 2).EntireRow.Delete70 End If80 i i 190 Loop100 End WithEnd Sub
在 VBA 中执行这样的代码也不会出错。为程序添加行号的目的就是方便使用 Goto 语句跳转到相应的语句去执行。
随着结构化程序设计方法的使用使用 Goto 语句跳转的方式已经不常用了也就不再需要为每行代码添加行号了。
为了使 Goto 跳转到需要的地方可在程序中添加标号。标号是以英文字母开头的一个标识符后加上一个冒号构成的。在程序代码中输入的标号始终是靠左对齐的。
例如以下代码使用 Goto 语句来进行循环操作删除单元格为 0 的行
Sub 使用 GOTO 语句删除为 0 的行 ()Dim i As Integeri 1
line1:With Sheets (sheet2)If .Cells (i, 2) 0 Then.Cells (i, 2).EntireRow.DeleteEnd Ifi i 1If i 100 Then GoTo line1End With
End Sub
不建议在程序中使用太多的 Goto 语句因为使用 Goto 语句会使程序代码不容易阅读及调试。应尽可能使用结构化控制语句Do…LoopFor…NextIf…Then…Else 和 Select Case。
2.7 数组
对于大量有序的数据可以使用数组对其进行存储和处理。
在 VBA 中数组中各元素可以是相同的数据类型也可以是不同的数据类型。
2.7.1 声明数组
计算机中数组占用一片连续的内存空间。在使用之前必须先对数组进行声明定义让计算机系统为其分配一片连续的内存空间。
声明数组变量必须为数组指定大小。若数组的大小被指定则它是一个大小固定的数组若程序运行时数组的大小可以被改变则它是一个动态数组。
2.7.1.1 声明一维数组
有两种方法声明大小固定的一维数组。
方法 1
Dim 数组名 (上界) As 数据类型
方法 1 与其他程序设计语言定义数组的格式相同只给出数组下标的上界而省略下标的下界默认为 0即数组的下标从 0 开始至定义的上界。 例如定义一个名为 MyArray 的数组共有 11 个元素分别为 MyArray (0)MyArray (1)…MyArray (10)
Dim MyArray (10) As String
如果希望数组下标从 1 开始可以通过 Option Base 语句来设置
Option Base 1
注使用该语句指定的数组下标的默认下界只能为 0 或 1。该语句只能出现在用户窗体或模块的声明部分不能出现在过程中且必须放在数组定义之前。
方法 2
Dim 数组名 (下界 To 上界) As 数据类型
方法 2 可定义数组的下标下界为任意值。 例如定义一个数组 MyArray共有 12 个元素分别为 MyArray (-1)MyArray (0)MyArray (1)…MyArray (10)
Dim MyArray (-1 To 10) As String
2.7.1.2 声明二维数组
二维数组的声明方式与一维数组类似不同的是需要设置两个上下标变量。
方法 1只定义数组上界
Dim 数组名 (第 1 维上界第 2 维上界) As 数据类型
方法 2同时定义上下界
Dim 数组名 (第 1 维下界 To 第 1 维上界第 2 维下界 To 第 2 维上界) As 数据类型
声明多维数组的格式与二维数组类似每一维都使用逗号隔开即可。但需要注意的是定义大数据量的数组将占用很大的内存空间。
2.7.2 默认数组
默认数组即数据类型为 Variant默认类型的数组。使用默认数组的好处是可以让每个数组元素保存一个不同类型的值得到一种混合状态的数组。
VBA 中定义默认数组语法如下
Dim 数组名 (上界)
等同于
Dim 数组名 (上界) As Variant
2.7.3 动态数组
动态数组是指在程序运行时大小可以改变的数组。在声明动态数组时可使用变量来设置下标。
声明动态数组一般分两个步骤
在用户窗体、模块或过程中使用 Dim 或 Public 声明一个没有下标的数组不能省略括号在过程中用 ReDim 语句重新定义该数组的大小。 ReDim 语句在过程级别中使用用于为动态数组变量重新分配存储空间。其语法格式如下
ReDim [Preserve] 数组名 (下标) [As 数据类型]
可使用 ReDim 语句反复改变数组的元素及维数的数目但是不能在将一个数组定义为某种数据类型之后再使用 ReDim 将该数组改为其他数据类型除非是 Variant 所包含的数组。
在默认情况下使用 ReDim 语句重定义数组的维数和大小时数组中原来保存的值将全部消失。
如果使用 Preserve 关键字当改变原有数组最后一维的大小时可以保持数组中原来的数据。同时只能重定义数组最后一维的大小并且不能改变维数的数目。
2.7.4 数组的基本操作
声明一个数组后就可以对数组或数组中的元素进行操作了。操作通常包括数组的初始化、数组元素的复制及数组的删除等。
2.7.4.1 数组的初始化
在 VBA 中创建数组后将自动初始化数组中的每个元素对于数值型的数组每个元素都将清零如果是字符串型的数组每个元素将初始化为空字符串。
而如果是重定义动态数组并且使用了 Preserve 关键字则不会对已有元素进行初始化而只对新增加的元素进行初始化。
对数组元素的初始化一般使用循环语句来进行操作可让用户逐个输入数组的初始值也可按一定的规则读取工作表单元格中的值来对数组赋初始值还可使用 Array 函数来为数组元素赋值。
用户逐个输入数组的初始值
例如以下代码将反复显示输入框要求用户依次输入 10 个数组元素的初始值并保存到对应的数组元素中
For i 1 To 10MyArray (i) InputBox (请输入数组的第 i 个元素的值)
Next
注InputBox 函数返回的是字符串类型。如果数组定义为数值型还需使用相关的函数将用户输入的内容进行转换如用 Val 函数将字符串转换为数值也可以使用如 Ccur、Cdate、CDbl、Cint 等相关函数进行转换。
读取工作表单元格中的值来对数组赋初始值
由于工作表就相当于一个二维数组每个单元格就是二维数组中的一个元素。使用工作表中某部分单元格为数组赋初值时可通过两个循环的嵌套完成。
例如以下代码使用工作表单元格中的数据来为数组元素赋初始值
Sub ArrayInit () 使用工作表单元格中的数据来为数组元素赋初始值Dim MyArray (5, 5)Dim i As Integer, j As IntegerFor i 1 To 5For j 1 To 5MyArray (i, j) Worksheets (sheet2).Cells (i, j)Next jNext i
End Sub
使用 Array 函数来为数组元素赋值
Array 函数的语法如下
数组变量名 Array (数据集)
其中“数组变量名” 是预先定义的数组名后面不跟括号Array 函数中的 “数据集” 是由一些常数构成的数据集合各值之间用逗号分开。例如
Sub ArrayInit1 () 使用 Array 函数初始化数组Dim MyArray, i As IntegerMyArray Array (星期日, 星期一, 星期二, 星期三, 星期四, 星期五,
星期六)
End Sub
执行上述代码最终得到 MyArray (0)“星期日”、MyArray (2)“星期一”…MyArray (6)“星期六”。
注注因为 Array 函数的返回值为 Variant 类型所以在使用 Array 函数给数组变量赋初值之前定义数组时只能将其定义为 Variant 类型且不能设置其下标否则将显示 “不能给数组赋值” 的错误提示。
2.7.4.2 数组元素的复制
单个的数组元素可像普通变量一样进行访问和赋值。因此要将一个数组中的值复制到另一个数组时使用 For 循环语句将数组元素逐个元素复制即可
Sub 数组元素的复制 ()Dim MyArray1 (5) As Integer, MyArray2 (5) As IntegerDim i As IntegerFor i 1 To 5MyArray1 (i) i 将 MyArray1 初始化为 1~5NextFor i 1 To 5MyArray2 (i) MyArray1 (i) 将 MyArray1 中的元素对应复制到 MyArray2 中Next
End Sub
在以上代码中如果将数组 MyArray2 声明为一个 Variant 变量那么通过一条语句就能将 MyArray1 中的所有元素复制到 MyArray2 中
Sub 数组元素的复制 ()Dim MyArray1 (5) As Integer, MyArray2 As VariantDim i As IntegerFor i 1 To 5MyArray1 (i) iNextMyArray2 MyArray1 将数组元素保存到 Variant 变量中
End Sub
通过 MyArray2MyArray1 语句赋值后变量 MyArray2 将保存一个数组变量。可使用 MyArray2 (1)、MyArray2 (2) 的形式访问数组中每个元素的值。
2.7.4.3 数组的清除
对于静态数组当创建完成后其内存空间也分配完成数组的大小将不能再改变。
有时可能需要清除数组的内容或对数组重新定义可使用 Erase 语句重新初始化大小固定的数组的元素并释放动态数组的存储空间。其语法格式如下
Erase 数组名 1, 数组名 2, … …
Erase 语句根据静态数组还是动态数组来采取完全不同的行为
静态数组 固定数值数组Erase 语句将每个元素设为 0固定字符串数组 (长度可变)Erase 语句将每个元素设为零长度字符串 (“”)固定字符串数组 (长度固定)Erase 语句将每个元素设为 0固定 Variant 数组Erase 语句将每个元素设为 Empty用户定义类型的数组Erase 语句将每个元素作为单独的变量来设置对象数组将每个元素设为特定值 Nothing 动态数组Erase 语句将删除整个数组结构并释放动态数组所使用的内存。在下次引用该动态数组之前程序必须使用 ReDim 语句来重新定义该数组变量的维数。
简单来说静态数组在使用 Erase 语句后仍然存在只是其内容被清空而动态数组使用 Erase 语句后即不存在了。
2.7.4.4 数组相关函数
VBA 中操作数组的主要有 5 个函数Array、IsArray、Erase、LBound 和 UBound。
检测变量是否为数组
IsArray 函数可检查指定的变量是否为一个数组如果指定变量是一个数组返回值为 True否则返回 False。对于包含数组的 Variant 表达式来说IsArray 很常用。
Dim MyArray (1 To 5) As Integer, YourArray, MyCheck 声明数组变量
YourArray Array (1, 2, 3) 使用数组函数
MyCheck IsArray (MyArray) 返回 True
MyCheck IsArray (YourArray) 返回 True2获得数组下标的范围
LBound 函数和 UBound 函数可获得数组下标的下界和上界。其语法格式为
LBound (数组名 [, 维数])
UBound (数组名 [, 维数])如果省略 “维数” 参数表示返回一维的下标下界或上界。
例如定义数组
Dim A (1 To 100, 0 To 3, -3 To 4)则使用 LBound 函数的返回值分别为
LBound (A, 1) 返回值为 1
LBound (A, 2) 返回值为 0
LBound (A, 3) 返回值为 - 3则使用 UBound 函数的返回值分别为
UBound (A, 1) 返回值为 100
UBound (A, 2) 返回值为 3
UBound (A, 3) 返回值为 42.8 过程
过程是指由一组完成指定任务的 VBA 语句组成的代码集合。在 VBA 中可执行的代码都必须放置在过程中。
2.8.1 过程的分类
VBA 中的过程可分为事件过程、属性过程和通用过程。
2.8.1.1 事件过程
事件是某个特定的时刻所发生的事情它是对象状态转换过程的描述。事件没有持续时间是瞬间完成的。
事件过程是当发生某个事件如单击、双击时对该事件做出响应的程序段。在 VBA 中可以激发事件的用户动作包括切换工作表、选择单元格、单击鼠标等几十种事件。当事件发生时将执行包含在事件过程中的代码。如果用户没有定义某事件所调用的过程那么当发生该事件时就不会产生任何响应。
事件既可以由用户动作如单击鼠标、按键等产生也可以由程序代码或系统产生如计时器定时产生的事件。编写事件响应代码是在 “代码编辑器” 中进行的“代码编辑器” 的右边有一个事件列表这个事件列表从属于左边的控件对象当左边控件列表中的控件改变后右边的事件列表也会发生变化。 2.8.1.1.1 事件分类
Excel 提供了非常多的事件主要分为以下几类。 工作簿事件工作簿事件发生在特定的工作簿中如 Open打开工作簿、BeforeClose关闭工作簿之前和 SheetActivate激活任何一张表等。工作簿事件的代码必须在 ThisWork 对象代码模块中编写。 工作表事件工作表事件发生在特定的工作表中例如 Activate激活工作表、Change更改工作表中的单元格和 SelectionChange工作表上的选定区域发生改变等。工作表事件的代码必须在对应工作表的代码模块中编写。 窗体、控件事件新建的用户窗体及窗体上的控件也可响应很多事件例如 Click单击、Change控件内容更改等这类事件的代码必须编写在相应的用户窗体代码模块中。 图表事件图表事件针对某个特殊的图表产生如 Select选中了图表中的某个对象和 SeriesChange 改变了系列中的某个数据点的值。 不与对象关联的事件不与对象关联的事件只有两个分别是 OnTime 和 OnKey根据时间和按钮来产生这两个事件。
2.8.1.1.2 编写事件程序
以对工作簿的 Open 事件编写处理代码为例步骤如下
在 Excel 环境下按 “AltF11” 组合键打开 Visual Basic 编辑器。在左侧的 “工程资源管理器” 窗口中列出了当前工程项目中的 Excel 对象双击其中的 “ThisWorkbook” 对象将在右侧打开代码编辑器。在代码窗口左侧对象下拉列表框中选择对象。选择好对象 “Workbook” 后在代码窗口右侧的事件下拉列表框中可看到出该对象的事件列表。选择好对象和事件后系统自动生成事件过程的外部结构。在事件过程结构中编写响应该事件的代码即可。
2.8.1.2 属性过程
在 VBA 中除了模块、用户窗体外还提供了类模块。类模块向开发人员提供了创建和操作自己的对象类的功能。
在 “类模块” 中设置类的属性值时可通过 “属性过程” 来完成。
2.8.1.2.1 类
在 VBA 中有各种各样的对象如 Workbook、Worksheet 和 Range 等这些对象也都是通过相应的类来创建的。例如 Dim rngUser As Range 定义了一个对象变量 rngUser该变量存储对象 Range 的引用在这里Range 是 VBA 内部定义好的一个类标准类。
2.8.1.2.2 类的作用
VBA 提供类模块功能让开发者也可以根据需要定义自己的类。
使用类模块编写代码可以使程序更易管理、维护也更利于大型应用程序的开发。
1创建自定义对象
类模块可以用于在应用程序中建立和使用自己的对象类型。在应用程序开发过程中可以发现很多系统都要使用到相似的功能。这时可将已有系统中的代码复制到新系统中包括复制代码用到的数据再逐行检查代码并进行修改。如果没有能保证代码与数据之间关系的措施在操作过程中很可能会出现混乱从而导致程序出现错误。
其实将这些数据和程序包装在一个类中在新的系统中使用该类创建一个对象即可通过对象的属性、方法等来完成相应的功能。
2封装复杂过程
使用类还可以将复杂的过程封装在类的内部类的使用人员不需要知道类的实现过程只需要熟悉类的属性、方法和事件即可。
2.8.1.2.3 类的预备知识
1对象
对象是由类创建的一个实例它是类的实体化。对象的引用和操作被划分为以下三个部分。
属性是指对象的特性。例如 Worksheet 对象有包含单元格的行数、列数等属性。方法是指对象的某个操作。例如 Worksheet 对象有增加工作表、删除工作表等方法。事件是指对象对外部动作的响应。例如单击 Worksheet 对象的单元格时会产生一个 Change 事件修改单元格内容时会产生一个 SelectionChange 事件。
2变量的作用域
变量可以划分为过程级、模块级和全局变量。在类模块中对变量作用域的理解要注意以下两点。
由于类是生成对象的模板每一个对象相当于是类的一个副本对象之间是相互独立的因此模块级的变量只作用于对象自身对其他通过该类创建的对象不会起作用。在类模块中使用 Public 关键字声明的变量通过该类生成的对象都可访问。
3过程和函数
变量、过程和函数是标准模块中使用的最基本的构件。在类模块中它们仍然是最基本和最重要的角色。过程和函数并无实质的区别当需要返回值时就使用 Function如果不需要返回任何结果可使用 Function也可使用 Sub。
过程、函数也有作用域在标准模块中通过使用 Private 和 Public 关键字可以省略 Public 关键字因为它是默认的可以划分为模块级和全局级以决定它是在当前的模块有效还是整个工程有效。
同变量一样在类模块中使用 Public 关键字的函数或过程才能被声明的对象调用。使用 Private 关键字的函数或过程只能在类模块中进行调用。
2.8.1.2.4 创建类
1.建立对象类
在 VBA 编辑器中选择 “插入”→“类模块” 命令向工程插入一个类模块 在 “属性” 窗口中修改类的 “(名称)” 为 “clsPerson” 2.建立属性 建立属性的方法有两种 a. 最简单的方法是在类模块中声明 Public 变量作为对象的属性。这种方法的缺点是类不能知道属性值何时被外部过程修改了也就无法将属性值限定在一个有效的范围了。 b. 另一种方法是使用 Property 过程来定义属性值这时外部过程修改属性值时将执行 Property 过程。在该过程中可以编写代码对设置的值进行检查控制其在一个规定的范围中。
Property 过程有以下 3 种形式 Property Let这类过程用来设置类模块的属性值。 Property Get这类过程用来读取类模块的属性值。 Property Set这类过程用来设置对对象的引用。 在使用 Property 过程设置属性值时类模块中的变量可声明为 Private 类型避免过程直接修改类模块中变量的值。 创建属性时的 3 个步骤如下 ① 创建私有变量。在创建 clsPerson 类时需要在类模块的声明部分使用以下代码声明变量 Private m_ID As String 编号
Private m_Name As String 姓名
Private m_Sex As String 性别
Private m_Birthday As Date 出生年月
Private m_Company As String 工作单位 ② 创建 Property Get 过程来获取对象的属性值。 ③ 创建 Property Let 过程来设置上一步定义的私有变量的值。
3.创建 Property Get 过程 Property Get 语句用来获取对象的属性值。其基本形式就是一个声明和一个主体。声明包括属性名和数据类型。每个属性值都需要创建一个 Property Get 过程。
对于 clsEmp 类其 4 个属性值的 Property Get 过程代码如下 Property Get 过程
Property Get ID() As StringID m_ID 返回编号
End PropertyProperty Get PerName() As StringPerName m_Name 返回姓名
End PropertyProperty Get Sex() As StringSex m_Sex 返回性别
End PropertyProperty Get Birthday() As DateBirthday m_Birthday 返回出生日期
End PropertyProperty Get Company() As StringCompany m_Company 返回工作单位
End Property Property Let 过程
Property Let PerName(strName As String)m_Name strName 设置姓名属性
End PropertyProperty Let Sex(strSex As String)If strSex 男 And strSex 女 ThenExit PropertyElsem_Sex strSex 设置性别属性End If
End PropertyProperty Let Birthday(datBirthday As Date)m_Birthday datBirthday 设置出生日期属性
End PropertyProperty Let Company(strCompany As String)m_Company strCompany 设置工作单位属性
End Property5.创建对象类的方法 除了属性之外一般对象至少有一个方法。方法是对象可以执行的操作使用方法可以操作对象类中的数据。
在 clsPer 类中创建两个方法一个用于调换工作单位
Public Function ExchangeCom (strCompany As String) As Boolean
If strCompany Super 公司 And strCompany End 公司 Then
ExchangeCom False
Else
m_Company strCompanyExchangeCom True
End If
End Function
6.类模块的事件 事件是对象可识别的动作例如按钮对象可识别 Click 事件。自定义类模块有 Initialize 事件和 Terminate 事件这两个事件分别在类的实例初次创建时和最后一个指针释放或破坏时触发。可以用 Initialize 事件设置对象类的默认属性值用 Terminate 事件进行销毁对象前的整理工作。
在 clsPer 类中员工的编号不能通过 Property Let 过程设置为只读属性但可以在 Initialize 事件中编写代码对其进行赋值。编写 Initialize 事件代码的过程如下。
1在类模块代码窗口的对象下拉列表中选择 “Class”在事件下拉列表中选择 “Initialize”。
2编写以下代码
Private Sub Class_Initialize ()
Randomize
m_ID Int (10000 * Rnd) 生成一个 4 位数
m_ID Format (m_ID, 0000) 进行格式化处理
End Sub
在上述代码中通过随机函数 Rnd 得到一个 01 之间的小数再将其乘以 10 000 后取整可得到一个 4 位数。如果位数小于 4 位则使用 Format 函数在前面加上 0。
2.8.1.2.5 使用类模块创建对象
创建一个用户窗体在窗体中使用 clsPerson 类来管理人员资料。
设计新增人员窗体
设计一个新增人员窗体可接收用户输入人员的相关数据并将其保存到 clsPer 对象类中。具体的操作步骤如下
① 在 VBA 编辑器中选择 “插入”→“用户窗体” 命令向工程中增加一个用户窗体。
② 设置窗体的名称属性为 “frmMain”并向窗体中添加如下图所示的控件 ③ 在窗体模块的声明部分输入以下代码
Dim person As New clsPerson 声明一个 clsPer 对象
Public persons As New Collection 声明一个集合对象
④ 在窗体的 Initialize 事件中调用过程 DisableBox将窗体中的输入控件禁用禁止用户输入数据
Private Sub UserForm_Initialize ()Call DisableBox 调用过程禁止用户输入
End Sub
⑤ DisableBox 过程的代码即设置各控件的 Enable 属性为 False 即可EnableBox 则是设置为 True
Sub DisableBox ()txtName.Enabled FalsetxtBirthday.Enabled FalsetxtCom.Enabled FalseoptMan.Enabled FalseoptWoman.Enabled False
nd Sub
ub EnableBox ()
txtName.Enabled True
txtBirthday.Enabled True
txtCom.Enabled True
optMan.Enabled True
optWoman.Enabled True
nd Sub
⑥ 为 “新增” 按钮编写如下代码
Private Sub AddPerson_Click ()
If AddPerson.Caption 新 增 ThenAddPerson.Caption 保 存 更改按钮提示信息Call EnableBoxtxtName.Value txtBirthday.Value txtCom.Value txtName.SetFocus
ElseAddPerson.Caption 新 增 更改按钮提示信息With person.PerName txtName.Value 姓名If optMan Then 性别.Sex optMan.ValueElse: .Sex optWoman.ValueEnd IfIf IsDate (txtBirthday.Value) Then.Birthday CDate (txtBirthday.Value)) 出生日期Else.Birthday DateSerial (1900, 1, 1) 默认值End If.Company txtCom.Value 工作单位End Withpersons.Add person, person.PerName 将对象添加到集合中Call DisableBox 禁止用户录入
End If
End Sub
其中单击 “新增” 按钮时将窗体中的文本框等控件设置为允许输入状态先清空原有数据接受用户输入数据同时将按钮的提示文字改为 “保存”。 单击 “保存” 按钮时将窗体中输入的数据保存到 emp 对象的各个属性中并将 emp 对象添加到 emps 集合对象中同时修改按钮和文本框等控件的状态。
⑦ 当用户点击调整工作单位按钮时弹出相应的窗体代码如下
Private Sub Adjust_Com_Click ()frmCom.Show 调整人员工作单位
End Sub设计调整员工部门窗体
调整员工部门的窗体将调用 emp 对象的 ExchangeDep 方法来调整员工的工作部门。该窗体用于输入员工的姓名以及调整后的部门。具体步骤如下
① 在 VBA 编辑器中选择 “插入”→“用户窗体” 命令向工程中增加一个用户窗体。
② 设置窗体的名称属性为 “frmCom”并向窗体中添加如下图所示的控件 为 “保存” 按钮编写代码如下
Private Sub Confirm_Click ()Dim person As clsPersonDim b As Booleanb FalseFor Each person In frmMain.personsIf person.PerName txtName.Value Thenperson.ExchangeCom txtCom.ValueMsgBox 人员姓名“ txtName.Value ” 的新工作单位为 person.Companyb TrueEnd IfNextIf Not b ThenMsgBox 输入的人员姓名有误End If
End Sub
测试结果 2.8.1.3 通用过程
有时在不同的事件过程中需要执行一段相同的代码可以把这段代码独立出来作为一个过程这样的过程称为 “通用过程”。
在 VBA 中通用过程又分为两类Sub子程序过程和 Function函数过程。
Sub 过程这类过程完成指定任务执行结束后不返回值。Function 过程这类过程完成指定的任务并返回一个值供调用程序使用。
一般通用过程保存在 VBA 的 “模块” 中一个模块可以包含任意数量的过程同时一个 VBA 工程可以包括任意多个模块。
2.8.2 定义 Sub 过程
VBA 中有两种定义过程的方法一种方法是使用窗体创建过程的结构再在过程中编写相应的代码另一种方法是在模块中直接输入代码来定义过程。
2.8.2.1 使用窗体创建 Sub 过程
在 VBA 编辑器中选择 “插入”→“过程” 命令打开 “添加过程” 对话框 在这个对话框中除了可以插入一个新的 Sub 过程、Function 过程或属性过程还可以设置 Public 或 Private 有效范围并使得过程中所有的局部变量成为静态变量。 在 “名称” 后的文本框中输入过程的名称并在 “类型” 栏中选择 “子程序” 单选框接着在 “范围” 栏中选中 “公共的” 单选框设置过程为全局的即在工程的各模块中都可以调用该过程将在过程前面添加 Public 关键字。还可根据需要选中下方的 “把所有局部变量声明为静态变量” 复选框如果选中该复选框将在过程名前面添加 Static 关键字。 单击 “确定” 按钮VBA 将自动生成过程的结构代码 2.8.2.2 使用代码创建 Sub 过程
Sub 过程的结构如下
[Private | Public | Friend] [Static] Sub 过程名 [(参数列表)][语句序列 1][Exit Sub][语句序列 2]
End Sub
过程由 Sub、End Sub 及它们之间的 VBA 代码构成。其中在 Sub 前面可加上限制过程作用域的关键字主要有以下几个:
Private表示只有在包含其声明的模块中的其他过程可以访问该 Sub 过程。Public表示所有模块的所有其他过程都可访问这个 Sub 过程。如果在 Sub 前面省略关键字则表示其为 Public。Friend只能在类模块中使用表示该 Sub 过程在整个工程中都是可见的但对对象实例的控制者是不可见的。Static表示在调用时保留 Sub 过程的局部变量的值。Static 属性对在 Sub 之外声明的变量不会产生影响即使过程中也使用了这些变量。
End Sub 语句标志着 Sub 过程的结束。为了能正确运行每个 Sub 过程必须有一个 End Sub 语句当程序执行到该语句时就结束该过程的运行。另外在过程中可以使用一个或多个 Exit Sub 语句直接退出过程的执行。
需要注意的是Sub 过程的定义不能嵌套即不能将过程的定义放在另一个过程中。
2.8.3 调用 Sub 过程
在 VBA 中通过调用定义好的过程来执行程序。Sub 过程的调用分两种方式一种是在 VBA 代码中调用 Sub 过程另一种是在 Excel 中以调用宏的方式来执行 Sub 过程。
2.8.3.1 使用 VBA 代码调用 Sub 过程
在程序中调用 Sub 过程有两种方式一种是把过程名放在一个 Call 语句中另一种是把过程名作为一个语句来使用。 用 Call 语句调用 Sub 过程 用 Call 语句可将程序执行控制权转移到一个 Sub 过程或 Function 过程中在过程中遇到 End Sub 或 Exit Sub 语句后再将控制权返回到调用程序的下一行。Call 语句的语法格式如下 Call 过程名 (过程参数列表) 如果过程没有参数可省略过程名后的括号 将过程作为一个语句 在调用过程时如果省略 Call 关键字过程也可调用。与使用 Call 关键字不同的是如果过程有参数这种调用方式必须要省略 “参数列表” 外面的括号。 即 过程名 过程参数 1, 过程参数 2, ...
2.8.3.2 以宏方式调用 Sub 过程
在 VBA 中可以将 Sub 过程作为一个宏来调用。 切换到 Excel 工作界面。 在工作表中任意位置插入并绘制一个按钮 在打开的 “指定宏” 界面中选择 VBA 中写好的 Sub 过程名称并点击确定 点击该创建的按钮即运行相应的 Sub 过程。
注以宏方式调用 Sub 过程无法给过程传递参数因此需要用参数的过程不会显示在宏列表中。
2.8.4 传递参数
为了使过程更具有通用性大多过程都需要设置参数。传递不同的参数给过程能有不同的执行结果。
对于有参数的过程在调用时必须将实际参数传递给过程完成形参与实参的结合过程再使用实参执行代码。
2.8.4.1 形参与实参的结合
形参即形式参数的简称是在 Sub 过程的定义中出现的变量名。因其没有具体的值只是形式上的参数所以称为形参。
实参即实际参数的简称是在调用 Sub 过程时传递给 Sub 过程的值。在 VBA 中实参可为常量、变量、数组或对象类的数据。
在 VBA 中形参与实参的结合有两种方式。 按位置结合 大多数程序语言调用子过程时都按位置结合形参与实参。 按位置结合即调用 Sub 过程时使用的实参次序必须与定义 Sub 过程时设置的参数次序相对应。例如使用以下代码定义 Sub 子过程 Sub Test (arg1 As Integer, arg2 As Long, arg3 As String)
...
End Sub 子过程中定义了 3 个参数。可使用以下语句调用该子过程 Call Test (1, 2, abc) 按命名参数方式结合 形参与实参的另一种结合方式是按形参名称来进行的即在调用 Sub 过程时输入形参的名称将形参名称与实参用符号 : 连接起来。与按位置结合方式不同使用这种方式时调用过程的参数位置可随意设置。 例如使用命名结合的方式调用上面定义的 “Test” 子过程 Call Test (arg1:1, arg3:abc, arg2:2)
2.8.4.2 传地址
在 VBA 中实参可通过两种方式将数据传递给形参即传地址和传值。
传地址是 VBA 默认的方式在定义过程时如果在形参前面有关键字 ByRef则该参数通过传地址的方式传递。传地址是指将实参变量的地址传递给形参这让形参和实参都代表同一个内存区域。
例如
Sub 传地址测试 (ByRef a As Integer)a a 1Debug.Print 子过程中的变量 A a
End Sub
由于 Sub 过程不能返回运算结果如果需要 Sub 过程返回值时可通过使用 ByRef 方式来定义形参就可将子过程的运算数据返回到调用程序中。
2.8.4.3 传值
传值就是将实参的值作为一个副本赋值给形参而不是传送实参的地址给形参。定义过程时在形参的前面添加 ByVal 关键字则该参数就按传值方式传递否则用传地址方式传递。
使用传值方式传递参数时传递的只是变量的副本。如果过程改变了形参的值所做改变也只在过程内部起作用不会影响到调用程序中变量的值。
例如
Sub 传值测试 (ByVal a As Integer)a a 1Debug.Print 子过程中的变量 A a
End Sub
在程序中使用传地址方式比传值方式效率高但是传地址方式中形参不是一个真正的局部变量有可能对程序产生不必要的影响。如无特殊需求应尽量使用传值方式。
2.8.4.4 传递数组参数
数组作为在内存中的一片连续区域也可作为一个参数传递给 Sub 子过程进行处理。数组一般是通过传地址方式进行传递的。
例如编写一个求数组中最大数的过程
Sub 求最大数 (a () As Integer)Dim i As Integer, max As Integermax a (LBound (a))For i LBound (a) To UBound (a)If a (i) max Then max a (i)NextDebug.Print 最大数: max
End Sub
在该过程中将形参定义为一个数组。使用数组作为形参时必须输入数组名并跟上一对空括号。对传递到过程的数组应使用 LBound 函数和 UBound 函数获取其下标的下界和上界接着程序才能遍历数组或对数组进行其他相关的操作。
2.8.5 可选参数和可变参数
在创建 VBA 的过程时除了可设置参数按地址或按值传递之外还可以根据需要为过程设置可选参数和可变参数。
2.8.5.1 可选参数
通常情况下一个 VBA 过程中的形参数量是固定的调用时提供的实参数量也是固定的。但在有的过程中可能有必需收集信息和可选信息例如收集顾客的信息时必须提供 “姓名”、“性别”而 “身份证号码” 则是可选的。
VBA 的过程可以通过对形参前面加上 Optional 关键字来设置该形参为可选参数。在过程内部通过使用 IsMissing 函数可测试调用程序是否传递了该可选参数。例如
Sub 可选参数 (strName As String, strSex As String, Optional ID)With Worksheets (sheet2).Range (A2) strName.Range (B2) strSexIf Not IsMissing (ID) Then.Range (C2) IDEnd IfEnd With
End Sub
注过程中可定义多个可选参数但可选参数必须放在参数表的最后而且必须是 Variant 类型。
2.8.5.2 可变参数
无论是固定参数还是可选参数在定义过程时都已经指定了参数的个数。在 VBA 中还可以定义可变参数即参数的个数在定义时是未知的。
在定义过程的参数表时在最后一个参数前面加上 ParamArray 关键字过程将接受任意个数的参数。例如使用可变参数编写求和函数 Sum
Sub MySum (intTotal As Integer, ParamArray intNum ())Dim i As Integer, j As IntegerFor i LBound (intNum) To UBound (intNum)intTotal intTotal intNum (i)Next
End Sub
该过程中可变参数为一个数组。程序中使用 LBound 函数和 UBound 函数获得数组下标的上下界然后进行累加并将累加的结果保存在第一个参数中用于返回给调用程序。调用以上子过程的代码如下
Sub 调用可变参数 ()Dim i As IntegerMySum i, 1, 2, 3, 4, 5, 6, 7, 8Debug.Print i
End Sub
程序中定义了一个变量 i用来获得子过程运算的结果。参数传递时将参数 i 用传地址方式传递给 “MySum” 子过程的形参 intTotal将后面的 “1、2、3、4、5、6、7、8” 作为一个数组传递给形参 intNum。
注ParamArray 只能用于参数列表的最后一个参数指明最后这个参数是一个 Variant 元素的 Optional 数组。另外ParamArray 关键字不能与 ByVal、ByRef 或 Optional 一起使用。
2.9 函数
2.9.1 函数与过程
Function 函数和 Sub 过程都属于 VBA 的通用过程。
Function 函数和 Sub 过程的相同点有
都是构成 VBA 程序的基本单位。都可用 Public、Private 等关键字设置过程的作用区域。都可接收参数参数的设置相同如传递方式、可选参数、可变参数等。
Function 函数和 Sub 过程的不同点有
Sub 过程不能返回一个值而 Function 函数可以返回一个值因此 Function 函数可以像 Excel 内部函数一样在表达式中使用。Sub 过程可作为 Excel 中的宏来调用而 Function 函数不会出现在 “选择宏” 对话框中要在工作表中调用 Function 函数可像使用 Excel 内部函数一样。在 VBA 中Sub 过程可作为独立的基本语句调用而 Function 函数通常作为表达式的一部分。
2.9.2 定义函数
Function 函数的创建方法与 Sub 过程的方法类似。在使用 Function 函数时一般需要使用一个变量来接收返回值。
2.9.2.1 创建函数
创建函数有两种方法
方法 1
通过对话框和手工输入代码。通过对话框创建函数的方法与创建 Sub 过程相似在 VBA 编辑器中选择 “插入”→“过程” 命令打开 “添加过程” 对话框接着在 “添加过程” 对话框在 “类型” 栏中选择 “函数” 单选框输入函数名称即可创建函数的结构。 方法 2
输入代码创建 Function 函数Function 结构如下
[Public | Private | Friend] [Static] Function 函数名 [(参数列表)] [As 返回类型]语句序列 1函数名 表达式 1Exit Function语句序列 2函数名 表达式 2
End Function
与 Sub 过程不同的是Function 函数在声明函数名的第一行使用 “As 返回类型” 定义函数的返回值类型在函数体内通过给函数名赋值来返回计算结果。
如果在函数体内没有赋值返回计算记过语句则该函数返回一个默认值数值函数返回 0字符串函数返回空字符串。
例如使用 Function 函数实现数组求和功能
Function MySum (ParamArray intNum ()) As LongDim i As Integer, j As LongFor i LBound (intNum) To UBound (intNum)j j intNum (i)NextMySum j
End Function
2.9.2.2 调用函数
调用 Function 函数也有两种方式一种是在工作表的公式中使用另一种是从 VBA 的另外一个过程里调用。
1在工作表中调用函数
自定义 Function 函数和系统内置函数一样可在 Excel 工作表的公式中进行引用。如果不知道 Function 函数的名称或它的参数可以使用 “插入函数” 对话框帮助用户向工作表中输入这些函数。例如要在工作表中引用 “MySum” 函数的过程如下。
① 在 Excel 窗口中单击选择一个单元格。
② 在功能区 “公式” 选项卡的 “函数库” 组中点击 “插入函数” 按钮将打开 “插入函数” 对话框 ③ 选择 “用户定义” 类别并点击 “确定” ④ 在 “函数参数” 对话框可输入函数所需要的参数并点击 “确定” 即可 2在 VBA 代码中调用函数
Function 函数与内部函数没有什么区别只不过内部函数 VBA 由系统提供而 Function 函数是由用户自己定义的。因此可以像使用 VBA 内部函数一样来调用 Function 函数。例如以下代码调用 MySum 函数并将计算结果保存到 t 中 Sub test1 ()Dim t As Longt MySum (1, 3, 8, 10, 12)Debug.Print tEnd Sub还可将 Function 函数作为表达式的一部分使用其返回值参加表达式的运算例如 t t MySum (1, 3, 8, 10, 12) * 33 Excel 应用程序开发流程
3.1 需求分析
在进行 Excel 应用程序开发时首先需要准确了解用户的需求需求分析的目的是确定需要完成哪些工作。
需求分析阶段的主要任务如下
功能需求给出应用程序必须完成的所有功能。环境需求用户的计算机硬件环境、软件环境和 Excel 的版本等。界面需求应用程序的用户界面是直接面对用户的界面设计是用户能否方便、快捷地操作应用程序的关键之一。在需求分析阶段应提出界面的需求。安全保密需求对客户信息的保密要求应在本阶段进行计划。用户技术层次在需求分析阶段了解用户的技术层次可为应用程序的开发提供一些辅助信息。
3.2 界面设计
在 Excel 中进行界面设计的方式主要有以下 3 种
在工作表中添加控件
在较简单的应用程序中只需要调用少数几个宏过程可向工作表中添加按钮或其他控件接着与宏过程进行绑定即可 用户窗体
用户界面是应用程序的一个重要组成部分。在 Excel 的应用程序中用户窗体作为应用程序的用户界面部分将用户的操作和 Excel 工作表中的数据隔离开。
在 VBA 编辑器中插入一个用户窗体 使用工具箱视图→工具箱打开中的控件设计窗体 自定义功能区
功能区是从 Excel 2007 起新增加的组件取代了以往版本的菜单和工具栏可使用 XML 代码自定义功能区。 3.3 代码设计
将用户界面设计好以后接下来就需要编写界面中各部分的事件代码如用户窗体中的按钮、功能区中的按钮等。
在 Excel 中设计 VBA 应用程序时界面设计和代码设计一般是交替进行的即设计好一个界面后就编写相应的代码。有时也可先录制修改好宏代码再和工作表或用户窗体中的按钮进行绑定。
3.4 帮助系统
在 Windows 应用程序中提供了在线电子文档的帮助系统Excel 也可以制作这种帮助系统。 对于小型应用系统一般不提供帮助系统但对于一个大型应用系统提供一个好的帮助系统可让用户更快地理解系统更快地熟悉系统的功能。
3.5 系统测试
在创建了应用程序之后必须对其进行测试测试和调试应用程序所花费的时间可能与开发系统的时间同样多。
对于一个完成开发的应用程序在设计测试数据时应尽可能多地考虑到各种不同的情况不但要使用正常的合乎逻辑的数据去测试应用程序的功能性还应使用一些可能导致应用程序出错的数据去测试应用程序的健壮性。
在设计测试数据的同时应编写出测试数据的结果并与应用程序进行实测时得到的数据进行对比如果结果相同则通过测试否则应检查并修改应用程序。
3.6 应用程序发布
通过测试后的应用程序就可以发布给最终用户使用了。在发布时需要注意以下 3 个问题:
Excel 版本如果是在 Excel 特定版本环境下开发的应用程序并使用了该特定版本的一些新功能就要求用户使用该 Excel 的特定版本。动态链接库如果应用程序中使用了 ActiveX 控件则需要考虑是否要将包含该 ActiveX 控件的 DLL 文件或 OCX 文件包含在应用程序中予以发布。辅助文件在一个大型的应用程序中有时可能还会使用到其他辅助文件如图片文件、数据库文件和帮助文件等需要将这些文件包括在发布文件中并且最好将其发布到其他盘符中进行测试以检查在 VBA 代码中是否使用了绝对路径来引用相关的文件。 Excel VBA 执行速度慢
posted 2022-01-14 17:32 NewJune
Excel 是办公利器尤其在办公室Excel 用的熟练与否会的 Excel 知识点多不多很大程度上决定了你工作是否高效能否按时打卡下班。
可我们也时常听到这样的吐槽Excel 好是好可就是表格大了之后公式多了之后它运算起来忒慢了。
我们写 VBA 宏除了要实现特定的自动化功能还肩负一个极其重要的使命让 Excel 快起来那么如何优化 VBA 代码给 Excel 宏提速呢
方法一启用【手动计算】
【公式】菜单栏有【计算选项】可以选择【自动计算】、【手动计算】一般 Excel 默认是自动计算。如果工具是 VBAExcel 公式结合那么 VBA 每执行一行更改了某些单元格值则整个 Excel 都会自动计算一遍公式。我们完全可以在 VBA 代码开始时先切换到【手动计算】等 VBA 主体执行完后再切换到【自动计算】减少过程中不必要的计算开销。 具体到代码层面写起来也是非常简便
Application.Calculation xlManual 手动在【手动】状态下需要点击【开始计算】来触发 excel 计算公式
Application.Calculate 开始计算Application.Calculation xlAutomatic 自动计算方法二关掉 Excel 窗口的刷新功能
当 VBA 代码飞速执行时伴随着单元格值在不停变化Excel 界面也在快速计算和刷新着这都会拖慢 VBA 的执行速度因此大多数情况下我们可以选择关掉页面的刷新执行完不要忘记恢复它的刷新功能代码如下
Application.ScreenUpdating False 关掉屏幕刷新
Application.ScreenUpdating True 重新启用屏幕的刷新功能方法三代码中少用 Excel 公式多用字典
很多刚开始写 VBA 的人由于对语法还不是很熟悉这类人更倾向于在脚本里大量调用 EXCEL 基本的公式如 application.WorksheetFunction.VLookup ()这样可以显著提升脚本的开发效率本是无可厚非的事儿。随着我们对 VBA 原生语法和数据类型越来越熟悉不妨把 Vlookup、Hlookup 等函数替换为用 VBA 字典实现。它语法更灵活可以轻松实现表格从右往左的反向查找。因为字典这类数据结构Key,Value) 查询要比 Vlookup 等公式的匹配速度快很多。
方法四把 Excel 文件当数据库来访问
把每个工作表看作是数据库表用 SQL 查询来提升 VBA 速度。VBA 中使用 SQL结构化查询语言Structured Query Language) 连接某个 Excel 数据源DataSource可以隐式连接而无需像 workbooks.open 那样显示加载打开的表对代码速度的提升显而易见。使用 SQL 来过滤、筛选、条件判断、分组、求最值等更是可以让 VBA 速度快到起飞数据透视表都瞬间变得不香了。唯一的缺点SQL 的学习成本比 VBA 和公式还是要稍高些间接拉高了学习门槛。但话又说回来真正会写 SQL 之后你会发现以前一些略显复杂的需求瞬间变得 SO EASY。大胆去学吧相信你绝不会后悔
改进了 VBA 代码中这些细节后你会发现VBA 原来可以这么快 via: VBA API 详解-CSDN博客 Office 二次开发于 2020-12-17 22:27:10 发布 https://blog.csdn.net/qq_25686631/article/details/111191241 【数据分析】Excel 中使用 VBA 进行宏编程_excel vba编程教程-CSDN博客 STARBLOCKSHADOW 已于 2024-05-19 19:35:55 修改 https://blog.csdn.net/qq_41084756/article/details/135892963 嫌 Excel VBA 执行速度慢这些建议你一定要看 - NewJune - 博客园 posted 2022-01-14 17:32 NewJune https://www.cnblogs.com/new-june/p/15802804.html
