​​VBA基础：VBA基本语句结构！

转自：michaelxwang

​

​选择判断

选择判断（If...Then）

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

​

​选择判断（Select...Case）

​

​选择判断（Select...Case）

Sub dengji()
'   定义一个Variant型变量cj
    Dim cj As Variant
    cj = InputBox(" 输入考试成绩：")
'   Select语句开始
    Select Case cj
    Case 0 To 59
        MsgBox " 等级：D "
    Case 60 To 69
        MsgBox " 等级：C "
    Case 70 To 89
        MsgBox " 等级：B "
    Case 90 To 100
        MsgBox " 等级：A "
    Case Else
        MsgBox " 输入错误！"
'   Select 语句结束
    End Select
End Sub

循环

循环(For...Next)

For   <循环变量>=<初值>To<终值>[Step 步长值]
      <循环体>
      [Exit For]
      [循环体]
Next [循环变量]

• 步长是可选参数，若省略，默认步长为1.
• 可以在循环体中任意处加一句或多句 Exit For，当遇到这个语句，退出For 循环，执行Next后的语句。
• Next 更改循环变量的值，执行下一个循环变量。变量名称可以省略，直接写成Next。

例：根据A列成绩在B列更新其对应等级

Sub dengji()
    Dim xj As String, i As Integer
    For i = 2 To 50 Step 1
        Select Case Cells(i, "A")
            Case 0 To 59
                xj = " D "
            Case 60 To 69
                xj = " C "
            Case 70 To 89
                xj = " B "
            Case 90 To 100
                xj = " A "
            Case Else
                xj = " 输入错误！"
        End Select
        Cells(i, "B") = xj
    Next i
End Sub

循环(Do...While)

Do   [While 逻辑表达式]
     <循环体>
     [Exit Do]
     [循环体]
Loop

• 若 逻辑表达式 的值为TRUE时，执行循环体，否则执行Loop后的语句，可省略，如果省略应在程序中使用Exit Do语句，让程序满足一定的条件后退出循环。
• [Exit Do] 可选语句，执行Exit Do 语句后，将跳出循环，执行Loop后的语句。
• Loop 标志Do While 语句结束，返回Do 语句处，再次判断循环条件。

例：根据A列成绩在B列更新其对应等级

Sub dengji()
    Dim xj As String, i As Integer
    i = 2
    Do While Cells(i, "A") <> ""
        Select Case Cells(i, "A")
            Case 0 To 59
                xj = " D "
            Case 60 To 69
                xj = " C "
            Case 70 To 89
                xj = " B "
            Case 90 To 100
                xj = " A "
            Case Else
                xj = " 输入错误！"
        End Select
        Cells(i, "B") = xj
    i = i + 1
    Loop
End Sub

也可以在结尾处判断循环条件，语句为：

Do
<循环体>
[Exit Do]
[循环体]
Loop [While 逻辑表达式]

注意： 这种循环格式在循环条件一开始就为false的时候要多执行一次循环体
例：根据A列成绩在B列更新其对应等级

Sub dengji()
    Dim xj As String, i As Integer
    i = 2
    Do
        Select Case Cells(i, "A")
            Case 0 To 59
                xj = " D "
            Case 60 To 69
                xj = " C "
            Case 70 To 89
                xj = " B "
            Case 90 To 100
                xj = " A "
            Case Else
                xj = " 输入错误！"
        End Select
        Cells(i, "B") = xj
    i = i + 1
    Loop While Cells(i, "A") <> ""
End Sub

循环(Do...Until)

Do [Until 逻辑表达式]
      <循环体>
     [Exit Do]
     [循环体]
Loop

结尾判断式

Do 
      <循环体>
     [Exit Do]
     [循环体]
Loop [Until 逻辑表达式]

• 若 逻辑表达式 的值为False时，执行循环体，否则退出循环。
  例：根据A列成绩在B列更新其对应等级

Sub dengji()
    Dim xj As String, i As Integer
    i = 2
    Do
        Select Case Cells(i, "A")
            Case 0 To 59
                xj = " D "
            Case 60 To 69
                xj = " C "
            Case 70 To 89
                xj = " B "
            Case 90 To 100
                xj = " A "
            Case Else
                xj = " 输入错误！"
        End Select
        Cells(i, "B") = xj
    i = i + 1
    Loop Until Cells(i, "A")  = ""
End Sub

循环(For Each...Next)

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

• 循环会默认遍历 集合或数组中所有的元素变量 。

例：获取工作簿的各个工作表名

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

学习资料见知识星球。

以上就是今天要分享的技巧，你学会了吗？若有什么问题，欢迎在下方留言。

快来试试吧，小琥 my21ke007。获取 1000个免费 Excel模板福利​​​​！

更多技巧， www.excelbook.cn

欢迎 加入 零售创新 知识星球，知识星球主要以数据分析、报告分享、数据工具讨论为主；