VBA笔记：基础语句结构

1.Select Case语句

用于多重判断语句，与 if...elseif 语句功能一样，且更有效（判断到符合逻辑后，就直接跳出循环，而不像if一样，得把所有循环走完~ 书里写的 ~）

Sub dengji()
    Dim cj As Variant
    cj = InputBox("输入考试成绩：", "成绩等级框", 0)
    Select Case cj
        Case 0 To 59
            MsgBox "等级: D"
        Case 60 To 69
            MsgBox "等级: C"
        Case 70 To 79
            MsgBox "等级: A"
        Case 80 To 100
            MsgBox "等级: S"
        Case Else
            MsgBox "输入错误！"
    End Select
End Sub

2.For Next语句

For循环，通常用来做一些批量处理工作，如下图，现在要统计每个成员的星级评定

image.png

星级评定标准如下

image.png

Sub xingji()
    Dim xj As String
    Dim i As Integer
    For i = 2 To 10 Step 1 '每次循环i增加的值，如果每次循环只+1，则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 '相当于i + 1 ，并进入下一次循环，i 可以被省略
End Sub

3.Do While语句

Do循环也可以实现上述for循环的效果，语句格式

'表达式1：条件判断在Do后边'
Do While条件表达式
    <循环体>
    Exit Do '可选语句，执行后跳出循环并执行loop后的语句'
    <循环体>
Loop

'表达式2：条件判断在Loop后边'
Do 
    <循环体>
    Exit Do '可选语句，执行后跳出循环并执行loop后的语句'
    <循环体>
Loop While条件表达式

案例

Sub xingji2()
    Dim xj As String, i As Integer
    i = 2
    Do While Cells(i, "H").Value <> ""
        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

4.Do Until语句

与 Do while 语句类似，语句格式

'表达式1：条件判断在Do后边'
Do Until 条件表达式
    <循环体>
    Exit Do '可选语句，执行后跳出循环并执行loop后的语句'
    <循环体>
Loop

'表达式2：条件判断在Loop后边'
Do 
    <循环体>
    Exit Do '可选语句，执行后跳出循环并执行loop后的语句'
    <循环体>
Loop Until 条件表达式

5.With语句

当需要对相同对象进行多次操作时，可以用到With语句

Sub FontSet()
    Worksheets("基础语句").Range("A2").Font.Name = "微软雅黑"
    Worksheets("基础语句").Range("A2").Font.Size = 12
    Worksheets("基础语句").Range("A2").Font.Bold = True
    Worksheets("基础语句").Range("A2").Font.ColorIndex = 3
End Sub
---------使用With语句前后对比--------------------------------
Sub FontSet2()
    With Worksheets("基础语句").Range("A2").Font
        .Name = "微软雅黑"
        .Size = 12
        .Bold = True
        .ColorIndex = 3
    End With
End Sub