※此文针对vba基础练习,且为excelhome教程之笔记,请选择食用
3.10.3 使用select case语句解决多选一问题:
Public Sub test()
Select Case Range("B2").Value
Case Is >= 90
Range("C2").Value = "优秀"
Case Is >= 80
Range("C2").Value = "良好"
Case Is >= 70
Range("C2").Value = "及格"
Case Else
Range("C2").Value = "不及格"
End Select
End Sub
建议将可能性最大的情况写在前,节省资源
练习题:
1.补全成绩评定等级
Public Sub dengji()
Dim cj As Variant
cj = InputBox("输入考试成绩:")
Select Case cj
Case 0 To 59
MsgBox "等级:D"
Case 60 To 69
MsgBox "等级:C"
Case 70 To 79
MsgBox "等级:B"
Case 80 To 89
MsgBox "等级:A"
Case Else
MsgBox "error"
End Select
End Sub
2.单位职工考核得分表
Public Sub KPI()
Dim xj As Variant
xj = InputBox("输入员工考核得分:")
Select Case xj
Case 0 To 85
MsgBox "不评级"
Case 85 To 99
MsgBox "一星级"
Case 100 To 114
MsgBox "二星级"
Case 115 To 129
MsgBox "三星级"
Case 130 To 149
MsgBox "四星级"
Case Else
MsgBox "五星级"
End Select
End Sub
3.10.4 用for。。next。。语句执行同一段代码
在活动工作表钱插入一张新工作表:
Public Sub shtadd()
Worksheets.Add
End Sub
需要插入5张工作表,就使用for next语句:
Public Sub shtaddx()
Dim i As Byte
For i = 1 To 5 Step 1
Worksheets.Add
Next i
End Sub
每一个for语句都必须用next结尾,for与next之间的语句称为循环体。
同时,初始值与结束值不一定需要初始值小于结束值,步长step可以为负整数,例如:
for i = 5 to 1 step -1
可以使用exit for终止for循环:
for <循环变量>=<初值>to<终值> [step 步长值]
<循环体>
[exit for]
[循环体]
next [循环变量]
利用循环为多个成绩评定等级:
Public Sub testdg()
Dim i As Byte
For i = 2 To 11 Step 1
Select Case Range("E" & i).Value
Case Is >= 90
Range("F" & i).Value = "优秀"
Case Is >= 80
Range("F" & i).Value = "良好"
Case Is >= 60
Range("F" & i).Value = "及格"
Case Else
Range("F" & i).Value = "不及格"
End Select
Next i
End Sub
改变引用区域的值,可以灵活运用在任何想引用的区域。
练习题:
1运行程序,将100以内的正奇数填充进A列单元格中
Public Sub jishu()
Dim xrow As Byte, i As Byte
xrow = 1
For i = 1 To 100 Step 2
Range("A" & xrow).Value = i
xrow = xrow + 1
Next
End Sub
2.找出100以内被3整除的数字填入B列单元格:
Public Sub three()
Dim xrow As Byte, i As Byte
xrow = 1
For i = 1 To 100 Step 1
If i Mod 3 = 0 Then
Range("B" & xrow).Value = i
xrow = xrow + 1
End If
Next
End Sub
3.10.5用for each。。next语句循环处理集合或数组中的成员
将工作表中所有的单元表名字写入单元格中:
Public Sub shtname()
Dim sht As Worksheet, i As Integer
i = 1
For Each sht In Worksheets
Range("A" & i) = sht.Name
i = i + 1
Next sht
End Sub
for each。。next 表示在这个语句之中的循环体执行次数取决于有几张工作表。for each。。next语句只能在一个集合中所有的对象或一个数组中所有元素中进行循环。
for each 变量 in 集合名称或数组名称
语句块1
[exit for]
[语句块2]
next [元素变量]
用for each。。next语句编写一个在A1:A100单元格中输入1~100自然数:
Public Sub yibai()
Dim c As Range, i As Integer
i = 1
For Each c In Range("A1:A100")
c.Value = i
i = i + 1
Next
End Sub
3.10.6用do语句按条件控制循环
想在工作表中插入5张新工作表,此为开头判断式:
Public Sub shtadd5()
Dim i As Byte
i = 1
Do While i <= 5
Worksheets.Add
i = i + 1
Loop
End Sub
每个do语句都必须以loop结尾,do与loop之间就是需要循环的循环体
也可以在句尾处设置条件,此为结尾判断式:
Public Sub shtadd6()
Dim i As Byte
i = 1
Do
Worksheets.Add
i = i + 1
Loop While i <= 5
End Sub
在循环体中设置退出循环的条件
Public Sub shtadd7()
Dim i As Byte
i = 1
Do
If i > 5 Then Exit Do
Worksheets.Add
i = i + 1
Loop
End Sub
使用do until语句执行重复的操作:与do while语句使用方式几乎相同,until指当判断条件为true时跳出,while指当判断条件为false时跳出。
使用go to语句,让程序转到另一条语句去执行
Public Sub sum_teat()
Dim mysum As Long, i As Integer
i = 1
x: mysum = mysum + 1
i = i + 1
If i <= 100 Then GoTo x
MsgBox "1到100的自然数之和是:" & mysum
End Sub
with语句:省略相同语句,简化程序
Public Sub fontset()
With Worksheets("sheet1").Range("A1:A9").Font
.Name = "仿宋"
.Size = 12
.Bold = True
.ColorIndex = 3
End With
End Sub