Office——Excel-VBA学习——进阶

1. 输入和绝对值
Sub QingwuAbs()
Dim a, b As Integer
a = InputBox("请输入一个负数：", "提示")
b = Abs(a)
MsgBox "你输入数的绝对值为：" & b
End Sub

2. IF结构
Sub TestIf()
[A1] = 35
If [A1] > 30 Then
    MsgBox ">30"
Else
    MsgBox "<=30"
End If
End Sub

3. Select结构
Sub TestSelect()
[A2] = 40
Select Case [A2].Value
    Case Is < 30
        MsgBox "<30"
    Case Is < 60
        MsgBox "<60"
    Case Is < 80
        MsgBox "<80"
    Case Else
        MsgBox "优秀"
End Select
End Sub

4. 循环For
Sub TestLoop()
    Dim Lsum As Long, i As Long
    For i = 1 To 100 Step 1 'Step 可省略
        Lsum = Lsum + i
    Next i 'Next 亦可
    MsgBox "1到1000的自然数和为：" & Lsum
End Sub

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

6. Do While
Sub TestDoWhile()
    Dim Lsum As Long, i As Long
    i = 1
    Do While i <= 1000
        Lsum = Lsum + i
        i = i + 1
    Loop
    MsgBox Lsum
End Sub

7. Do Until
Sub TestDoUntil()
    Dim Lsum As Long, i As Long
    i = 1
    Do
        Lsum = Lsum + i
        i = i + 1
    Loop Until i > 1000
    MsgBox Lsum
End Sub