VBA之遍历代码

通过使用条件语句和循环语句 (也称为控制结构), 可以编写做出决策和重复操作的 Visual Basic 代码。 另一个有用的控制结构是With 语句, 它允许您运行一系列语句, 而无需重新限定对象。

条件语句

  • If…Then…Else：当条件为 True 或 False 时执行分支语句
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    Function Bonus(performance, salary) 
     If performance = 1 Then 
     Bonus = salary * 0.1 
     ElseIf performance = 2 Then 
     Bonus = salary * 0.09 
     ElseIf performance = 3 Then 
     Bonus = salary * 0.07 
     Else 
     Bonus = 0 
     End If 
    End Function
  • Select Case：从一组条件中选择一个分支
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    Function Bonus(performance, salary) 
      Select Case performance 
        Case 1 
          Bonus = salary * 0.1 
        Case 2, 3 
          Bonus = salary * 0.09 
        Case 4 To 6 
          Bonus = salary * 0.07 
        Case Is > 8 
          Bonus = 100 
        Case Else 
          Bonus = 0 
      End Select 
    End Function

循环重复代码

  • Do…Loop：当条件为 True 时开始循环或停止循环
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    Sub ExitExample() 
        counter = 0 
        myNum = 9 
        Do Until myNum = 10 
            myNum = myNum - 1 
            counter = counter + 1 
            If myNum < 10 Then Exit Do 
        Loop 
        MsgBox "The loop made " & counter & " repetitions." 
    End Sub
  • For…Next：使用计数器按指定次数运行语句
    1
    2
    3
    4
    5
    6
    Sub TwosTotal() 
        For j = 2 To 10 Step 2 
            total = total + j 
        Next j 
        MsgBox "The total is " & total 
    End Sub
  • For Each…Next：对集合中的每个项目重复运行一组语句
    1
    2
    3
    4
    Dim TestArray(10) As Integer, I As Variant 
    For Each I In TestArray 
     TestArray(I) = I 
    Next I

    对同一对象运行多个语句

  • With：对同一对象运行一系列语句

Exit 语句

退出 Do…Loop 块、 For…Next 、 Function 、 Sub 或 Property 代码

  • Exit Do
  • Exit For
  • Exit Function
  • Exit Property
  • Exit Sub