01 语法说明

都知道学会了英语语法，再加上大量的词汇基础，就算基本掌握了英语了。 类似的要使用vba，也要入乡随俗，了解他的构成，简单的说vba包含数据类型、 变量/常量、对象和常用的语句结构。

不过呢在量和复杂度上远低于英语，不用那么痛苦的记单词了，所以vba其实很简单的。 熟悉了规则之后剩下就是查官方函数啦，查Excel提供的可操作对象啦。

顺带一提的是，函数其实也很容易理解，方便使用。拿到一个函数，例如Sum， 只要知道它是求多个数的和就够了，剩下的就是用了。例如Sum(1000,9)结果就是1009了。 函数的一大好处就是隐藏具体实现细节，提供简洁的使用方法。

1.1 数据和数据类型

Excel里的每一个单元格都是一个数据，无论是数字、字母或标点都是数据。 对数据排排队，吃果果，对不同的数据扔到不同的篮子里归类，篮子就是数据类型了。

在Excel-vba中，数据类型只有数值、文本、日期、逻辑或错误五种类型。 前四种最为常用。具体描述参见下表：

表1.1 VBA数据类型

补充一点是，数组就像一筐水果，里面可以存不止一个数据。 他不是一个具体的数据类型，叫数据结构更合适些。

1.2 常量和变量

定义后不能被改变的量，就是常量；相反的变量就能修改具体值。

在vba里，使用一个 变量/常量 要先声明。

常量声明方法如下：
Const 常量名称 As 数据类型 = 存储在常量中的数据 例如：

Const PI As Single = 3.14 ' 定义一个浮点常量为PI，值为3.14

变量声明方法如下：

Dim 变量名 As 数据类型

变量名，必须字母或汉字开头，不能 包含空格、句号、感叹号等。

数据类型，对应上面 ↑　表1.1里的那些

更多的声明方法，跟Dim声明的区别是作用范围不同：

Private v1 As Integer   ' v1为私有整形变量
Public v2 As String     ' v2为共有字符串变量
Static v3 As Integer    ' v3为静态变量，程序结束后值不变

' 变量声明之后，就可以赋值和使用了
v1 = 1009
v2 = "1009"
v3 = 1009

' 使用类型声明符，可以达到跟上面同样的效果
public v2$  ' 与 Public v2 As String 效果一样

' 声明变量时，不指定具体的类型就变成了Variant类型，根据需要转换数据类型
Dim v4

1.3 数组

使用数组和对象时，也要声明，这里说下数组的声明：

' 确定范围的数组，可以存储b - a + 1个数，a、b为整数
Dim 数组名称(a To b) As 数据类型

Dim arr(1 TO 100) As Integer ' 表示arr可以存储100个整数
arr(100) '表示arr中第100个数据

' 不指定a，直接声明时，默认a为0
Dim arr2(100) As Integer ' 表示arr可以存储101个整数,从0数
arr2(100) '表示arr2中第101个数据

' 多维数组
Dim arr3(1 To 3,1 To 3,1 To 3) As Integer ' 定义了一个三维数组，可以存储3*3*3=27个整数

' 动态数组，不确定数组大小时使用
Dim arr4() As Integer   ' 定义arr4为整形动态数组
ReDim arr4(1 To v1)     ' 设定arr4的大小，不能重新设定arr4的类型

除了用Dim做常规的数组的声明，还有下面这些声明数组的方式:

' 使用Array函数将已知的数据常量放到数组里
Dim arr As Variant        ' 定义arr为变体类型
arr = Array(1, 1, 2, 3, 5, 8, 13, 21) ' 将整数存储到arr中,索引默认从0开始

' 使用Split函数分隔字符串创建数组
Dim arr2 As Variant
arr2 = Split("hello, world", ", ") ' 按,分隔字符串 hello,world 并赋值给arr2

' 使用Excel单元格区域创建数组
' 这种方式创建的数组，索引默认从1开始
Dim arr3 As Variant
arr3 = Range("A1:C3").Value   ' 将A1:C3中的数组存储到arr3中
Range("A4:C6").Value= arr3    ' 将arr3中的数据写入到A4:C6中的区域

