VBA 入门笔记

去年刚刚学习VBA，总结一下入门知识，包括属性定义，选择判断循环等等。

Sub aa()

MsgBox _
"学习 VBA ing"
End Sub


Sub 属性赋值()
Sheet2.Name = "属性赋值"
Sheet2.Range("b2") = "This is B2 cell"
End Sub


Sub 选择()
Range("a1:a10").Select


End Sub


Sub 复制()
Sheet1.Range("a5:a10") = "复制"
Sheet1.Range("a5:a10").Copy Sheet2.Range("b5:b10")


End Sub


Sub 删除()
Sheets(3).Delete
End Sub


'常量
Sub 常量()
Const s = 3.1415926
End Sub


'变量
Sub 变量()
Dim a As Integer
a = 2
a = 5
End Sub
Sub 常量与变量的应用()
Const pi = 3.14
Dim a As Integer
Dim a As Integer
a = 200
Debug.Print (pi * a)
End Sub


'VBA 常见的数据类型
'Integer    %   整型
'Single     !   单精度
'Double     #   双精度
'long       &   长整型
'string     $   字符型
'Currency   @   货币型
Sub 数据类型()
Dim aa As Integer
Dim bb%
Dim a As Integer, b As Single, c As String
Dim D%, e!, f$
End Sub


Sub If判断单条件()
Dim a%, b%, c%
a = 2
b = 2
c = 2
If a = b Then MsgBox ("Equal")


If b = c Then
    MsgBox ("Equal again")
End If


If a = 3 Then
    MsgBox ("a equals c")
Else
    MsgBox ("Not equal")
End If
    
End Sub


Sub If判断多条件()
Dim a%, b$
a = Sheet1.Range("b12")


If a >= 90 Then
    b = "优秀"
ElseIf a >= 80 Then
    b = "良好"
ElseIf a >= 60 Then
    b = "合格"
Else
    b = "不及格"
End If
Sheet1.Range("b13").Value = b


Cells(14, 2) = IIf(Cells(12, 2) >= 50, "可", "否")


End Sub


Sub select单条件判断()
i = -1
Select Case i
Case Is > 0
    MsgBox ("正数")
Case Else
    MsgBox ("负数")
End Select
End Sub


Sub select多条件判断()
Select Case Sheet1.[b16].Value
Case "A"
    Sheet1.[b17].Value = "A is good"
Case "B"
    Sheet1.[b17].Value = "B is okay"
Case "O"
    Sheet1.[b17].Value = "O is bad"
Case Else
    Sheet1.[b17].Value = "No this blood, fuck u"
End Select
End Sub


Sub 循环基本实例()
Dim a%
Do
a = a + 1
If a > 10 Then
    MsgBox "a=" & a & ",终于大于10了"
    Exit Do
End If
Loop
End Sub


Sub 大于90打勾小于打叉()
Dim a%
a = 20
Do
    a = a + 1
    If a > 26 Then
        Exit Do
    Else
        If Cells(a, 2) >= 90 Then Cells(a, 3) = "√"
    End If
Loop
a = 20
Do Until Cells(a, 2) = ""
    If Cells(a, 2) < 90 Then Cells(a, 3) = "×"
    a = a + 1
Loop
End Sub




Sub 隔行填色()
Dim rs%
rs = 2
Do Until Sheet1.Range("a" & rs) = ""
    Sheet1.Range("a" & rs & ":" & "c" & rs).Interior.ColorIndex = 7
    rs = rs + 2
Loop
End Sub


Sub 循环条件放最后()
Dim pwd$, i!
Do
    i = i + 1
    If i > 3 Then
        MsgBox ("错误超过3次，请联系管理员")
        Exit Do
    End If
    pwd = InputBox("请输入密码")
    If pwd = "123" Then MsgBox ("正确的结果：123")
Loop Until pwd = "123"


End Sub


'有A1的单元格设置颜色为红色


Sub foreachnext循环1()
Dim rng As Range, n!
For Each rng In Sheet1.Range("a2:a20")
    If rng = "A1" Then rng.Interior.ColorIndex = 3
    Next
End Sub




'fornext循环
Sub fornext循环()
Dim rng!
For rng = 29 To 32 Step 1
    Sheet1.Cells(rng, 3) = Sheet1.Cells(rng, 1) * Sheet1.Cells(rng, 2)
    Next
End Sub
'fornext小结
Sub fornextexitfor()
Dim n!, m!
For i = 17 To 22
    If Cells(i, 1) = "A3" Then n = n + 1
Next i
For j = 17 To 22
    If Cells(j, 1) = "A1" Then m = m + 1: Exit For
Next j
MsgBox n & "个A1 & " & m & "个A3"
End Sub


'debug显示变量
Sub 属性()
    Debug.Print Sheet1.Name
    Debug.Print Sheet1.Range("a2").Value

End Sub