去年刚刚学习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