VBA-Excel程序控制语法

1、If…Then语句 

Sub SayHello1()
    If Time < 0.5 Then MsgBox "早上好!"
    If Time >= 0.5 Then MsgBox "下午好！"
End Sub

Sub SayHello2()
    If Time < 0.5 Then
        MsgBox "早上好!"
    Else
        MsgBox "下午好！"
    End If
End Sub

Sub SayHello3()
    If Time < 0.5 Then
        MsgBox "早上好!"
    ElseIf Time > 0.75 Then
        MsgBox "晚上好！"
    Else
        MsgBox "下午好！"
    End If
End Sub

2、Select Case语句 

Sub SayHello1()
    Select Case Time
        Case Is < 0.5
            MsgBox "早上好!"
        Case Is > 0.75
            MsgBox "晚上！"
    End Select
End Sub

Sub SayHello2()
    Select Case Time
        Case Is < 0.5
            MsgBox "早上好!"
        Case Is > 0.75
            MsgBox "晚上！"
        Case Else
            MsgBox "下午好！"
    End Select
End Sub

 
Sub xingji()
    Dim xj As String
    Select Case Cells(2, "H")
        Case Is < 85
            xj = "不评定"
        Case Is < 100
            xj = "一星级"
        Case Is < 115
            xj = "二星级"
        Case Is < 130
            xj = "三星级"
        Case Is < 150
            xj = "四星级"
        Case Else
            xj = "五星级"
    End Select
    Cells(2, "I") = xj
End Sub

3、For…Next语句

 语法结构：

For <循环变量>=<初值>To<终值> [Step 步长值]
　　<循环体>
        [Exit For]  '可以在循环体中任意处加一句或多句Exit For，当遇到这个语句，退出For循环，执行Next后语句
　　<循环体>
Next [循环变量]
 

Sub xingji()
    Dim xj As String, i As Integer
    For i = 2 To 19 Step 1
        Select Case Cells(i, "H")
            Case Is < 85
                xj = "不评定"
            Case Is < 100
                xj = "一星级"
            Case Is < 115
                xj = "二星级"
            Case Is < 130
                xj = "三星级"
            Case Is < 150
                xj = "四星级"
            Case Else
                xj = "五星级"
        End Select
        Cells(i, "I") = xj
    Next i
End Sub

4、Do While语句

语法结构1：
Do [While 逻辑表达式]
    <循环体>
    [Exit Do]
    [循环体]
Loop
对应示例：

Sub xingji()
    Dim xj As String, i As Integer
    i = 2
    Do While Cells(i, "H") <> ""
        Select Case Cells(i, "H")
            Case Is < 85
                xj = "不评定"
            Case Is < 100
                xj = "一星级"
            Case Is < 115
                xj = "二星级"
            Case Is < 130
                xj = "三星级"
            Case Is < 150
                xj = "四星级"
            Case Else
                xj = "五星级"
        End Select
        Cells(i, "I") = xj
    i = i + 1
    Loop
End Sub

语法结构2
Do
    <循环体>
    [Exit Do]
    [循环体]
Loop  [While 逻辑表达式]
对应示例：

Sub xingji()
    Dim xj As String, i As Integer
    i = 2
    Do
        Select Case Cells(i, "H")
            Case Is < 85
                xj = "不评定"
            Case Is < 100
                xj = "一星级"
            Case Is < 115
                xj = "二星级"
            Case Is < 130
                xj = "三星级"
            Case Is < 150
                xj = "四星级"
            Case Else
                xj = "五星级"
        End Select
        Cells(i, "I") = xj
    i = i + 1
    Loop While Cells(i, "H") <> ""
End Sub

5、Do Until语句

　　do Until 后的逻辑表达式为False则执行循环体，否则退出循环，跟Do While相反。

语法结构1：
Do [Until 逻辑表达式]
    <循环体>
    [Exit Do]
    [循环体]
Loop
 对应示例： 

Sub xingji()
    Dim xj As String, i As Integer
    i = 2
    Do Until Cells(i, "H") = ""
        Select Case Cells(i, "H")
            Case Is < 85
                xj = "不评定"
            Case Is < 100
                xj = "一星级"
            Case Is < 115
                xj = "二星级"
            Case Is < 130
                xj = "三星级"
            Case Is < 150
                xj = "四星级"
            Case Else
                xj = "五星级"
        End Select
        Cells(i, "I") = xj
    i = i + 1
    Loop
End Sub

语法结构2：
Do
    <循环体>
    [Exit Do]
    [循环体]
Loop [Until 逻辑表达式]
 对应示例：

Sub xingji()
    Dim xj As String, i As Integer
    i = 2
    Do
        Select Case Cells(i, "H")
            Case Is < 85
                xj = "不评定"
            Case Is < 100
                xj = "一星级"
            Case Is < 115
                xj = "二星级"
            Case Is < 130
                xj = "三星级"
            Case Is < 150
                xj = "四星级"
            Case Else
                xj = "五星级"
        End Select
        Cells(i, "I") = xj
    i = i + 1
    Loop Until Cells(i, "H") = ""
End Sub

6、For Each…Next语句

当前活动工作簿中有许多工作表，但并不知道数量。如果要把所有工作表的名称按次序写入活动工作表的A列，For Each…Next是更适合的循环。

For Each 元素变量 In 集合名称或数组名称
    <语句块1>
    [Exit For]
    [语句块2]
Next [元素变量]
示例1：

Sub shtName()
    Dim sht As Worksheet, i As Integer
    i = 1
    For Each sht In Worksheets
        Cells(i, "A") = sht.Name
        i = i + 1
    Next sht
End Sub

示例2：

Sub shtName()
    Dim c As Range, i As Integer
    i = 1
    For Each c In Range("A1:A10")
        c.Value = i
        i = i + 1
    Next c
End Sub

7、GoTo语句

 “去到指定地点”，用来让程序转到另外一条语句去执行。

Sub he()
    Dim mysum As Long, i As String
    i = 1
x:    mysum = mysum + i
    i = i + 1
    If i <= 100 Then GoTo x
    MsgBox "1到100的自然数和是：" & mysun
    
End Sub

8、With语句

 当需要对相同的对象进行多次操作时，会编写一些重复代码

Sub FontSet()
    With Worksheets("sheet1").Range("A1").Font
        .Name = "仿宋"
        .Size = 12
        .Bold = True
        .ColorIndex = 3
    End With  '这里的With语句结束标志，不可缺少
End Sub