Excel-vba 基础

参考：https://zhuanlan.zhihu.com/data-analysis-by-xian

1.控件

vba1.png

Private Sub CommandButton1_Click()

Dim V_name1 As Integer                     '单变量声明格式

Dim V_name2, V_name3 As String             '同类型多变量声明格式

Dim V_name4 As Double, V_name5 As Boolean, V_name6 As Date '不同类型多变量声明格式

'数据赋值

V_name1 = 200
V_name2 = "this is a string 1"
V_name3 = "this is a string 2"
V_name4 = 2573.876
V_name5 = True
V_name6 = Now()


MsgBox ("现在时间：" & V_name6) '窗口显示

End Sub

2.数据类型
a.字符串 string
b. 数字类型 整形 integer 长整形 long 单精度浮点型 single 双精度 double
c.日期型 date
d.布尔型 boolean
e.变体型 variant (当不确定数据类型时，可以用来保存)
f.对象型 object （*）
3.数组
3.1静态数组-长度固定不变，默认索引从0开始
Dim MyArray(10 to 20) as String
3.2动态数组-长度可变，待长度确定可用ReDim重新定义数组
Dim MyArray() as String
数组赋值与取值

Private Sub CommandButton1_Click()
Dim arr(5) As String
For i = 1 To 5
    arr(i) = i
Next
v_time = arr(1)
MsgBox ("数值：" & v_time)
End Sub

3.3多维数组
定义一个4行5列的两维数组：Dim MyArr(4,5) as String 或 MyArr(1 to 4, 5 to 8)
定义一个三维数组：Dim MyArr(3,4,5) as String 或 MyArr(1 to 3, 4 to 6, 7 to 9)
4.字典
4.1创建字典：

Dim d As Object
Set d =CreatObject("Scripting.Dictionary")

4.2 字典的6个方法-Add、Exists、Keys、Items、 Remove、RemoveAll
Add方法

 d.Add Key,Item    #key:键，Items值

Exists方法

d.Exists(Key)  #判断关键字是否存在于字典(键),返回True/False

Keys方法

d.Keys()   #返回字典所有的键，类型是数组

Items方法

d.Items()   #返回字典所有的值，类型是数组

Remove方法

d.Remove(key)  #移除指定Key的数据

RemoveAll方法

d.RemoveAll()  #清空字典

5.条件选择
5.1 IF
If...Then...End If
If...Then...Else...End If
If...Then...ElseIf...Then...End If
5.2 Select Case
Select Case 测试表达式
Case 表达式1
' 如果测试表达式等于表达式1，则执行这里的语句
Case 表达式2
' 如果测试表达式等于表达式2，则执行这里的语句
Case 表达式3
' 如果测试表达式等于表达式3，则执行这里的语句
' 后面可以有更多的Case （最后一个可以使用Case Else）

End Select
Sub test2()
    Dim score As Integer
    score = 53
    Select Case score
        Case Is >= 90
            Debug.Print "优"
        Case Is >= 80
            Debug.Print "良"
        Case Is >= 60
            Debug.Print "中"
        Case Else
            Debug.Print "差"
    End Select
End Sub

6.流程控制
6.1 For
a.循环次数已知
for 变量=开始值 to 结束值
循环执行的语句
next

For i = 1 To 10
    Debug.Print i
Next

b.循环次数未知
For Each 循环变量 In 循环对象
' 循环执行的语句
Next 循环变量

遍历数组
For Each a In myArr
    Debug.Print a
Next a

7.1操作工作表

vba表引用.png

Sub vba1()

    Dim sht_slea As Worksheet
    Dim sht_result As Worksheet
    Dim sht_para As Worksheet

    Set sht_slea = Worksheets("SLEA")
    Set sht_result = Worksheets("Check_Result")
    Set sht_para = Worksheets("Parameter")

End Sub

用 Dim 变量名 As Worksheet 的格式来定义一个工作表对象；
用 Set 变量名 ＝ Worksheets("表名") 的格式来把工作表对象赋值给指定的变量；
然后就可以用这个变量来引用或操作对应工作表中的对象和数据了。
7.2操作单元格区域
单元格区域，即Range对象。应该是在Excel VBA中用得最多的对象。Range对象是Worksheet对象的一个子集。所以通常通过worksheet_object.Range()的方式来引用。

Sub test()
    Dim sht_slea As Worksheet
    Dim rng As Range

    Set sht_slea = Worksheets("SLEA")
    Set rng = sht_slea.Range("D2")

    Debug.Print rng
End Sub

输出SLEA表中D2单元格中的数据。
特说明一下。仅在Range对象引用的是单个单元格时，才可以用Debug.Print或者MsgBox来输出Range对象中的内容。引用了多个单元格的Range对象使用Debug.Print或者MsgBox来输出，将会报错。
7.3操作多个单元格区域

Sub vba2()
    Dim sht_slea As Worksheet
    Dim rng As Range
    Set sht_slea = Worksheets("SLEA")
    Set rng = sht_slea.Range("A1:D2")
    '多区域sht_slea.Range("D2:D5, B2:B5")
    rng.Interior.ColorIndex = 16
    'A1:D2填充颜色
    For Each Item In rng
        Debug.Print Item
    Next Item
    '遍历数组rng内容
