VBA - 快速指南
VBA - Overview
VBA代表V isual B asic for A pplications是A来自Microsoft的事件驱动编程语言,现在主要用于Microsoft Office应用程序,如MSExcel,MS-Word和MS-Access。
它帮助技术人员构建定制的应用程序和解决方案,以增强这些应用程序的功能。 这种设施的优点是你不需要在我们的电脑上安装visual basic,但安装Office会隐含地帮助实现这个目的。
您可以在所有办公室版本中使用VBA,从MS-Office 97到MS-Office 2013,以及任何可用的最新版本。 在VBA中,Excel VBA是最受欢迎的。 使用VBA的优点是您可以使用线性编程在MS Excel中构建非常强大的工具。
VBA的应用
您可能想知道为什么在Excel中使用VBA,因为MS-Excel本身提供了大量内置函数。 MS-Excel仅提供基本的内置函数,这些函数可能不足以执行复杂的计算。 在这种情况下,VBA成为最明显的解决方案。
例如,使用Excel的内置公式计算贷款的每月还款非常困难。 相反,很容易为这样的计算编程VBA。
访问VBA编辑器
在Excel窗口中,按“ALT + F11”。 将打开VBA窗口,如以下屏幕截图所示。
VBA - Excel Macros
在本章中,您将学习如何逐步编写一个简单的宏。
Step 1 - 首先,在Excel 20XX中启用“开发人员”菜单。 要执行相同操作,请单击文件→选项。
Step 2 - 单击“自定义功能区”选项卡,然后选中“开发人员”。 单击“确定”。
Step 3 - “开发人员”功能区出现在菜单栏中。
Step 4 - 单击“Visual Basic”按钮以打开VBA编辑器。
Step 5 - 通过添加按钮开始编写脚本。 单击插入→选择按钮。
Step 6 - 执行右键单击并选择“属性”。
Step 7 - 编辑名称和标题,如以下屏幕截图所示。
Step 8 - 现在双击按钮,将显示子程序大纲,如下面的屏幕截图所示。
Step 9 - 通过简单地添加消息开始编码。
Private Sub say_helloworld_Click()
MsgBox "Hi"
End Sub
Step 10 - 单击按钮执行子过程。 子过程的输出显示在以下屏幕截图中。
Note - 在后面的章节中,我们将演示使用一个简单的按钮,如步骤1到10所述。因此,彻底理解本章很重要。
VBA - Excel Terms
在本章中,您将了解常用的Excel VBA术语。 这些术语将用于其他模块,因此理解其中的每一个都很重要。
单元 (Modules)
模块是编写代码的区域。 这是一个新的工作簿,因此没有任何模块。
要插入模块,请导航到“插入”→“模块”。 插入模块后,将创建“module1”。
在模块中,我们可以编写VBA代码,代码在过程中编写。 过程/子过程是一系列VBA语句,用于指示要执行的操作。
过程 (Procedure)
过程是一组作为整体执行的语句,它指示Excel如何执行特定任务。 执行的任务可以是非常简单或非常复杂的任务。 但是,将复杂的程序分解为较小的程序是一种很好的做法。
程序的两种主要类型是Sub和Function。
Function
函数是一组可重用的代码,可以在程序中的任何位置调用。 这消除了一遍又一遍地编写相同代码的需要。 这有助于程序员将大型程序划分为许多小型且易于管理的功能。
除了内置函数,VBA还允许编写用户定义的函数,并在Function和End Function之间写入语句。
Sub-Procedures
子程序与功能类似。 虽然子程序不返回值,但函数可能会也可能不会返回值。 子程序可以不带调用关键字调用。 子过程始终包含在Sub和End Sub语句中。
VBA - Macro Comments
注释用于记录程序逻辑和用户信息,以便其他程序员可以在将来无缝地处理相同的代码。
它包括诸如由开发,修改,以及还包括合并逻辑的信息。 执行时解释器会忽略注释。
VBA中的注释由两种方法表示。
任何以单引号(')开头的语句都被视为注释。 以下是一个例子。
' This Script is invoked after successful login
' Written by : IoWiki
' Return Value : True/False
任何以关键字“REM”开头的语句。 以下是一个例子。
REM This Script is written to Validate the Entered Input
REM Modified by : IOWIKI/user2
VBA - Message Box
MsgBox function显示一个消息框,等待用户单击按钮,然后根据用户单击的按钮执行操作。
语法 (Syntax)
MsgBox(prompt[,buttons][,title][,helpfile,context])
参数描述 (Parameter Description)
Prompt - 必需参数。 在对话框中显示为消息的字符串。 提示的最大长度约为1024个字符。 如果消息扩展到多行,则可以使用回车字符(Chr(13))或每行之间的换行符(Chr(10))分隔行。
Buttons - 可选参数。 一个数字表达式,指定要显示的按钮类型,要使用的图标样式,默认按钮的标识以及消息框的模态。 如果留空,则按钮的默认值为0。
Title - 可选参数。 字符串表达式显示在对话框的标题栏中。 如果标题留空,则应用程序名称将放在标题栏中。
Helpfile - 可选参数。 一个String表达式,用于标识用于为对话框提供上下文相关帮助的帮助文件。
Context - 可选参数。 一个数字表达式,用于标识“帮助”作者分配给相应“帮助”主题的“帮助”上下文编号。 如果提供了上下文,则还必须提供helpfile。
Buttons参数可以采用以下任何值 -
0 vbOKOnly - 仅显示“确定”按钮。
1 vbOKCancel - 显示“确定”和“取消”按钮。
2 vbAbortRetryIgnore - 显示“中止”,“重试”和“忽略”按钮。
3 vbYesNoCancel - 显示“是”,“否”和“取消”按钮。
4 vbYesNo - 显示“是”和“否”按钮。
5 vbRetryCancel - 显示“重试”和“取消”按钮。
16 vbCritical - 显示“关键消息”图标。
32 vbQuestion - 显示警告查询图标。
48 vbExclamation - 显示警告消息图标。
64 vbInformation - 显示信息消息图标。
0 vbDefaultButton1 - 第一个按钮是默认值。
256 vbDefaultButton2 - 默认为第二个按钮。
512 vbDefaultButton3 - 默认为第三个按钮。
768 vbDefaultButton4 - 第四个按钮是默认值。
0 vbApplicationModal应用程序模式 - 当用户响应消息框之前,当前应用程序将无法工作。
4096 vbSystemModal系统模式 - 在用户响应消息框之前,所有应用程序都无法运行。
以上值在逻辑上分为四组: first group (0到5)表示要在消息框中显示的按钮。 second group (16,32,48,64)描述了要显示的图标的样式, third group (0,256,512,768)表示哪个按钮必须是默认按钮, fourth group (0,4096) )确定消息框的模态。
返回值 (Return Values)
MsgBox函数可以返回以下值之一,可用于标识用户在消息框中单击的按钮。
- 1 - vbOK - 单击确定
- 2 - vbCancel - 单击取消
- 3 - vbAbort - 单击Abort
- 4 - vbRetry - 单击“重试”
- 5 - vbIgnore - 单击忽略
- 6 - vbYes - 单击是
- 7 - vbNo - 没有点击
例子 (Example)
Function MessageBox_Demo()
'Message Box with just prompt message
MsgBox("Welcome")
'Message Box with title, yes no and cancel Butttons
int a = MsgBox("Do you like blue color?",3,"Choose options")
' Assume that you press No Button
msgbox ("The Value of a is " & a)
End Function
输出 (Output)
Step 1 - 可以通过单击VBA窗口上的“运行”按钮或通过从Excel工作表调用该功能来执行上述功能,如以下屏幕截图所示。
Step 2 - 显示一个简单消息框,其中显示消息“欢迎”和“确定”按钮
Step 3 - 单击确定后,将显示另一个对话框,其中包含一条消息以及“是,否和取消”按钮。
Step 4 - 单击“否”按钮后,该按钮(7)的值将存储为整数,并显示为消息框给用户,如以下屏幕截图所示。 使用此值,可以了解用户点击了哪个按钮。
VBA - InputBox
InputBox function提示用户输入值。 输入值后,如果用户单击确定按钮或按键盘上的ENTER,则InputBox函数将返回文本框中的文本。 如果用户单击“取消”按钮,该函数将返回一个空字符串(“”)。
语法 (Syntax)
InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile,context])
参数描述 (Parameter Description)
Prompt - 必需参数。 在对话框中显示为消息的字符串。 提示的最大长度约为1024个字符。 如果消息扩展到多行,则可以使用回车字符(Chr(13))或每行之间的换行符(Chr(10))分隔行。
Title - 可选参数。 字符串表达式显示在对话框的标题栏中。 如果标题留空,则应用程序名称将放在标题栏中。
Default - 可选参数。 用户希望显示的文本框中的默认文本。
XPos - 可选参数。 X轴的位置表示从屏幕左侧水平的提示距离。 如果留空,则输入框水平居中。
YPos - 可选参数。 Y轴的位置表示垂直于屏幕左侧的提示距离。 如果留空,则输入框垂直居中。
Helpfile - 可选参数。 一个String表达式,标识用于为对话框提供上下文相关帮助的帮助文件。
context - 可选参数。 一个数字表达式,用于标识“帮助”作者分配给相应“帮助”主题的“帮助”上下文编号。 如果提供了上下文,则还必须提供helpfile。
例子 (Example)
让我们通过在两个输入框(一个用于长度,一个用于宽度)的帮助下在运行时从用户获取值来计算矩形的面积。
Function findArea()
Dim Length As Double
Dim Width As Double
Length = InputBox("Enter Length ", "Enter a Number")
Width = InputBox("Enter Width", "Enter a Number")
findArea = Length * Width
End Function
输出 (Output)
Step 1 - 要执行相同操作,请使用功能名称进行呼叫,然后按Enter键,如以下屏幕截图所示。
Step 2 - 执行时,显示第一个输入框(长度)。 在输入框中输入一个值。
Step 3 - 输入第一个值后,显示第二个输入框(宽度)。
Step 4 - 输入第二个号码后,单击“确定”按钮。 该区域显示如下面的屏幕截图所示。
VBA - Variables
Variable是一个命名的内存位置,用于保存可在脚本执行期间更改的值。 以下是命名变量的基本规则。
您必须使用一个字母作为第一个字符。
您不能在名称中使用空格,句点(。),感叹号(!)或字符@,&,$,#。
名称长度不能超过255个字符。
您不能使用Visual Basic保留关键字作为变量名称。
Syntax
在VBA中,您需要在使用它们之前声明变量。
Dim <<variable_name>> As <<variable_type>>
数据类型 (Data Types)
有许多VBA数据类型,可以分为两大类,即数字和非数字数据类型。
数字数据类型
下表显示数值数据类型和允许的值范围。
类型 | 价值范围 |
---|---|
Byte | 0到255 |
Integer | -32,768 to 32,767 |
Long | -2,147,483,648 to 2,147,483,648 |
Single | 负值为-3.402823E + 38至-1.401298E-45 1.401298E-45至3.402823E + 38为正值。 |
Double | -1.79769313486232e + 308至-4.94065645841247E-324为负值 4.94065645841247E-324至1.79769313486232e + 308为正值。 |
Currency | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
Decimal | 如果不使用小数,则+/- 79,228,162,514,264,337,593,543,950,335 +/- 7.9228162514264337593543950335(小数点后28位)。 |
非数字数据类型
下表显示非数字数据类型和允许的值范围。
类型 | 价值范围 |
---|---|
字符串(固定长度) | 1 to 65,400 characters |
字符串(可变长度) | 0到20亿个字符 |
Date | 1月1日,100日至9999年12月31日 |
Boolean | True or False |
Object | Any embedded object |
Variant (numeric) | 任何大小为double的值 |
Variant (text) | Same as variable-length string |
Example
让我们创建一个按钮并将其命名为“Variables_demo”以演示变量的使用。
Private Sub say_helloworld_Click()
Dim password As String
password = "Admin#1"
Dim num As Integer
num = 1234
Dim BirthDay As Date
BirthDay = DateValue("30/10/2020")
MsgBox "Passowrd is " & password & Chr(10) & "Value of num is " &
num & Chr(10) & "Value of Birthday is " & BirthDay
End Sub
Output
执行脚本后,输出将如以下屏幕截图所示。
VBA - Constants
Constant是一个命名的内存位置,用于保存在脚本执行期间无法更改的值。 如果用户尝试更改常量值,则脚本执行最终会出错。 声明常量的方式与声明变量的方式相同。
以下是命名常量的规则。
您必须使用一个字母作为第一个字符。
您不能在名称中使用空格,句点(。),感叹号(!)或字符@,&,$,#。
名称长度不能超过255个字符。
您不能使用Visual Basic保留关键字作为变量名称。
语法 (Syntax)
在VBA中,我们需要为声明的Constants赋值。 如果我们尝试更改常量的值,则会引发错误。
Const <<constant_name>> As <<constant_type>> = <<constant_value>>
例子 (Example)
让我们创建一个按钮“Constant_demo”来演示如何使用常量。
Private Sub Constant_demo_Click()
Const MyInteger As Integer = 42
Const myDate As Date = #2/2/2020#
Const myDay As String = "Sunday"
MsgBox "Integer is " & MyInteger & Chr(10) & "myDate is "
& myDate & Chr(10) & "myDay is " & myDay
End Sub
输出 (Output)
执行脚本后,将显示输出,如以下屏幕截图所示。
VBA - Operators
可以使用简单表达式定义Operator - 4 + 5等于9.这里,4和5称为operands ,+称为operator 。 VBA支持以下类型的运算符 -
- 算术运算符
- 比较运算符
- 逻辑(或关系)运算符
- 连接运算符
算术运算符
VBA支持以下算术运算符。
假设变量A保持5,变量B保持10,则 -
操作者 | 描述 | 例 |
---|---|---|
+ | 添加两个操作数 | A + B将给出15 |
- | 从第一个中减去第二个操作数 | A - B将给-5 |
* | 将两个操作数相乘 | A * B将给出50 |
/ | 用分母除以分子 | B/A会给2 |
% | 模数运算符和整数除法后的余数 | B%A将给出0 |
^ | 指数运算符 | B ^ A将给出100000 |
比较运算符
VBA支持以下比较运算符。
假设变量A保持10,变量B保持20,则 -
操作者 | 描述 | 例 |
---|---|---|
= | 检查两个操作数的值是否相等。 如果是,则条件为真。 | (A = B)是假的。 |
<> | 检查两个操作数的值是否相等。 如果值不相等,则条件为真。 | (A <> B)是真的。 |
> | 检查左操作数的值是否大于右操作数的值。 如果是,则条件为真。 | (A> B)是假的。 |
< | 检查左操作数的值是否小于右操作数的值。 如果是,则条件为真。 | (A < B) 为真 |
>= | 检查左操作数的值是否大于或等于右操作数的值。 如果是,则条件为真。 | (A> = B)为假。 |
<= | 检查左操作数的值是否小于或等于右操作数的值。 如果是,则条件为真。 | (A <= B)为真。 |
逻辑运算符 (The Logical Operators)
VBA支持以下逻辑运算符。
假设变量A保持10,变量B保持0,则 -
操作者 | 描述 | 例 |
---|---|---|
AND | 称为逻辑AND运算符。 如果两个条件都为True,则表达式为真。 | a <> 0 AND b <> 0为False。 |
OR | 称为逻辑OR运算符。 如果两个条件中的任何一个为True,则条件为真。 | a <> 0 OR b <> 0为真。 |
NOT | 称为逻辑非运算符。 用于反转其操作数的逻辑状态。 如果条件为真,则Logical NOT运算符将为false。 | NOT(a <> 0 OR b <> 0)为假。 |
XOR | 称为逻辑排除。 它是NOT和OR运算符的组合。 如果一个且只有一个表达式的计算结果为True,则结果为True。 | (a <> 0 XOR b <> 0)为真。 |
连接运算符
连接后,VBA支持连接运算符。
假设变量A保持5,变量B保持10然后 -
操作者 | 描述 | 例 |
---|---|---|
+ | 添加两个值作为变量。 值为数字 | A + B将给出15 |
& | Concatenates two Values | A&B将给510 |
假设变量A =“Microsoft”和变量B =“VBScript”,则 -
操作者 | 描述 | 例 |
---|---|---|
+ | Concatenates two Values | A + B将提供MicrosoftVBScript |
& | Concatenates two Values | A&B将提供MicrosoftVBScript |
Note - 连接运算符可用于数字和字符串。 如果变量包含数值或字符串值,则输出取决于上下文。
VBA - Decisions
决策允许程序员控制脚本或其中一个部分的执行流程。 执行由一个或多个条件语句控制。
以下是大多数编程语言中的典型决策结构的一般形式。
VBA提供以下类型的决策制定声明。 单击以下链接以查看其详细信息。
S.No. | 声明和说明 |
---|---|
1 | if 语句 if语句由一个布尔表达式后跟一个或多个语句组成。 |
2 | if..else statement if else语句由一个布尔表达式后跟一个或多个语句组成。 如果条件为True,则执行If语句下的语句。 如果条件为false,则执行脚本的Else部分。 |
3 | if...elseif..else statement 一个if语句后跟一个或多个ElseIf语句,它由布尔表达式组成,然后是一个可选的else statement ,当所有条件都变为false时执行。 |
4 | 嵌套if语句 另一个if或elseif语句中的if或elseif语句。 |
5 | switch 语句 switch语句允许测试变量与值列表的相等性。 |
VBA - Loops
可能存在需要多次执行代码块的情况。 通常,语句按顺序执行:首先执行函数中的第一个语句,然后执行第二个语句,依此类推。
编程语言提供各种控制结构,允许更复杂的执行路径。
循环语句允许我们多次执行语句或语句组。 以下是VBA中循环语句的一般形式。
VBA提供以下类型的循环来处理循环要求。 单击以下链接以检查其详细信息。
S.No. | 循环类型和描述 |
---|---|
1 | for循环 多次执行一系列语句,并缩写管理循环变量的代码。 |
2 | for ..each循环 如果组中至少有一个元素并且对组中的每个元素重复,则执行此操作。 |
3 | while..wend循环 这在执行循环体之前测试条件。 |
4 | do..while循环 只要条件为True,就会执行do..While语句。(即)循环应该重复,直到条件为False。 |
5 | do..until循环 只要条件为False,就会执行do..Until语句。(即)循环应该重复,直到条件为True。 |
循环控制语句 (Loop Control Statements)
循环控制语句将执行从其正常序列更改。 当执行离开作用域时,循环中的所有剩余语句都不会被执行。
VBA支持以下控制语句。 单击以下链接以检查其详细信息。
S.No. | 控制声明和描述 |
---|---|
1 | Exit For statement 终止For loop语句并将执行转移到For loop语句 |
2 | Exit Do statement 终止Do While语句并将执行转移到循环后的语句 |
VBA - Strings
字符串是一系列字符,可以包含字母,数字,特殊字符或所有字符。 如果变量用双引号“”括起来,则称该变量为字符串。
语法 (Syntax)
variablename = "string"
例子 (Examples)
str1 = "string" ' Only Alphabets
str2 = "132.45" ' Only Numbers
str3 = "!@#$;*" ' Only Special Characters
Str4 = "Asc23@#" ' Has all the above
字符串函数 (String Functions)
有预定义的VBA字符串函数,可以帮助开发人员非常有效地处理字符串。 以下是VBA支持的String方法。 请点击每个方法详细了解。
S.No. | 功能名称和描述 |
---|---|
1 | InStr 返回指定子字符串的第一个匹配项。 搜索从左到右进行。 |
2 | InstrRev 返回指定子字符串的第一个匹配项。 搜索从右到左进行。 |
3 | Lcase 返回指定字符串的小写。 |
4 | Ucase 返回指定字符串的大写。 |
5 | Left 从字符串的左侧返回特定数量的字符。 |
6 | Right 从字符串的右侧返回特定数量的字符。 |
7 | Mid 根据指定的参数从字符串中返回特定数量的字符。 |
8 | Ltrim 删除指定字符串左侧的空格后返回一个字符串。 |
9 | Rtrim 删除指定字符串右侧的空格后返回一个字符串。 |
10 | Trim 删除前导空格和尾随空格后,返回字符串值。 |
11 | Len 返回给定字符串的长度。 |
12 | Replace 用另一个字符串替换字符串后返回一个字符串。 |
13 | Space 填充具有指定空格数的字符串。 |
14 | StrComp 比较两个指定的字符串后返回一个整数值。 |
15 | String 返回具有指定字符的字符串指定的次数。 |
16 | StrReverse 在反转给定字符串的字符序列后返回一个字符串。 |
VBA - Date-Time Function
VBScript日期和时间函数可帮助开发人员将日期和时间从一种格式转换为另一种格式,或以适合特定条件的格式表示日期或时间值。
Date 函数
S.No. | 功能说明 |
---|---|
1 | Date 一个Function,返回当前系统日期。 |
2 | CDate 一个函数,它将给定输入转换为日期。 |
3 | DateAdd 一个Function,它返回添加了指定时间间隔的日期。 |
4 | DateDiff 一个Function,它返回两个时间段之间的差异。 |
5 | DatePart 一个Function,它返回给定输入日期值的指定部分。 |
6 | DateSerial 一个Function,它返回给定年份,月份和日期的有效日期。 |
7 | FormatDateTime 一个Function,它根据提供的参数格式化日期。 |
8 | IsDate 一个Function,它返回一个布尔值,无论所提供的参数是否为日期。 |
9 | Day 一个Function,返回1到31之间的整数,表示指定日期的日期。 |
10 | Month 一个Function,返回1到12之间的整数,表示指定日期的月份。 |
11 | Year 一个Function,它返回一个表示指定日期年份的整数。 |
12 | MonthName 一个Function,返回指定日期的特定月份的名称。 |
13 | WeekDay 一个Function,返回一个整数(1到7),表示指定日期的星期几。 |
14 | WeekDayName 一个Function,返回指定日期的工作日名称。 |
Time 函数
S.No. | 功能说明 |
---|---|
1 | Now 一个Function,它返回当前系统的日期和时间。 |
2 | Hour 一个Function,返回0到23之间的整数,表示给定时间的小时部分。 |
3 | Minute 一个Function,返回0到59之间的整数,表示给定时间的分钟部分。 |
4 | Second 一个Function,返回0到59之间的整数,表示给定时间的秒部分。 |
5 | Time 一个Function,返回当前系统时间。 |
6 | Timer 一个Function,返回自12:00 AM以来的秒数和毫秒数。 |
7 | TimeSerial 一个Function,返回特定输入小时,分钟和秒的时间。 |
8 | TimeValue 一个Function,它将输入字符串转换为时间格式。 |
VBA - Arrays
我们非常清楚变量是存储值的容器。 有时,开发人员可以一次在一个变量中保存多个值。 当一系列值存储在单个变量中时,它就被称为array variable 。
阵列声明
除了数组变量的声明使用括号之外,数组的声明方式与声明变量的方式相同。 在以下示例中,括号中提到了数组的大小。
'Method 1 : Using Dim
Dim arr1() 'Without Size
'Method 2 : Mentioning the Size
Dim arr2(5) 'Declared with size of 5
'Method 3 : using 'Array' Parameter
Dim arr3
arr3 = Array("apple","Orange","Grapes")
虽然,数组大小表示为5,但它可以保持6个值,因为数组索引从ZERO开始。
数组索引不能为负数。
VBScript Arrays可以在数组中存储任何类型的变量。 因此,数组可以在单个数组变量中存储整数,字符串或字符。
将值分配给数组
通过针对要分配的每个值指定数组索引值,将值分配给数组。 它可以是一个字符串。
例子 (Example)
添加按钮并添加以下功能。
Private Sub Constant_demo_Click()
Dim arr(5)
arr(0) = "1" 'Number as String
arr(1) = "VBScript" 'String
arr(2) = 100 'Number
arr(3) = 2.45 'Decimal Number
arr(4) = #10/07/2013# 'Date
arr(5) = #12.45 PM# 'Time
msgbox("Value stored in Array index 0 : " & arr(0))
msgbox("Value stored in Array index 1 : " & arr(1))
msgbox("Value stored in Array index 2 : " & arr(2))
msgbox("Value stored in Array index 3 : " & arr(3))
msgbox("Value stored in Array index 4 : " & arr(4))
msgbox("Value stored in Array index 5 : " & arr(5))
End Sub
执行上述功能时,会产生以下输出。
Value stored in Array index 0 : 1
Value stored in Array index 1 : VBScript
Value stored in Array index 2 : 100
Value stored in Array index 3 : 2.45
Value stored in Array index 4 : 7/10/2013
Value stored in Array index 5 : 12:45:00 PM
多维数组
数组不仅限于单个维度,而且最多可以包含60个维度。 二维阵列是最常用的阵列。
例子 (Example)
在以下示例中,声明了一个包含3行和4列的多维数组。
Private Sub Constant_demo_Click()
Dim arr(2,3) as Variant ' Which has 3 rows and 4 columns
arr(0,0) = "Apple"
arr(0,1) = "Orange"
arr(0,2) = "Grapes"
arr(0,3) = "pineapple"
arr(1,0) = "cucumber"
arr(1,1) = "beans"
arr(1,2) = "carrot"
arr(1,3) = "tomato"
arr(2,0) = "potato"
arr(2,1) = "sandwitch"
arr(2,2) = "coffee"
arr(2,3) = "nuts"
msgbox("Value in Array index 0,1 : " & arr(0,1))
msgbox("Value in Array index 2,2 : " & arr(2,2))
End Sub
执行上述功能时,会产生以下输出。
Value stored in Array index : 0 , 1 : Orange
Value stored in Array index : 2 , 2 : coffee
ReDim 语句
ReDim语句用于声明动态数组变量并分配或重新分配存储空间。
语法 (Syntax)
ReDim [Preserve] varname(subscripts) [, varname(subscripts)]
参数描述 (Parameter Description)
Preserve - 一个可选参数,用于在更改最后一个维的大小时保留现有数组中的数据。
Varname - 必需参数,表示变量的名称,应遵循标准变量命名约定。
Subscripts - 必需参数,表示数组的大小。
例子 (Example)
在以下示例中,已重新定义数组,然后在更改现有数组大小时保留值。
Note - 在调整小于原始数组的大小时,消除的元素中的数据将丢失。
Private Sub Constant_demo_Click()
Dim a() as variant
i = 0
redim a(5)
a(0) = "XYZ"
a(1) = 41.25
a(2) = 22
REDIM PRESERVE a(7)
For i = 3 to 7
a(i) = i
Next
'to Fetch the output
For i = 0 to ubound(a)
Msgbox a(i)
Next
End Sub
执行上述功能时,会产生以下输出。
XYZ
41.25
22
3
4
5
6
7
数组方法
VBScript中有各种内置函数,可以帮助开发人员有效地处理数组。 下面列出了与数组一起使用的所有方法。 请单击方法名称以了解详细信息。
S.No. | 功能说明 |
---|---|
1 | LBound 一个Function,返回一个对应于给定数组的最小下标的整数。 |
2 | UBound 一个Function,它返回一个对应于给定数组的最大下标的整数。 |
3 | Split 一个Function,它返回一个包含指定数量值的数组。 基于分隔符拆分。 |
4 | Join 一个Function,它返回一个字符串,该字符串包含数组中指定数量的子字符串。 这与Split Method完全相反。 |
5 | Filter 一个Function,它返回一个基于零的数组,该数组包含基于特定过滤条件的字符串数组的子集。 |
6 | IsArray 一个Function,返回一个布尔值,指示输入变量是否为数组。 |
7 | Erase 一个Function,它恢复数组变量的已分配内存。 |
VBA - User Defined Functions
function是一组可重用的代码,可以在程序中的任何位置调用。 这消除了一遍又一遍地编写相同代码的需要。 这使程序员能够将大型程序划分为许多小型且易于管理的功能。
除了内置函数,VBA还允许编写用户定义的函数。 在本章中,您将学习如何在VBA中编写自己的函数。
函数定义 (Function Definition)
VBA函数可以有一个可选的return语句。 如果要从函数返回值,则需要这样做。
例如,您可以在函数中传递两个数字,然后您可以期望函数在您的调用程序中返回它们的乘法。
Note - 函数可以返回由逗号分隔的多个值,作为分配给函数名称本身的数组。
在我们使用函数之前,我们需要定义该特定函数。 在VBA中定义函数的最常用方法是使用Function关键字,后跟唯一的函数名称,它可能带有也可能不带有参数列表和带有End Function关键字的语句,后者表示函数的结束。 以下是基本语法。
语法 (Syntax)
添加按钮并添加以下功能。
Function Functionname(parameter-list)
statement 1
statement 2
statement 3
.......
statement n
End Function
例子 (Example)
添加以下返回区域的函数。 请注意,可以使用函数名称本身返回值/值。
Function findArea(Length As Double, Optional Width As Variant)
If IsMissing(Width) Then
findArea = Length * Length
Else
findArea = Length * Width
End If
End Function
调用一个函数 (Calling a Function)
要调用函数,请使用函数名称调用函数,如以下屏幕截图所示。
将显示如下所示区域的输出给用户。
VBA - Sub Procedure
Sub Procedures与功能类似,但存在一些差异。
子程序DO函数可能返回值也可能不返回值时返回值。
可以在没有call关键字的情况下调用子过程。
子过程始终包含在Sub和End Sub语句中。
例子 (Example)
Sub Area(x As Double, y As Double)
MsgBox x * y
End Sub
呼叫程序
要在脚本中的某处调用过程,可以从函数进行调用。 我们将无法使用与函数相同的方式,因为子过程将不返回值。
Function findArea(Length As Double, Width As Variant)
area Length, Width ' To Calculate Area 'area' sub proc is called
End Function
现在,您将只能调用该函数,但不能调用子过程,如以下屏幕截图所示。
该区域仅在“消息”框中计算和显示。
结果单元格显示ZERO,因为该函数未返回区域值。 简而言之,您无法直接调用excel工作表中的子过程。
VBA - Events
当您手动更改单元格或单元格值范围时,可以触发事件驱动编程VBA。 更改事件可能会使事情变得更容易,但您可以非常快速地结束充满格式的页面。 有两种事件。
- 工作表事件
- 工作簿活动
工作表事件
当工作表中发生更改时,将触发工作表事件。 它是通过右键单击工作表选项卡并选择“查看代码”,然后粘贴代码来创建的。
用户可以选择这些工作表中的每一个,并从下拉列表中选择“工作表”以获取所有支持的工作表事件的列表。
以下是可由用户添加的受支持的工作表事件。
Private Sub Worksheet_Activate()
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Private Sub Worksheet_Calculate()
Private Sub Worksheet_Change(ByVal Target As Range)
Private Sub Worksheet_Deactivate()
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
例子 (Example)
我们说,我们只需要在双击之前显示一条消息。
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
MsgBox "Before Double Click"
End Sub
输出 (Output)
双击任何单元格后,将向用户显示消息框,如以下屏幕截图所示。
工作簿活动
当工作簿整体发生更改时,将触发工作簿事件。 我们可以通过选择“ThisWorkbook”并从下拉列表中选择“工作簿”来添加工作簿事件的代码,如以下屏幕截图所示。 立即向用户显示Workbook_open子过程,如以下屏幕截图所示。
以下是可由用户添加的受支持的工作簿事件。
Private Sub Workbook_AddinUninstall()
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Private Sub Workbook_Deactivate()
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Private Sub Workbook_Open()
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Private Sub Workbook_SheetBeforeDoubleClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean)
Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Private Sub Workbook_SheetFollowHyperlink(ByVal Sh As Object, ByVal Target As Hyperlink)
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
Private Sub Workbook_WindowActivate(ByVal Wn As Window)
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
Private Sub Workbook_WindowResize(ByVal Wn As Window)
例子 (Example)
让我们说,我们只需要向用户显示一条消息,无论何时创建新工作表,都会成功创建新工作表。
Private Sub Workbook_NewSheet(ByVal Sh As Object)
MsgBox "New Sheet Created Successfully"
End Sub
输出 (Output)
创建新的Excel工作表后,将向用户显示一条消息,如以下屏幕截图所示。
VBA - Error Handling
编程中有三种类型的错误:(a)语法错误,(b)运行时错误,以及(c)逻辑错误。
语法错误 (Syntax errors)
语法错误(也称为解析错误)在VBScript的解释时发生。 例如,以下行导致语法错误,因为它缺少右括号。
Function ErrorHanlding_Demo()
dim x,y
x = "IoWiki"
y = Ucase(x
End Function
运行时错误 (Runtime errors)
在解释之后,执行期间会发生运行时错误,也称为异常。
例如,以下行导致运行时错误,因为此处语法正确但在运行时它尝试调用fnmultiply,这是一个不存在的函数。
Function ErrorHanlding_Demo1()
Dim x,y
x = 10
y = 20
z = fnadd(x,y)
a = fnmultiply(x,y)
End Function
Function fnadd(x,y)
fnadd = x + y
End Function
逻辑错误
逻辑错误可能是最难追踪的错误类型。 这些错误不是语法或运行时错误的结果。 相反,当你在驱动你的脚本的逻辑中犯了错误并且你没有得到你期望的结果时,它们会发生。
您无法捕获这些错误,因为它取决于您的业务需求,您希望在程序中放置哪种类型的逻辑。
例如,将数字除以零或写入进入无限循环的脚本。
错误的对象
假设我们有一个运行时错误,那么执行会通过显示错误消息而停止。 作为开发人员,如果我们想捕获错误,则使用Error Object。
例子 (Example)
在以下示例中, Err.Number给出错误编号, Err.Description给出错误描述。
Err.Raise 6 ' Raise an overflow error.
MsgBox "Error # " & CStr(Err.Number) & " " & Err.Description
Err.Clear ' Clear the error.
错误处理
VBA启用错误处理例程,也可用于禁用错误处理例程。 如果没有On Error语句,则发生的任何运行时错误都是致命的:显示错误消息,并且执行突然停止。
On Error { GoTo [ line | 0 | -1 ] | Resume Next }
S.No. | 关键字和说明 |
---|---|
1 | GoTo line 启用从所需行参数中指定的行开始的错误处理例程。 指定的行必须与On Error语句位于相同的过程中,否则将发生编译时错误。 |
2 | GoTo 0 禁用当前过程中启用的错误处理程序并将其重置为Nothing。 |
3 | GoTo -1 禁用当前过程中启用的异常并将其重置为Nothing。 |
4 | Resume Next 指定当发生运行时错误时,控件将立即转到发生错误的语句之后的语句,并从该点继续执行。 |
例子 (Example)
Public Sub OnErrorDemo()
On Error GoTo ErrorHandler ' Enable error-handling routine.
Dim x, y, z As Integer
x = 50
y = 0
z = x/y ' Divide by ZERO Error Raises
ErrorHandler: ' Error-handling routine.
Select Case Err.Number ' Evaluate error number.
Case 10 ' Divide by zero error
MsgBox ("You attempted to divide by zero!")
Case Else
MsgBox "UNKNOWN ERROR - Error# " & Err.Number & " : " & Err.Description
End Select
Resume Next
End Sub
VBA - Excel Objects
使用VBA编程时,用户将要处理的重要对象很少。
- 应用对象
- 工作簿对象
- 工作表对象
- Range Objects
应用对象
Application对象包含以下内容 -
- 应用程序范围的设置和选项。
- 返回顶级对象的方法,如ActiveCell,ActiveSheet等。
例子 (Example)
'Example 1 :
Set xlapp = CreateObject("Excel.Sheet")
xlapp.Application.Workbooks.Open "C:\test.xls"
'Example 2 :
Application.Windows("test.xls").Activate
'Example 3:
Application.ActiveCell.Font.Bold = True
工作簿对象
Workbook对象是Workbooks集合的成员,包含当前在Microsoft Excel中打开的所有Workbook对象。
例子 (Example)
'Ex 1 : To close Workbooks
Workbooks.Close
'Ex 2 : To Add an Empty Work Book
Workbooks.Add
'Ex 3: To Open a Workbook
Workbooks.Open FileName:="Test.xls", ReadOnly:=True
'Ex : 4 - To Activate WorkBooks
Workbooks("Test.xls").Worksheets("Sheet1").Activate
工作表对象
Worksheet对象是Worksheets集合的成员,包含工作簿中的所有Worksheet对象。
例子 (Example)
'Ex 1 : To make it Invisible
Worksheets(1).Visible = False
'Ex 2 : To protect an WorkSheet
Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True
范围对象
范围对象表示包含一个或多个连续单元块的单元格,行,列或选定的单元格。
'Ex 1 : To Put a value in the cell A5
Worksheets("Sheet1").Range("A5").Value = "5235"
'Ex 2 : To put a value in range of Cells
Worksheets("Sheet1").Range("A1:A4").Value = 5
VBA - Text Files
您还可以阅读Excel文件,并使用VBA将单元格的内容写入文本文件。 VBA允许用户使用两种方法处理文本文件 -
- 文件系统对象
- 使用Write命令
File System Object (FSO)
顾名思义,FSO帮助开发人员处理驱动器,文件夹和文件。 在本节中,我们将讨论如何使用FSO。
S.No. | 对象类型和描述 |
---|---|
1 | Drive Drive是一个对象。 包含允许您收集有关连接到系统的驱动器的信息的方法和属性。 |
2 | Drives Drives是一个集合。 它提供了物理或逻辑上附加到系统的驱动器列表。 |
3 | File 文件是一个对象。 它包含允许开发人员创建,删除或移动文件的方法和属性。 |
4 | Files 文件是一个集合。 它提供了文件夹中包含的所有文件的列表。 |
5 | Folder 文件夹是一个对象。 它提供了允许开发人员创建,删除或移动文件夹的方法和属性。 |
6 | Folders 文件夹是一个集合。 它提供了文件夹中所有文件夹的列表。 |
7 | TextStream TextStream是一个Object。 它使开发人员能够读写文本文件。 |
Drive
Drive是一个对象,可以访问特定磁盘驱动器或网络共享的属性。 Drive对象支持以下属性 -
- AvailableSpace
- DriveLetter
- DriveType
- FileSystem
- FreeSpace
- IsReady
- Path
- RootFolder
- SerialNumber
- ShareName
- TotalSize
- VolumeName
例子 (Example)
Step 1 - 在使用FSO继续编写脚本之前,我们应该启用Microsoft Scripting Runtime。 要执行相同操作,请导航到工具→参考,如以下屏幕截图所示。
Step 2 - 添加“Microsoft Scripting RunTime”并单击“确定”。
Step 3 - 添加要在文本文件中写入的数据并添加命令按钮。
Step 4 - 现在是脚本的时候了。
Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
Dim fso As FileSystemObject
Set fso = New FileSystemObject
Dim stream As TextStream
LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count
' Create a TextStream.
Set stream = fso.OpenTextFile("D:\Try\Support.log", ForWriting, True)
CellData = ""
For i = 1 To LastRow
For j = 1 To LastCol
CellData = Trim(ActiveCell(i, j).Value)
stream.WriteLine "The Value at location (" & i & "," & j & ")" & CellData
Next j
Next i
stream.Close
MsgBox ("Job Done")
End Sub
输出 (Output)
执行脚本时,请确保将光标放在工作表的第一个单元格中。 创建Support.log文件,如以下屏幕截图“D:\Try”下所示。
该文件的内容显示在以下屏幕截图中。
Write Command
与FSO不同,我们不需要添加任何引用,但是,我们将无法使用驱动器,文件和文件夹。 我们将只能将流添加到文本文件中。
例子 (Example)
Private Sub fn_write_to_text_Click()
Dim FilePath As String
Dim CellData As String
Dim LastCol As Long
Dim LastRow As Long
LastCol = ActiveSheet.UsedRange.Columns.Count
LastRow = ActiveSheet.UsedRange.Rows.Count
FilePath = "D:\Try\write.txt"
Open FilePath For Output As #2
CellData = ""
For i = 1 To LastRow
For j = 1 To LastCol
CellData = "The Value at location (" & i & "," & j & ")" & Trim(ActiveCell(i, j).Value)
Write #2, CellData
Next j
Next i
Close #2
MsgBox ("Job Done")
End Sub
输出 (Output)
执行脚本后,将在“D:\Try”位置创建“write.txt”文件,如以下屏幕截图所示。
该文件的内容显示在以下屏幕截图中。
VBA - Programming Charts
使用VBA,您可以根据特定条件生成图表。 我们来看一个例子吧。
Step 1 - 输入必须生成图表的数据。
Step 2 - 创建3个按钮 - 一个用于生成条形图,另一个用于生成饼图,另一个用于生成柱形图。
Step 3 - 开发宏来生成这些类型的图表中的每一个。
' Procedure to Generate Pie Chart
Private Sub fn_generate_pie_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlPie
Next cht
End Sub
' Procedure to Generate Bar Graph
Private Sub fn_Generate_Bar_Graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlBar
Next cht
End Sub
' Procedure to Generate Column Graph
Private Sub fn_generate_column_graph_Click()
Dim cht As ChartObject
For Each cht In Worksheets(1).ChartObjects
cht.Chart.Type = xlColumn
Next cht
End Sub
Step 4 - 单击相应按钮后,将创建图表。 在以下输出中,单击“生成饼图”按钮。
VBA - User Forms
User Form是一个自定义的对话框,使用户数据条目更易于控制,更易于用户使用。 在本章中,您将学习如何设计一个简单的表单并将数据添加到excel中。
Step 1 - 按Alt + F11导航到VBA窗口,导航到“插入”菜单,然后选择“用户表单”。 选择后,将显示用户表单,如以下屏幕截图所示。
Step 2 - 使用给定的控件设计表单。
Step 3 - 添加每个控件后,必须命名控件。 标题对应于表单上显示的内容,名称对应于为该元素编写VBA代码时将出现的逻辑名称。
Step 4 - 以下是针对每个添加的控件的名称。
控制 | 逻辑名称 | 字幕 |
---|---|---|
From | frmempform | 员工表格 |
Employee ID Label Box | empid | 员工ID |
firstname Label Box | firstname | First Name |
lastname Label Box | lastname | Last Name |
dob Label Box | dob | 出生日期 |
mailid Label Box | mailid | 电子邮件ID |
Passportholder Label Box | Passportholder | Passport Holder |
Emp ID文本框 | txtempid | 不适用 |
First Name Text Box | txtfirstname | 不适用 |
Last Name Text Box | txtlastname | 不适用 |
电子邮件ID文本框 | txtemailid | 不适用 |
日期组合框 | cmbdate | 不适用 |
月组合框 | cmbmonth | 不适用 |
年组合框 | cmbyear | 不适用 |
Yes Radio Button | radioyes | Yes |
No Radio Button | radiono | No |
提交按钮 | btnsubmit | Submit |
取消按钮 | btncancel | Cancel |
Step 5 - 通过右键单击表单并选择“查看代码”,为表单加载事件添加代码。
Step 6 - 从对象下拉列表中选择“Userform”,然后选择“Initialize”方法,如以下屏幕截图所示。
Step 7 - 加载表单后,确保清除文本框,填写下拉框并重置单选按钮。
Private Sub UserForm_Initialize()
'Empty Emp ID Text box and Set the Cursor
txtempid.Value = ""
txtempid.SetFocus
'Empty all other text box fields
txtfirstname.Value = ""
txtlastname.Value = ""
txtemailid.Value = ""
'Clear All Date of Birth Related Fields
cmbdate.Clear
cmbmonth.Clear
cmbyear.Clear
'Fill Date Drop Down box - Takes 1 to 31
With cmbdate
.AddItem "1"
.AddItem "2"
.AddItem "3"
.AddItem "4"
.AddItem "5"
.AddItem "6"
.AddItem "7"
.AddItem "8"
.AddItem "9"
.AddItem "10"
.AddItem "11"
.AddItem "12"
.AddItem "13"
.AddItem "14"
.AddItem "15"
.AddItem "16"
.AddItem "17"
.AddItem "18"
.AddItem "19"
.AddItem "20"
.AddItem "21"
.AddItem "22"
.AddItem "23"
.AddItem "24"
.AddItem "25"
.AddItem "26"
.AddItem "27"
.AddItem "28"
.AddItem "29"
.AddItem "30"
.AddItem "31"
End With
'Fill Month Drop Down box - Takes Jan to Dec
With cmbmonth
.AddItem "JAN"
.AddItem "FEB"
.AddItem "MAR"
.AddItem "APR"
.AddItem "MAY"
.AddItem "JUN"
.AddItem "JUL"
.AddItem "AUG"
.AddItem "SEP"
.AddItem "OCT"
.AddItem "NOV"
.AddItem "DEC"
End With
'Fill Year Drop Down box - Takes 1980 to 2014
With cmbyear
.AddItem "1980"
.AddItem "1981"
.AddItem "1982"
.AddItem "1983"
.AddItem "1984"
.AddItem "1985"
.AddItem "1986"
.AddItem "1987"
.AddItem "1988"
.AddItem "1989"
.AddItem "1990"
.AddItem "1991"
.AddItem "1992"
.AddItem "1993"
.AddItem "1994"
.AddItem "1995"
.AddItem "1996"
.AddItem "1997"
.AddItem "1998"
.AddItem "1999"
.AddItem "2000"
.AddItem "2001"
.AddItem "2002"
.AddItem "2003"
.AddItem "2004"
.AddItem "2005"
.AddItem "2006"
.AddItem "2007"
.AddItem "2008"
.AddItem "2009"
.AddItem "2010"
.AddItem "2011"
.AddItem "2012"
.AddItem "2013"
.AddItem "2014"
End With
'Reset Radio Button. Set it to False when form loads.
radioyes.Value = False
radiono.Value = False
End Sub
Step 8 - 现在将代码添加到“提交”按钮。 单击提交按钮后,用户应该能够将值添加到工作表中。
Private Sub btnsubmit_Click()
Dim emptyRow As Long
'Make Sheet1 active
Sheet1.Activate
'Determine emptyRow
emptyRow = WorksheetFunction.CountA(Range("A:A")) + 1
'Transfer information
Cells(emptyRow, 1).Value = txtempid.Value
Cells(emptyRow, 2).Value = txtfirstname.Value
Cells(emptyRow, 3).Value = txtlastname.Value
Cells(emptyRow, 4).Value = cmbdate.Value & "/" & cmbmonth.Value & "/" & cmbyear.Value
Cells(emptyRow, 5).Value = txtemailid.Value
If radioyes.Value = True Then
Cells(emptyRow, 6).Value = "Yes"
Else
Cells(emptyRow, 6).Value = "No"
End If
End Sub
Step 9 - 添加方法以在用户单击“取消”按钮时关闭表单。
Private Sub btncancel_Click()
Unload Me
End Sub
Step 10 - 单击“运行”按钮执行表单。 在表单中输入值,然后单击“提交”按钮。 这些值将自动流入工作表,如以下屏幕截图所示。