一、数据类型

1.变量与类型

• Dim x As Integer
• Dim x As Double
• Dim x As String
• Dim x As Boolean
• Dim x As Date
• Dim x As Range

2.数组

Dim Films(1 To 5) As String

Films(1) = "Lord of the Rings"
Films(2) = "Speed"
Films(3) = "Star Wars"
Films(4) = "The Godfather"
Films(5) = "Pulp Fiction"

3.字符串

• Left(“example text”, 4) // Output: exam
• Right(“example text”, 2) // Output: xt
• Len(“example text”) // Output: 12
• Instr(“example text”, “am”) // Output: 3
• Mid(“example text”, 9, 4) // Output: text

4.特殊符号

连接符号

“Value is ” & Range(“A1”).value

换行符

MsgBox “Line 1” & vbNewLine & “Line 2”

二、操纵单元格

1.范围

Dim example As Range
Set example = Range("A1:D1")

• example.Count
• example.Value = 8
• example.Select
• example.Rows(3).Select
• example.Columns(2).Select
• example.Columns.Count
• example.ClearContents

2.复制与粘贴

Range("A1:A2").Select
Selection.Copy

Range("C4").Select
ActiveSheet.Paste

3.工作簿

• Application.Workbooks(“Book1”).Worksheets(1).Range(“A1”).Value
• Worksheets(1).Range(“A1”).Value
• Worksheets.Count
• Worksheets.Add

4.单元格赋值

• Range(“A1”).value = “Hello”
• Range(“A1”, “D4”).value = “Hello”
• Range(“A1:D4”).Value = “Hello”
• Cells(1,1).value = “Hello”

5.清空单元格

• Range(“A1”, “D4”).Clear
• Range(“A1”).ClearContents

6.公式

• Range(“B1”).Formula = Range(“A1”) * 2
• Range(“B1”) = WorksheetFunction.Average(Range(“A1:A4”))

三、函数

1.消息

MsgBox “This is fun.”

2.函数（返回值）

Function Area(x As Double, y As Double) As Double
	Area = x * y
End Function

3.过程

Sub Area(x As Double, y As Double)
	MsgBox x * y
End Sub

四、条件判断

1.IF-THEN-ELSE-END IF

If score >= 60 Then
    grade = "passed"
Else
    grade = "failed"
End If

2.条件逻辑(AND, OR, NOT)

If Range("B1").Value >= 110 And Range("B2").Value >= 7 Then ...

If Range("B1").Value >= 110 Or Range("B2").Value >= 7 Then ...

If Not Range("B3").Value = "Yes" Then ...

五、循环

1.FOR循环

For i = 1 To 4
	'Do something
Next i

2.FOR Each循环

Dim entry, myRange As Range
Dim sum As Integer
Set myRange = Range("A1:D4")
myRange.Select
sum = 0
For Each entry In myRange
    sum = entry.Value + sum
Next

3.Do Until循环

i = 1
Do Until Cells(i, 1).Value = ""
	i = i + 1
Loop
row_end = i

六、时间与日期

1.日期

Dim exampleDate As Date
exampleDate = DateValue("Jun 19, 2010")

• Year(exampleDate) // Output: 2010
• Month(exampleDate) // Output: 6
• Day(exampleDate) // Output: 19

2.NOW函数

• Hour(Now)
• Minute(Now)
• Second(Now)

3.TimeValue函数

Dim y As Double
y = TimeValue("09:20:01 am") // Output: 0.388900462962963
Hour(y) // Output: 9
Miniute(y) // Output: 20
Second(y) // Output: 01

Reference:

[1] Excel VBA Easy