End Sub

7.4操作单元格对象
单元格，Cells(行号,列号)，即可引用到单个单元格对象。Cells对象也是Worksheet对象的一个子集，以worksheet_object.Cells()的方式来引用。

Sub test2()
    Dim sht_slea As Worksheet

    Set sht_slea = Worksheets("SLEA")
    Debug.Print sht_slea.Cells(1, 2)
End Sub

使用Cells和Range似乎没什么区别，但是在进行数据处理时，我们经常需要动态地把数据读或写入一个单元格中，这时候，用数字表示位置的Cells对象，再结合For循环，操作起来就很方便了。

Sub test3()
    Dim sht_slea As Worksheet
    Set sht_slea = Worksheets("SLEA")
    For r = 1 To 5
        For c = 1 To 4
            Debug.Print sht_slea.Cells(r, c)
        Next
    Next
End Sub

注意：
1.Range("B2:D4")也可以用Range(Cells(2, 2), Cells(4, 4))来表示
2.Range("B2:B4, D2:D4")和Range("B2:B4", "D2:D4")的区别：前者是分别引用B2:B4和D2:D4这两个区域，而后者则表示引用的是从B2:B4开始到D2:D4结束为止的这一整个连续的区域。所以后者其实是等价于Range("B2:D4")。
3.父对象的省略
Worksheet对象，父对象为Workbook，即工作薄；
VBA也提供了一种用法，即Worksheet直接由Application对象引出。Range的父对象是Worksheet对象，Cells对象的父对象也是Worksheet对象。所以在给这些对象赋值时，标准的写法应当要把父对象给写上，如：

Sub test3()
    Dim sht_slea As Worksheet
    Dim titl_rng As Range
    Dim data_rng As Range
    Dim wbk as Workbook

    Set wbk = Application.ThisWorkbook()
    Set sht_slea = wbk.Worksheets("SLEA")
    Set title_rng = sht_slea.Range("A1:D1")
    Set data_rng = sht_slea.Range(sht_slea.Cells(2, 1), sht_slea.Cells(4, 4))
End Sub

VBA中代码涉及到的对象都位于一个工作薄，这个工作薄是激活状态时，则这些父对象是可以省略的（如果有同时打开了多个Excel文件，而在不同的工作薄中有同名的工作表，则忽略父对象时有可能会让程序产生误解，导致执行失败）。默认就是当前（激活的）工作薄。所以在当前工作薄被激活时，上述代码和下面的是等价的：

Sub test4()
    Dim sht_slea As Worksheet
    Dim titl_rng As Range
    Dim data_rng As Range

    Set sht_slea = Worksheets("SLEA")
    Set title_rng = sht_slea.Range("A1:D1")
    Set data_rng = sht_slea.Range(Cells(2, 1), Cells(4, 4))
End Sub

尽量避免意外情况的发生，在单独引用Cells、Range对象时，Worksheet对象也不要省略；
8.1过程和函数

Sub 过程名()
    在过程中执行的代码
End Sub

Function 函数名(参数1, 参数2, ...) As 数据类型
    在函数中执行的代码
    函数名 = 函数执行后的结果
End Function

8.2参数
a.可选参数
格式与必选参数类似，只是在参数名前面用Optional声明参数是可选的。

Sub sub_test(Optional s As String)
    Debug.Print s
End Sub

b.必选参数
必选参数在定义时放置在过程或函数名后面的括号中

Sub sub_test(s As String)
    Debug.Print s
End Sub

在调用上述过程时，可以不传入参数，此时则不会输出任何东西，也不会报错，因为参数是可选的。如果传入了参数，则会输出这个参数。

Sub sub_test(Optional s As String = "Hello, World")
    Debug.Print s
End Sub
带参数调用：
Sub test()
    Call sub_test("I am a boy")
End Sub
不带参数调用：
Sub test()
    Call sub_test
End Sub

c.同时用可选参数与必选参数

Sub sub_test(var As Integer, Optional s As String = "Hello, World")
    Debug.Print var
    Debug.Print s
End Sub
调用
Sub test()
   Call sub_test(50, "Hi, Meinv")
End Sub

9.错误处理
9.1 On Error GoTo
表示如果自本行开始后面的代码执行出错，则跳至指定位置继续执行。

Sub te4()
    On Error GoTo con
    Debug.Print "a" + 3
    Debug.Print 8 - 5
con:
    Debug.Print "error occur"
End Sub

第2行设置了On Error GoTo con，即当第3行出错时，就跳到con标记处，执行自con后的代码。标记后面应加上冒号（:）。第3行中，用双引号包含的a是一个字符串，而3是一个数字，直接将字符串与数字相加是会出错的，所以这里会触发错误，第4行不会被执行到，程序会直接跳转到con处，执行后面的Debug.Print语句。程序输出error occur。
9.2 On Error Resume Next
表示如果代码执行出错，则从出错代码的下一行代码继续执行。

Sub te3()
    On Error Resume Next
    Debug.Print 5 + 3
    Debug.Print "a" + 3
    Debug.Print "error occur"
End Sub

代码第3行执行正常，第4行将出错，于是直接执行第5行。
所以程序的输出是：
8
error occur