Excel VBA编程

如何创建VBA

1. 进入开发工具窗口

2.选择插入模块，然后插入过程，选择子程序

VBA语法规则

在Excel中，数据只有文本，数值，日期值，逻辑值和错误值五种类型。但是在VBA中，数据类型跟Excel不完全相同。根据数据的特点，VBA将数据分为布尔型（boolean），字节型(byte)，整数型(integer)，单精度浮点型(single)，双精度浮点型(double)，货币型(currency)，小数型(decimal)，字符串型（string），日期型（date）,对象型等等

声明变量

声明变量，其实就是指定该变量的名称及其可存储的数据类型，要在VBA中声明一个变量，有以下几种方法：

1. Dim 变量名 as 数据类型
2. Private 变量名 as 数据类型，用private声明变量，该变量将会变成私有变量
3. Public 变量名 as 数据类型，用public变量定义的变量是公有变量
4. static 变量名 as 数据类型，如果使用static声明变量，这个变量将会变成静态变量，当程序结束后，静态变量会保持其原值不变。

给变量赋值

1. 给数据类型的变量赋值，应该要使用这个语句：let 变量名称 = 要存储的数据
2. 给对象类型的变量赋值，应该使用下面的语句：“set 变量名称 = 要存储的对象名称”

让变量存储的数据参与运算

1. 数据型变量参与运算

2. 对象型变量参与运算

关于声明变量的其他知识

1. 如果要声明多个变量可以将代码写成下面形式：

2. 可以使用变量类型声明符定义变量类型

数据类型	类型声明字符
Integer	%
Long	&
Single	！
Double	#
currency	@
string	$

3. 声明变量可以不指定变量类型：在VBA中声明变量是，如果不确定会将类型的数据存储在变量中，可以在声明变量时，只定义变量的名字，而不是变量的类型。如果声明变量时，只指定变量的名称而不指定变量的数据类型，VBA默认将该变量定义为Variant类型，如果一个变量被声明为variant类型，俺么它能够存储任何数据类型
4. 强制声明所有变量：如果担心自己忘记在程序中忘记声明变量，可以在模块开头，输入“Option Explicit”

变量的作用域

按作用域划分，VBA中的变量可以划分为本地变量，模块变量和公共变量。

作用域	描述
单个过程	在一个过程中使用dim或者static语句声明的变量，作用域为本过程，即只有声明变量的语句所在的过程能够使用它，这样的变量，称为本地变量
单个模块	在模块的第一个过程之前使用dim或者private语句声明的变量，作用域为声明变量的语句所在模块中的所有过程，即该模块中所有的过程都可以使用它，这样的边框称为模块级变量
所有模块	在一个模块的第一个过程之前使用public语句声明的变量，作用域为所有模块，即所有模块中的过程都可以使用它，这样的变量称为公共变量

特殊的变量——数组

1. 数组就是同种类型的多个变量的集合
2. 数组中的元素可以通过索引值取出
3. 声明数组时应该声明数组的大小
   “public dim 数组名称 （a to b）as 数据类型”
4. 给数组赋值就是给数组的每个元素分别赋值

声明多维数组

Dim arr(a,b) as Integer

这里是声明一个叫arr数组，其形状为a行b列

声明动态数组

当声明数组时，不知道要在这个数组中填入多少个数据时，可以使用动态数组。将数组声明为动态数组时，不能直接使用变量，需要之后使用redim 重新制定数组大小

Sub test()
    a = Application.WorksheetFunction.CountA(rang("A:A"))
    Dim arr() As String
    ReDim arr(1 To a)
End Sub

其他创建数组的方法

1. 使用array函数声明数组

Sub arraytest()
    Dim arr As Variant
    arr = Array(1, 2, 3, 4)
    MsgBox "arr数组的第2个元素为：" & arr(1)
   
End Sub

2. 使用是split函数声明数组

Sub arraytest()
    Dim arr As Variant
    arr = Split("叶枫，空空，小月，老祝", "，")
    MsgBox "arr数组的第2个元素为：" & arr(1)
   
End Sub

3. 通过单元格区域直接创建数组

Sub arraytest()
    Dim arr As Variant
    Range("A1:C3").Value = 5
    arr = Range("A1:C1").Value
    Range("e1:G1").Value = arr
   
End Sub

数组函数

利用UBound求数组的最大索引号

Sub arraytest()
    Dim arr As Variant
    Range("A1:C3").Value = 5
    arr = Range("A1:C1").Value
    MsgBox "数组的最大索引号是：" & UBound(arr)
   
