基础知识

'单元格赋值

Sub setval()

    Range("A1").Value = 100

End Sub

---

'单元格填充色

Sub setcolor()

    Range("B3").Interior.ColorIndex = 3

    '值有1-56

End Sub

---

'获取单元格的值

Sub alert()

    x1 = Sheets(1).Cells(1, 1)

    x2 = Sheets(1).[A2]

    MsgBox (x1)

    MsgBox (x2)

End Sub

---

'定义数组

Dim arr1()

Dim arr2(5)

arr3 = Array("apple", "orange", "grapes")

Dim arr(5)

arr(0) = "1"

arr(1) = 100

arr(2) = 2.45

arr(3) = "1"

arr(4) = #10/25/2018#

arr(5) = #12:45:00 PM#

---

'定义多维数组

Dim arr(2, 3)

arr(0, 0) = "apple"

arr(0, 1) = "orange"

arr(0, 0) = "grapes"

'定义常量

Sub const_var()

    Const myinteger As Integer = 25

    MsgBox (myinteger)

End Sub

---

'定义变量

Sub var()

    Dim str As String

    str = "hello world"

    MsgBox (str)

End Sub

---

'单元格的格式方法

    '1.Range

        'Range ("A1") '表示A1单元格

        'Range ("A1:A9")  '表示A1-A9单元格

    '2.[]

        '[A1]    '表示A1单元格

        '[A1:A9]    '表示A1到A9单元格

    '3.Cells(第几行，第几列)

        'Cells(1,1)  '表达第一行第一列的单元格

---

'单元格的复制、粘贴

Sub copy()

    Range("B3").copy Range("A3")

    '将A3的值，复制到B3单元格

End Sub

---

Sub cut()

    Range("A1").cut Range("B3")

End Sub

---

'条件判断结构 if-then

Sub if_test()

    B3 = Range("B3").Value

    If B3 > 10 Then

        MsgBox ("大于10")

    ElseIf B3 < 10 Then

        MsgBox ("小于10")

    Else

        MsgBox ("aaaa")

    End If

    MsgBox (B3)

End Sub

---

'选择判断结构 select case

Sub select_test()

    B3 = Range("B3").Value

    Select Case B3

    Case 1 To 10

        msg = "1到10之间"

    Case 11 To 20

        msg = "11到20之间"

    Case 21, 22

        msg = "21,22"

    Case Else

        msg = "其他的值"

    End Select

    MsgBox (msg)

End Sub

---

'循环结构 for next

Sub for_test()

    For i = 1 To 10 Step 1

        If i > 5 Then

            Exit For

        End If

        Range("D" & i).Value = i

    Next i

End Sub

---

'循环结构 for each 用于为数组或集合中的每个元素

Sub for_each_test()

    fruits = Array("苹果", "香蕉", "橘子")

    For Each Item In fruits

        MsgBox (Item)

    Next

End Sub

---

'do  while  loop

Sub do_while_loop_test()

    Do While i < 5

    '在循环开始时，判断条件是否符合

        If i > 3 Then

            MsgBox ("跳出循环")

            Exit Do

        End If

        i = i + 1

        MsgBox ("the value of i is :" & i)

    Loop

End Sub

---

Sub do_while_loop_test()

    Do

        If i > 3 Then

            MsgBox ("跳出循环")

            Exit Do

        End If

        i = i + 1

        MsgBox ("The value of i is : " & i)

    Loop While i < 5

    '在循环结束时，判断条件是否符合

End Sub