数组常用的函数

函数说明

UBound(Array arr,[Integer i]);
UBound为函数名
arr和i 为UBound的的参数，用中括号括起来的表示i为非必填参数
arr和i 之前的Array，Integer表示对应参数的数据类型

补充 VBA 内置函数列表

1.4 运算符

运算符的作用是对数据进行操作，像加减乘除等。这块不再具体说明，列一下vba中常用的运算符。

' Like是个比较有用的运算符，常用来做匹配或模糊匹配。
' 在模糊匹配的时候，有一些通配符能方便模糊匹配规则的书写
"这是一个demo1" Like "*demo1" = True    ' * 号表示匹配任意多个字符
"这是一个demo2" Like "????demo2" = True ' ? 号表示匹配任意单个字符
"这是一个demo3" Like "*demo#" = True    ' # 号表示匹配任意数字

三目运算符

正常在VBA中没有类似java的 expression ? true : false 写法，但是可以使用 IFF 来代替：

x = IIF(expression, A, B）
x = IIF(条件, 如果成立A赋值给X, 如果不成立B赋值给X）

作用也等同于如下：

If ... Then
Else
End If

1.5 语句结构

程序通常都是顺序依次执行的。语句结构用来控制程序执行的步骤， 一般有选择语句、循环 语句。

1.5.1 选择语句

选择语句用来判断程序执行那一部分代码

语法：If ... Then ... End If
If选择可以嵌套使用

常用的三种形式：

1. 普通模式

If 10 > 3 Then
    操作1  ' 执行这一步
End If

' 增加Else和Else If逻辑
If 1 > 2 Then
    操作1
ElseIf 1 = 2 Then
    操作2
Else
    操作3  ' 执行这一步
End If

2. 嵌套If语句

If 10 > 3 Then
    If 1 > 2 Then
        操作1
    Else
        操作2  ' 执行这一步
    End If
Else
    操作3
End If

3. Select ... Case ... 多选一，类似于java中的 Switch ... Case ... 语句

Dim Length As Integer
Length = 10
Select Length
    Case Is >= 8
        操作1  ' 执行这一步
    Case Is > 20
        操作2
    Case Else
        操作3
End Select

sample code:

Private Sub switch_demo_Click()
    Dim MyVar As Integer
    MyVar = 1

    Select Case MyVar
        Case 1
            Debug.Print "The Number is the Least Composite Number"
        Case 2
            Debug.Print "The Number is the only Even Prime Number"
        Case 3
            Debug.Print "The Number is the Least Odd Prime Number"
        Case Else
            Debug.Print "Unknown Number"
    End Select
End Sub

1.5.2 循环语句

循环语句用来让程序重复执行某段代码

1. 普通For ... Next循环
   语法：For 循环变量 = 初始值 To 终值 Step 步长
   注：在VBA循环中可以使用Exit关键字来跳出循环，类似于Java中的break， 在for循环中语法为：Exit For，在do while循环中为：Exit Do，也可以利用GoTo语句 跳出本次循环，详见：1.5.3 GoTo语句
   

Dim i As Integer
For i = 1 To 10 Step 2 ' 设定i从1到10，每次增加2，总共执行5次
    操作1   ' 可以通过设定 Exit For 退出循环
Next i

2. For Each ... 循环
   语法：For Each 变量 In 集合或数组

Dim arr
Dim i As Integer
arr = Array(1, 2, 3, 4, 5)
For Each i In arr ' 定义变量i，遍历arr数组
    操作1
Next i

3. Do ... While循环
   语法：
   

• 前置循环条件：
  

• 后置循环条件：
  

Sample code:

Dim i As Integer
i = 1
Do While i < 5  ' 循环5次
    i = i + 1
Loop

' ===============================================
' 将判断条件后置的Do...While
Dim i As Integer
i = 1
Do
    i = i + 1
Loop While i < 5 ' 循环4次

4. Do Until 直到...循环
   语法：
   Do Until 表达式 表达式为真时跳出循环

Dim i As Integer
i = 5
Do Until i < 1  
    i = i - 1
Loop

' ===============================================
' 后置的Do Until
Dim i As Integer
i = 5
Do
    i = i - 1
Loop Until i < 1  

1.5.3 GoTo语句

GoTo 无条件地分支直接跳转到过程中指定的行。

注： GoTo语句大多用于错误处理时，但会影响程序结构，增加阅读和代码调试难度， 除非必要时，应尽量避免使用GoTo语句。

Sub TestGoTo

    Dim lngSum As Long, i As Integer
    i = 1

JUMPX:
    i = i + 1
    If i <= 100 Then GoTo JUMPX
    Debug.Print "1到100的自然数之和是：" & lngSum

End Sub

CONTINUE

循环中实现continue操作，类似java语言的continue直接跳出本次循环

Sub continueTest()
    Dim i

    For i = 0 To 5
        If i = 1 Then
            '// 跳转到CONTINUE部分
            GoTo CONTINUE
        ElseIf i = 3 Then
            '// 跳转到CONTINUE部分
            GoTo CONTINUE
        End If

        '//没有GoTo语句的时候打印counter: i
        Debug.Print i

CONTINUE:   '// countinue跳转块，可以写逻辑，如果没有逻辑就直接进行下次循环
    Next

End Sub

选择和循环提供了多种实现同一目的的语句结构，他们都能实现同样的作用， 差别一般是初始条件。还有书写的复杂度。正确的选择要使用的语句结构， 代码逻辑上会更清楚，方便人的阅读。

简写

在操作对象的属性时常常要先把对象调用路径都写出来，用with可以简化这一操作

' 简化前
WorkSheets("表1").Range("A1").Font.Name="仿宋"
WorkSheets("表1").Range("A1").Font.Size=12
WorkSheets("表1").Range("A1").Font.ColorIndex=3

' 使用`With`
With WorkSheets("表1").Range("A1").Font
    .Name = "仿宋"
    .Size = 12
    .ColorIndex =3
End With

1.6 过程(Sub)和函数(Function)

Sub 和 Function 是VBA提供的两种封装体。

• 利用宏录制得到的就是Sub。
• Sub 定义时无需定义返回值类型，而 Function 一般需要用 “As 数据类型” 定义函数返回值类型。
• Sub 中没有对过程名赋值的语句，而 Function 中有对函数名赋值的语句，一般在函数最后返回值，格式如下：

Set functionName = xxxxxx

• 调用过程：调用 Sub 过程与 Function 过程不同。调用 Sub 过程的是一个独立的语句，而调用函数过程只是表达式的一部分。另外，自定义函数并不允许修改工作表和单元格格式 (A UDF will only return a value it won't allow you to change the properties of a cell/sheet/workbook. )。但是，与 Function 一样，Sub 也可以修改传递给它们的任何变量的值。
• 调用 Sub 过程有两种方法：
  • 以下两个语句都调用了名为 ProcExcel 的 Sub 过程。

Call  ProcExcel (FirstArgument, SecondArgument)
ProcExcel  FirstArgument, SecondArgument

注意当使用 Call 语法时，参数必须在括号内。若省略 Call 关键字，则也必须省略参数两边的括号。

1.6.1 Sub 过程

[Private|Public] [Static] Sub 过程名([参数列表 [As 数据类型]])
    [语句块]
End Sub
' [Private|Public]定义过程的作用范围
' [Static]定义过程是否为静态
' [参数列表]定义需要传入的参数

调用Sub的方法有三种，使用Call、直接调用和Application.Run

1.6.2 Function 函数

vba内部提供了大量的函数，也可以通过Function来定义函数，实现个性化的需求。

[Public|private] [Static] Function 函数名([参数列表 [As 数据类型]]) [As 数据类型]
    [语句块]
    [函数名=过程结果]
End Function

使用函数完成上面的例子：

参数传递

参数传递的方式有两种，引用和传值。 传值，只是将数据的内容给到函数，不会对数据本身进行修改。 引用，将数据本身传给函数，在函数内部对数据的修改将同样的影响到数据本身的内容。

参数定义时，使用ByVal关键字定义传值，子过程中对参数的修改不会影响到原有变量的内容。 默认情况下，过程是按引用方式传递参数的。在这个过程中对参数的修改会影响到原有的变量。 也可以使用ByRef关键字显示的声明按引用传参。

Sub St1(ByVal n As Integer, ByRef range)
    ...Other code
End SUb

1.7 正则表达式(Regular Expression)

在VBA中使用正则表达式，因为正则表达式不是vba自有的对象， 故此要用它就必须采用两种方式引用它：一种是前期绑定，另外一种是后期绑定。

前期绑定：就是手工勾选工具/引用中的Microsoft VBScript Regular Expressions 5.5； 然后在代码中定义对象：Dim regExp As New RegExp；
后期绑定：使用CreateObject方法定义对象：CreateObject("vbscript.regexp")

RegExp对象的属性：

• Global – 设置或返回一个Boolean值，该值指明在整个搜索字符串时模式是全部匹配还是只匹配第一个。如果搜索应用于整个字符串，Global 属性的值应该为 True，否则其值为 False。默认的设置为True。
• Multiline – 返回正则表达式是否具有标志, 缺省值为False。如果指定的搜索字符串分布在多行，这个属性是要设置为True的。
• IgnoreCase – 设置或返回一个Boolean值，指明模式搜索是否区分大小写。如果搜索是区分大小写的，则IgnoreCase 属性应该为False；否则应该设为True。缺省值为True。
• Pattern – 设置或返回被搜索的正则表达式模式。被搜索的正则字符串表达式。它包含各种正则表达式字符。

RegExp对象的方法：

• Execute – 对指定的字符串执行正则表达式搜索。需要传入要在其上执行正则表达式的文本字符串。正则表达式搜索的设计模式是通过RegExp对象的Pattern来设置的。Execute方法返回一个Matches集合，其中包含了在string中找到的每一个匹配的Match对象。如果未找到匹配，Execute将返回空的Matches集合。
• Replace – 替换在正则表达式查找中找到的文本。
• Test – 对指定的字符串执行一个正则表达式搜索，并返回一个Boolean值指示是否找到匹配的模式。Global属性对Test方法没有影响。如果找到了匹配的模式，Test方法返回True；否则返回False。
• MatchCollection对象与Match对象 匹配到的所有对象放在MatchCollection集合中，这个集合对象只有两个只读属性：
• Count：匹配到的对象的数目
• Item：集合的又一通用方法，需要传入Index值获取指定的元素。 一般，可以使用ForEach语句枚举集合中的对象。集合中对象的类型是Match。
• Match对象有以下几个只读的属性：
  • FirstIndex – 匹配字符串在整个字符串中的位置，值从0开始。
  • Length – 匹配字符串的长度。
  • Value – 匹配的字符串。
  • SubMatches – 集合，匹配字符串中每个分组的值。作为集合类型，有Count和Item两个属性。

Sample Code（前期绑定）：

Private Function IsStringDate(ByVal strDate As String)
    Dim strDatePattern
    ' 前期绑定
    Dim regEx As New RegExp, matches

    Dim str MatchContent As String

    strDatePattern = "^(([0-9])|([0-2][0-9])|([3][0-1]))\-(Jan|Feb|Mar|Apr|May|Jun|Jul|Aug|Sep|Oct|Nov|Dec)\-\d{4}$"

    With regEx
        .Global = True      ' 搜索字符串中的全部字符，如果为假，则找到匹配的字符就停止搜索！
        .MultiLine = False  ' 是否指定多行搜索
        .IgnoreCase = True  ' 指定大小写敏感（True）
        .Pattern = strDatePattern   ' 所匹配的正则
    End With

    If regEx.Test(strDate) Then     ' 如果与正则相匹配
        Set matches = regEx.Execute(strDate)
        MatchContent = matches(0).Value
    Else
        MatchContent = "Not Matched"
    End If

    IsStringDate = regEx.Test(strDate)

End Function

Sample Code（后期绑定）：

Function ExtractNumber(str As String) As String
    Dim regEx As Object
    Set regEx = CreateObject("vbscript.regexp")  ' 后期绑定
    With regEx
        .Global = True       ' 搜索字符串中的全部字符，如果为假，则找到匹配的字符就停止搜索！
        .Pattern = "\D"      ' 非数字字符的正则表达式
        ExtractNumber = .Replace(str, "")        ' 把非数字字符替换成空字符串
    End With
    Set regEx = Nothing      ' 清除内存中的对象变量的地址，即释放内存。
End Function

1.8 注释（Comments code）

个人觉得代码注释起着非常重要的作用。 -- bluetata 11/28/2018 18:40

注释语句是用来说明程序中某些语句的功能和作用；VBA 中有两种方法标识为注释语句。
单引号 ' 举例：' 定义全局变量；可以位于别的语句之尾，也可单独一行。
Rem 举例：Rem 定义全局变量；只能单独一行

以下列举出了不同级别的注释代码，也可以点击这里查看 VBA Sample Code。

1. 源码概要注释/Source version Comments Code

在每个source文件的最开头

'--------------------------------------
' Creation date : 03/05/2017  (cn)
' Last update   : 11/28/2018  (cn)
' Author(s)     : Sekito.Lv
' Contributor(s):
' Tested on Excel 2016
'--------------------------------------

2. 区块注释/Use Title Blocks Comments code for Each Macro

在每个Function或者Sub上下，根据个人风格，可以在紧贴在函数上面一行处， 也可以在函数名的下面一行处。

'=======================================================
' Program:   DoMemoData
' Desc:      Writes memo data to the memo sheet
' Called by: PrintControl
' Call:      DoMemoData wbkReport, oStopRow
' Arguments: wbkReport--Name of the report workbook
'            oStopRow--Number of the last row to process
' Comments: (1) RunReport initializes the m_oMemoRowNum
'               variable
'           (2) wksMemo doesn't need to be static. And
'               it's over-defined. Fix this at some
'               point.
' Changes----------------------------------------------
' Date        Programmer    Change
' 11/26/2018  Sekito.Lv     Written
' 11/28/2018  Sekito.Lv     Re-set memo object. This is
'                           needed at times in Excel 8
'                           when the report workbook must
'                           close then re-open.
'=======================================================
Sub DoMemoData(wbkReport As Workbook, oStopRow As Long)

3. 行内注释/Use In-Line Comments

' If this routine was called by the batch routine...
If g_bCalledByBatch Then

    'Get the reference of the changing date cell
    sDateRef = GetNameVal("ChgDateCell", 0, g_nReference)

    ' If the date name is empty, return null sDateFormula
    If sDateRef = g_sNull Then
        sDateFormula = g_sNull

    ' Else, get the beginning formula in the date cell
    Else
        sDateFormula = m_wbkReport.Worksheets(1). _
        Evaluate(sDateRef).Formula
    End If
Else

4. 函数列表注释/List of Function Comments

一般紧挨着源码概要注释下面，与其空一行到两行

'-------------------------------------
' List of functions :
' - 1  - PublicHolidayFr
' - 2  - WorkingDay
' - 3  - WorkableDay
' - 4  - NextWorkingDay
' - 5  - NextWorkableDay
' - 6  - PrevWorkingDay
'-------------------------------------

1.9 补充

• 在vba中使用 '进行代码注释

• 在很长的语句中使用_来分割成多行

• 在有很多嵌套判断中，代码的可读性会变得很差，一般讲需要返回的内容及时返回，减少嵌套

• Sub中默认按引用传递参数，所以注意使用，一般不要对外面的变量进行修改，将封装保留在内部

• Dim和Set的关系及区分

很明显的是 vba中使用Dim设定变量类型，Set将对象引用赋值给变量

' 将Range对象赋值给变量rg
Dim rg As Range         ' 声明rg为Range对象
Set rg = Range("A1")    ' 设定rg为Range("A1")的引用，之后操作rg和操作Range("A1")一样了

' 如果不使用Set，下面的代码将报错
Dim rg As Range
rg = Range("A1")   ' 这段代码将报错

' 在非显示声明rg的前提下，下面的代码将会得到不一样的结果
rg = Range("A1")       ' rg将会是Range("A1")的内容，rg的类型将会是一种基本类型，Integer/String等
Set rg = Range("A1")   ' 这种情况下，rg将会是Range对象

• VBA中变量用Dim定义和不用Dim定义而直接使用有何区别？

用Dim语句声明变量就是定义该变量应存储的数据类型； 如果不指定数据类型或对象类型，也就是不用Dim定义，且在模块中没有 Deftype 语句， 则该变量按缺省设置是 Variant 类型。

• VBA中用Set赋值和不用Set赋值有什么区别？

给普通变量赋值使用Let，Let 可以省略。
给对象变量赋值使用Set，Set 不能 省略。

Sub AssignString()
    Dim strA As String
    Dim strB As String

    strA = "hello"      ' 本句也可写成 LET strA = "hello"
    Set strB = "hello"  ' 错误写法/Compile error
EndSub

1.10 示例

举个排序的例子，要对A1:A20的单元格区域进行排序，区域内的内容为1-100的随机整数， 规则是大于50的倒序排列，小于50的正序排列。将结果显示在B1:B20的区域里。

在这个例子中，首先定义一个Sub过程来随机生成A1:A20区域的内容。 代码如下:

' 创建随机整数，并赋值
Sub createRandom(times As Integer)
    Dim num As Integer
    Dim arr() As Integer
    ReDim arr(times)

    For num = 1 To times
        Randomize (1) ' 初始化随机数
        arr(num) = Rnd(1) * 10000 \ 100 ' Rnd随机数函数生成0~1的浮点数
        ' 上面使用了运算符进行取整，也可以根据需求使用vba内部的取整函数达到同样的效果
        ' arr(num) = Int(Rnd(1) * 100)
        ' arr(num) = Round(Rnd(1) * 100)
        Range("A" & num) = arr(num)
    Next num
End Sub

' 自定义排序
Function defSort(rgs) As Variant
    Dim arr() As Integer
    Dim total As Integer
    Dim rg
    Dim st As Integer  ' 数组开始标记
    Dim ed As Integer  ' 数组结束标记

    Debug.Print "rgs类型:"; TypeName(rgs)
    total = UBound(rgs)
    ReDim arr(total)
    st = 1
    ed = total

    ' 对数组分区
    For Each rg In rgs
        If rg > 50 Then
            arr(ed) = rg
            ed = ed - 1
        Else
            arr(st) = rg
            st = st + 1
        End If
    Next rg

    Dim i As Integer
    Dim j As Integer
    Dim tmp As Integer

    ' 冒泡排序
    For i = 1 To total
        For j = i To total
            If arr(i) > 50 And arr(j) > 50 Then '大于50的倒序排列
                If arr(i) < arr(j) Then
                    tmp = arr(i)
                    arr(i) = arr(j)
                    arr(j) = tmp

                    Debug.Print "大于50的"; i; j; tmp ' 程序运行过程中在立即窗口显示执行内容，用于调试程序
                End If
            Else If arr(i) <= 50 And arr(j) <= 50 Then ' 小于50的正序排列
                If arr(i) > arr(j) Then
                    tmp = arr(i)
                    arr(i) = arr(j)
                    arr(j) = tmp

                    Debug.Print "不大于50的"; i; j; tmp
                End If
            Else
                Exit For
            End If
        Next j
    Next i
    defSort = arr
End Function


' 程序入口
Sub main()
    Const SORT_NUM = 20
    Dim rgs
    Dim arr

    createRandom SORT_NUM ' 初始化待排序区域

    rgs = range("A1:A" & SORT_NUM)
    arr = defSort(rgs)

    ' 循环赋值
    For i = 1 To SORT_NUM
        range("B" & i) = arr(i)
    Next i
End Sub