End Sub

利用LBound函数求最小索引号

Sub arraytest()
    Dim arr As Variant
    Range("A1:C3").Value = 5
    arr = Range("A1:C1").Value
    MsgBox "数组的最小索引号是：" & LBound(arr)
   
End Sub

求多维数组的最大和最小索引号

Sub arraytest()
   Dim arr(10, 100) As Integer
   Dim a As Integer, b As Integer
   a = UBound(arr, 1)
   b = UBound(arr, 2)
   MsgBox "第一维的最大索引是：" & a & Chr(13) & "第二维的最大索引是：" & b
   
End Sub

用join函数将一维数组合并成字符串

Sub arraytest()
    Dim arr As Variant
    arr = Array(1, 2, 3, 4)
    Text = Join(arr)
    MsgBox Text
   
End Sub

将数组内容写入工作表中

Sub arraytest()
    Dim arr As Variant
    arr = Array(1, 2, 3, 4, 5, 6, 7)
    Range("A4:A11").Value = Application.WorksheetFunction.Transpose(arr)
    
End Sub

其中transpose是对数组进行列转换。数组内容如果不够填充单元格长度，超出的数据会以NA值填充。若需要填充的单元格数目不够数组长度，那么会按照顺序依次填充。

数组的存取

当将Excel表中的数据传递给数组时，默认建立的是一个二维数组，因此在取数组的值时，需要传递两个数值进去，如果只传入一个数组，会出现下标越界的警告。数组的第一个数指定行，第二个数指定列

arr = range("B1:C1").value
msgbox arr(1,2)

特殊数据的专用容器——常量

常量常常用来存储一些固定不变的数据，如利率，税率和圆周率等。**声明常量时，英同时定义常量的名称，可存储的数据类型以及存储在其中的数据。语句为：

const 常量名称 as 数据类型 = 存储在常量中的数据

同定义变量一样，在过程内部使用const语句定义的常量称为本地常量，只可以在声明常量的过程中使用；

如果在模块的第一个过程之前使用const语句声明常量，该常量被称为模块级常量，该模块中的所有过程都可以使用它；

如果想让声明的常量在所有模块中都可以使用它，那么应该在模块的第一个过程之前使用public语句将它声明为公共常量。

对象，集合及对象的属性和方法

对象就是东西，使用VBA代码操作和控制的东西，属于名词。在VBA中，Excel的工作簿，工作表，单元格等都是对象，图表，透视表，图片等也都是对象，甚至于单元格的边框线，插入的批注都是对象…

集合也是对象，它是对多个相同类型对象的统称。

每个对象都有属性，对象的属性可以理解为这个对象包含的内容或者具有的特征。对象和属性是相对而言的。单元格相对于字体来说的对象，但是单元格相对于工作表而言是属性

方法是在对象上执行的某个动作或者操作，每个对象都有其对应的一个或者多个方法。方法和属性的区别是属性返回对象包含的内容或者具有的特点，如子对象、颜色、大小等；方法是对对象的一种操作，如选中，激活等

VBA中的运算符

算数运算符

运算符	作用	示例
+	求两个数的和	5+9=14
-	求两个数的差，或者求一个数的相反数	8-3=5
*	求两个数的积
/	求两个数的商
\	求连个数相处后所得的商的整数	5\2=2
^	求某个数的次方
Mod	求两个数相除后的余数	12 mod 9 =3

比较运算符

运算符	作用	语法	返回结果
=	比较两个数据是否相等	expression1=expression2	相等返回TRUE，不相等返回false
<>	不等于	expression1<> expression2	与上相反
>	比较两个数的大小	expression1> expression2
>=	比较两个数的大小	expression1>= expression2
<	比较两个数的大小	expression1<expression2
<=	比较两个数的大小	expression1<=expression2
is	比较两个对象的引用变量	对象1 is 对象2	当对象1和对象2 引用相同的对象时返回TRUE，否则返回false
like	比较两个字符串是否匹配	字符串1 like 字符串2	当字符串1与字符串2匹配时返回TRUE，否则返回false

通配符

通配符	作用	代码举例
*	代替任意多个字符	“李家俊” like “李*”
?	代替任意单个字符	“李家俊” like “李??”
#	代替任意单个数字	“商品5” like “商品#”
[charlist]	代替位于charlist中的任意一个字符	“I” like “[A-Z]”
[!charlist]	代替不位于charlist中的任意一个字符	“I” like “[!A-Z]”

