VBA 入门

写在前面

注意：想要保存包含VBA代码的表单，必须保存为*.xlsm格式

一、基本概念

• 宏：是一段可以运行的 VBA 代码片段

• 注释、给单元格改变颜色

'我是一行注释
'A1 单元格的填充颜色设置为颜色号为 49407 的颜色
Range("A1").Interior.Color = 49407

• 变量、给单元格赋值变量

'声明一个文本类型的变量
Dim s As String
'给 s 变量赋值
s = "Hello World"

'单元格 A1写入变量s的值
Range("A1").Value = s

• 常用 Excel 对象：
  Application 对象，表示 Excel 应用程序。
  Workbook 对象，表示工作簿对象。
  Worksheet 对象，表示工作表对象
  Range 对象，表示单元格区域对象。

• 用户窗体：
  文本控件
  按钮控件
  列表控件
  输入控件

Sub MyCode()
    MsgBox "Hello World"
End Sub

• 强制变量声明，即在模块头部写上以下语句：

Option Explicit

１.１、变量、常量

声明变量使用以下语法：

Dim [变量名] As [数据类型]

常量量使用以下语法：

Const [常量名] As [数据类型] = [值]

声明数组

'固定长度数组声明
Dim [变量名](开始序号 to 结束序号) As [数据类型]
'动态数组声明
Dim [变量名]() As [数据类型]

'实例
'声明包含10个文本类型元素的数组
Dim names(1 to 10) As String
'声明长度未知的文本类型数组
Dim names() As String

'声明数组
Dim arr(1 to 5) As String
'数组赋值
arr(1) = "Zhang San"
arr(2) = "Li Si"
arr(3) = "Wang Wu"

Let用来给变量赋值
Let关键词可以忽略不写，直接以变量开头写赋值语句。

Dim age As Integer
Let age = 30

对象类型变量的赋值set

'声明工作表类型的对象
Dim sheet As Worksheet
'将名称为“绩效表”的工作表，赋到 sheet 变量
Set sheet = Worksheets("绩效表")

由于对象可以包含多个属性，因此 VBA 提供一种同时给多个属性赋值的简单方法。

Dim sheet As Worksheet
Set sheet = Worksheets("绩效表")

With sheet
    .Name = "旧绩效"
    .Visible = False
End With

嵌套 With 结构

Sub MyCode()

    With Worksheets("Sheet1")
        .Name = "新名称"
        .Tab.ThemeColor = xlThemeColorLight1
        .Visible = xlSheetHidden
        
        With .Range("A1:A10")
            .Interior.ThemeColor = xlThemeColorAccent1
            .Font.Size = 12
            .Font.Name = "等线"
        End With
        
    End With
    
End Sub

数据类型

１.２、运算符

算术运算符

比较运算符

逻辑运算符

连接操作符

其他操作符

下划线的使用示例

Sub MyCode()
    
    Dim name As String
    
    name = "Hello" & _
    	"World"
    
End Sub

１.３、字符、日期、时间相关操作

与 Excel 提供的文本函数一样，VBA 也提供多种文本函数，可直接在程序中使用，包括：

给日期变量赋值时，可以直接把日期放置在两个 # 之间赋值，也可以使用数字，还可以把日期作为文本赋值：

Dim birthday As Date
Dim time As Date

birthday = #2018-1-1#
birthday = 43101
birthday = "2018-1-1"

time = #12:00:00#
time = 0.5
time = "12:00:00"

１.４、判断、循环语句

判断

'如果为空，则用上方的单元格的值填充当前单元格
If isBlank Then
    Cells(i, 1) = Cells(i - 1, 1)
End If

多个条件

If 条件表达式 Then
    '真时执行的代码
Else
    '假时执行的代码
End If

选择语句

Select Case 变量
	Case 判断条件 1
    	'条件 1 真时，执行的代码
	Case 判断条件 2
    	'条件 2 真时，执行的代码
	Case 判断条件 3
    	'条件 3 真时，执行的代码
    Case Else
    	'之前的所有条件都不为真时，执行的代码
End Select

循环

'循环开始
For i = 2 To 10
	'这里是循环的代码
Next i


'循环集合
Sub MyCode()

    Dim sh As Worksheet
    
    For Each sh In Worksheets
        Debug.Print sh.Name
    Next sh

End Sub

Exit For 语句用于跳出循环过程

Sub MyCode()

    Dim i As Integer
    Dim sum As Integer
    
    For i = 1 To 10
    
        sum = sum + i
        
        If sum > 30 Then
            Exit For
        End If
        
    Next
    
End Sub

Do While 循环也有两种形式：

Do While … Loop 循环
Do … Loop While 循环

Sub MyCode()

    Dim i As Integer
    Dim sum As Integer
    
    i = 1
    Do While i <= 10
        sum = sum + i
        i = i + 1
    Loop
    
End Sub

Do
    '循环执行的代码
Loop While [条件表达式]

Exit Do 语句用于跳出 Do While 循环。

Do Until 循环也有两种形式：
Do Until … Loop 循环
Do … Loop Until 循环

Do Until [条件表达式]
    '循环执行的代码
Loop


Do
    '循环执行的代码
Loop Until [条件表达式]

goto语句

１.5、过程 Sub

VBA 过程以 Sub 语句开始，以 End Sub 语句结束

Sub [过程名]()
    语句1
    语句2
    ...
    语句n
End Sub