逻辑运算符

运算符	作用	语句形式	计算规则
and	执行逻辑“与”运算	表达式1 and 表达式2	当表示式1和表达式2的值都为TRUE时，返回TRUE，否则返回false
or	执行逻辑 “或”运算	表达式1 or 表达式2	二者之一为真返回TRUE，同时为false，返回false
not	执行逻辑“非”运算	not 表示	取反运算
xor	执行逻辑“异或”运算	表达式1 xor 表达式2	当表达式1和表达式2返回的值不相同时返回TRUE，否则返回false
eqv	执行逻辑“等价”运算	表达式1 eqv 表达式2	当表达式1和表达式2返回的值相同时返回TRUE，反之false
Imp	执行逻辑“蕴含”运算	表达式1 imp 表达式2	当表示1的值为TRUE，表达式2的值为false时返回false，否则返回TRUE

VBA内置函数

函数虽然很多，但是我们不需要很精确的记住它们。**如果记得某个函数大致拼写，在编写代码时只要在【代码窗口】中输入“VBA.”，就可以在系统显示的函数列表中选择需要使用的函数。

执行程序执行的基本语句结构

if语句

在VBA中，if语句的规则如下：

if 条件 then 语句 else 条件

select case语句

尽管使用if语句可以解决“多选一”的问题，但当判断的选择条件过多时，使用多个elseif语句或多个if语句，就像一句话里用了太多的如果，会为理解代码逻辑带来困难。通常，当需要在三种或以上的策略中做出选择时，我们会选择使用select case 语句来解决问题

Sub test()
    Select Case Range("B2").Value
        Case Is >= 90
            Range("C2").Value = "优秀"
        Case Is >= 80
            Range("C2").Value = "良好"
        Case Is >= 60
            Range("C2").Value = "及格"
        Case Is < 60
            Range("c1").Value = "不及格"
    End Select
    
End Sub

for 循环

在VBA中定义for循环的语法规则如下：

for 循环变量=初值 to 终值 step 步长值
    循环体
next 循环变量名

for循环都要以next结尾

Sub test()
    Dim irow As Byte
    Dim i As Byte
    For i = 1 To 10 Step 1
        Select Case Range("B" & i).Value
            Case Is > 100
                Range("C" & i).Value = "信息错误"
            Case Is >= 90
                Range("C" & i).Value = "优秀"
            Case Is >= 80
                Range("C" & i).Value = "良好"
            Case Is >= 60
                Range("C" & i).Value = "及格"
            Case Is < 60
                Range("C" & i).Value = "不及格"
        End Select
    Next i
End Sub

for each…next语句循环处理集合或数组中的成员

当需要循环处理一个数组的每个元素或者集合中的每个成员时，使用for each……next语句

Sub test()
   Dim i As Byte
  j = 1
    For Each sht In Worksheets
        Range("D" & j).Value = sht.Name
        j = j + 1
    Next sht
   
End Sub

do while循环

do while语句可以分为两种，按照设置循环条件的位置区分，可以分为开头判断式和截尾判断式。其语句结构如下：

1. 开头判断式

do [while 循环条件]
    循环体
    exit do
    循环体
loop

2. 截尾判断式

do 
    循环体
    exit do
    循环体
loop [while 循环条件]

每个do语句都必须以loop结尾，当循环进行到loop处时，会重新回到do语句判断条件是否成立

Sub test()
   Dim i As Byte
   i = 1
   Do
        Worksheets.Add
        i = i + 1
    Loop While i < 5
End Sub

使用Goto语句，让程序转到另一条语句去执行

要让goto语句清楚的知道要转向的目标语句，可在目标语句之前加上一个带冒号的文本字符创，或者不带带冒号的数字标签，然后在goto的后面写上标签名

Sub test()
   Dim i As Integer
   Dim sum As Long
   i = 1
x:    mysum = mysum + i
    i = i + 1
    If i <= 100 Then GoTo x
    MsgBox "1到100的和为：" & mysum
End Sub

with语句，简写代码

当需要对相同的对象进行多次操作时，往往会编写一些重复的代码。如果不想重复多次的录入相同的语句，可以用with语句简化

Sub fontest()
    With Worksheets("Sheet1").Range("A1").Font
        .Name = "仿宋"
        .ColorIndex = 3
        .Bold = True
        .Size = 12
    End With
End Sub