没有参数，直接写过程名，即可调用过程。

Sub Main()
    MySub
End Sub

Sub MySub()
    '代码
End Sub

过程还可以接受一个或多个参数

'声明一个过程
Sub SayHello(name As String)
    Msgbox "Hello" & name
End Sub

'在另一个过程，调用上述过程，调用时，提供一个实际的 name 参数
Sub MyCode()
    SayHello "World 2"
End Sub

使用 Call 关键词调用时，Call 后接过程名。

Sub Main()
    Call MySub(2019,"年")
End Sub

Sub MySub(val1 As Integer, val2 As String)
    '代码
End Sub

提前退出过程 Exit Sub

Sub Main()
    Call MySub
    Msgbox "父过程"
End Sub

Sub MySub()
    Exit Sub
    Msgbox "子过程"
End Sub

立即结束当前运行的所有 VBA 过程 End

Sub Main()
    Call MySub
    Msgbox "父过程"
End Sub

Sub MySub()
	End
    Msgbox "子过程"
End Sub

传递参数可以不按顺序传递

'主入口
Sub Main()
    CustomLog 100, 10 '方式一
    CustomLog num:=100, base:=10 '方式二
    CustomLog base:=10, num:=100 '方式三
End Sub

用 Optional 定义可选参数
可选参数需写在参数列表的末尾，否则 VBA 提示错误。

'声明一个带可选参数的子过程
Sub CustomLog(num As Double, Optional base As Integer)
    '子过程代码
End Sub

调用时，VBA 会提示可选参数，可选的参数放置在中括号中。

可选参数一般会给一个默认值

'声明一个带可选参数的子过程
Sub CustomLog(num As Double, Optional base As Integer = 10)
    Debug.Print Log(num) / Log(base)
End Sub

１.6、函数 Function

函数与过程相比，函数可以有返回值

返回值：并把值赋值给函数名

'声明函数，该函数随机返回 true 或 false。函数需指定返回值类型。
Function RandomLogic() As Boolean
    RandomLogic = Rnd() > 0.5
End Function

有参函数

Function Add2Number(num1 As Double, num2 As Double) As Double
    Add2Number = num1 + num2
End Function

函数与过程6个不同点：
第1点：声明语句不同
第2点：函数可以返回值
第3点：函数需指定返回值类型
第4点：函数主体代码中，返回值赋值到函数自己
第5点：调用函数时，使用类型与函数返回值类型相同的变量获得返回值
第6点：函数可在单元格内公式中使用

自定义函数也一样可以嵌套使用

１.7、ByVal 复制传递 和 ByRef 引用传递

在定义过程或函数时，如果需要传递变量，则每个参数需要指定传递类型。传递类型有 2 种，分别是 ByVal 和 ByRef 。

• ByVal：传递变量时，复制一份该变量，传入过程或函数。在过程和函数内部对该变量进行修改，只对该副本有效，对上一级过程（父过程）的变量没有影响。
• ByRef：传递变量时，将该变量的引用地址传入过程或函数。传入引用地址意味着，在过程或函数内部对其修改时，也会影响上一级过程（父过程）中的变量的值。

Sub Test()

    Dim msg As String
    msg = "main"
    
    TestSub1 msg
    
    Msgbox msg

End Sub

'ByVal 传递类型
Sub TestSub1(ByVal msg As String)
    msg = "val"
End Sub

省略传递类型
默认情况下，当省略传递类型时，默认值是 ByRef，因此以下两种写法是等效的。

'指定 ByVal 传递类型
Sub TestSub1(ByRef msg As String)

End Sub

'省略传递类型
Sub TestSub1(msg As String)

End Sub

１.8、作用域

１.8.1、变量作用域

1、在过程或函数内部声明的变量，只有在当前过程或函数内被使用。
2、一个模块中，在任何一个过程和函数外面，使用关键词 Private 或 Dim 声明的变量，称之为模块变量，其作用域是当前模块。
3、工程级别变量，在所在模块顶部声明 Option Private Module 修饰语句前提下，在过程或函数外面，使用关键词 Public 声明的变量，其作用域是当前工程。

作用域冲突：
当相同名称的变量，多次以不同的作用域声明时，出现作用域冲突。这种情况，VBA 会自动以就近原则使用变量，即优先使用最近定义的变量。

１.8.2、过程或函数作用域

使用 Private 关键词声明的过程或函数，具备模块作用域，只能在当前模块中使用。

Private Sub Test()

End Sub

在模块中，顶部声明 Option Private Module 修饰语句，并且直接声明或使用 Public 关键词声明的过程或函数，具备工程作用域，在当前工程的所有模块中使用。

Option Private Module

Sub Test1()
End Sub

Public Sub Test2()
End Sub

在模块中，直接声明或使用 Public 关键词声明的过程或函数，具备全局作用域。
可以在打开的任何一个工作簿中使用。

Sub Test1()

End Sub
Public Sub Test2()

End Sub

二、vba实用代码

2.1、 从选择中突出显示重复项

此宏将检查您选择的每个单元格并突出显示重复值。您还可以更改代码中的颜色

Sub HighlightDuplicateValues()
	Dim myRange As Range
	Dim myCell As Range
	Set myRange = Selection
	For Each myCell In myRange
		If WorksheetFunction.CountIf(myRange, myCell.Value) > 1 Then
			myCell.Interior.ColorIndex = 36
		End If
	Next myCell
End Sub