VBA自学日志

---

前言

VBA自学成柴的第三周

---

一、For each 循环

for each 循环一般用于循环一个集合，比如一个工作表，一个工作簿，或者是一个文件夹。

• 格式

for each a in 集合
	content
next

遍历工作簿

Sub test()
Dim a as WorkBook
For Each a in WorkBooks
	内容
Next
End Sub

遍历工作表

Sub test()
Dim a as WorkSheet
For Each a in WorkSheets
	内容
Next
End Sub

遍历单元格(CurrentRegion)

Sub test()
Dim a as Range
for Each a in Range("a1").CurrentRegion
	内容
Next
End Sub

遍历单元格(UsedRange)

Sub test()
Dim a as Range
for Each a in ActiveSheet.UsedRange
	内容
Next
End Sub

案例：将下列分数85分以上的分数变成红色

Sub test()
Dim a As Range
Dim lastrow As Long
'找到数据的边界
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
'通过&将列和行列连接
For Each a In Range("b2:b" & lastrow)
    If a.Value >= 85 Then
        a.Interior.ColorIndex = 3
    End If
Next a
End Sub

二、offset 偏移

以单元格为基准，进行偏移，返回单元格offset一共有两种偏移方式，我就只记了一种。

• 格式
  单元格.offset(行，列) 从0开始
• 符号
  上负，下正，左负，右正

实用案例：给95分及以上的同学建一个新的工作表，以他们的名字命名。

Sub test()
Dim a As Range
Dim lastrow As Byte
'找到数据边界最后一行
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
'循环整个列，用&将列和行连接起来
For Each a In Range(Sheet3.[b35], "b" & lastrow)
   If a.Value >= 95 Then
        '这里使用偏移，将b列左边的a列（姓名列）作为新表的名字
        newSheetName = a.Offset(0, -1).Value
        ' 创建新的工作表
        Set NewSheet = Sheets.Add(After:=Sheets(Sheets.Count))
        '将刚才定义的名字赋值给新表
        NewSheet.Name = newSheetName
        '将符合条件的单元格内容复制到新工作表，这里因为是希望将
        '整个表内容复制到新表中，所以这里使用偏移定位到第一列，然
        '后使用resize重构这个数据集，得到后面的数据。resize是后面
        '的内容，我直接提前用了。
        a.Offset(0, -1).Resize(1, 2).Copy NewSheet.Range("A1")
   End If
Next a
End Sub

虽然代码有注释，但是我还想总结一下：
1.定位边界
2.循环数据集
3.利用offset偏移得到第一列的值并赋值
4.赋值给新列名称
5.利用offset+resize+copy将完整数据复制到新表中

三、Resize 属性

用于调整区域大小，返回一个range对象，该对象表示重新定义的区域。

• 格式

单元格.resize(新区域行数，新区域列数) 从1开始

案例：将分数大于95分的同学全部标成红色

Sub test()
Dim a As Range
Dim lastrow As Byte
lastrow = Cells(Rows.Count, "b").End(xlUp).Row
For Each a In Range("b35:b" & lastrow)
    If a.Value >= 85 Then
    	'用offset偏移定位到第一列，然后resize重构到1行2列,返回所有数据
    	'然后将所有数据变成红色
        a.Offset(0, -1).Resize(1, 2).Interior.ColorIndex = 3
    End If
Next a
End Sub

四、Exit 语句

exit用于提前结束代码，但是不能取代end，end永远是老大哥在后面。

• 格式

exit do   '只能写在do循环里面
exit for  '只能写在for循环里面
exit sub  '只能写在sub循环里面

案例，抽取前三个姓李的同学

Sub test()
Dim a, b, lastrow As Byte
lastrow = Cells(Rows.Count, "a").End(xlUp).Row
For a = 35 To lastrow
    If Left(Cells(a, "A"), 1) = "李" Then
        Cells(a, "B").Offset(0, -1).Resize(1, 2).Copy _
        Cells(Rows.Count, "f").End(xlUp).Offset(1, 0)
        b = b + 1
        If b = 3 Then
        Exit Sub
        End If
    End If
Next a
End Sub

总结:
1.用left找到左边第一位是“李”的同学
2.offset+resize+copy 提取所有数据并赋值给f列
3.定位f列边界，并且用offset向下偏移一行，因为第一行是表头

五、DO…LOOP语句

DO LOOP无限循环语句,DO LOOP语句需要结合exit提前结束语句，不然就会陷入无限循环当中，直到机器死机。

• 格式

DO
	循环的内容
LOOP

在循环到5的时候结束语句

Sub test()
Dim a as Byte
Do 
n=n+1
If n = 5 Then Exit Do
LOOP
End Sub

六、一些错误代码总结

如果想忽略错误,继续运行在代码前面加

On Error Resume Next

一些常见的错误代码：

代码	含义
9	数组下标超出范围
13	类型不匹配
91	对象变量未设置
428	对象不支持此属性或方法
445	对象不支持此操作
53	文件未找到
76	路径未找到
91	溢出
0	代码无错误，当然这肯定不会弹出来

七、GOTO语句

跳转语句

• 格式

GOTO 66
其他内容
66:

在代码中加入GOTO，会在你类型出错的时候提醒你：

Sub test()
...
'因为这里运行两句话所以要在中间打上冒号。
If Err.Number <> 0 Then MsgBox "不好意思，您输入的格式有误"  : GoTo 66 
... `如果出现错就不会执行中间的代码，直接跳转到66后
66:
Err.Clear '记得清除报错信息
End Sub

八、do while 和 do until

Do While loop 和 Do until loop 是有前提条件结束的Do循环语句。

• 格式

Do While 条件 （条件成立才开始循环）
循环内容
LOOP
-------------------------------
Do Until 条件（条件成立才退出循环）
循环内容
LOOP

案例，抽取前三个姓李的同学

Do While:

Sub test()
Dim a As Integer
Dim b, c As Byte
a = 35
Do While b <> 3
If Left(Cells(a, "A"), 1) = "李" Then
    Cells(a, "b").Offset(0, -1).Resize(1, 2).Copy _
    Cells(Rows.Count, "f").End(xlUp).Offset(1, 0)
    b = b + 1
End If
a = a + 1
Loop
End Sub

Do Until:

Sub test()
Dim a As Integer
Dim b, c As Byte
a = 35
Do Until b = 3
If Left(Cells(a, "A"), 1) = "李" Then
    Cells(a, "b").Offset(0, -1).Resize(1, 2).Copy _
    Cells(Rows.Count, "f").End(xlUp).Offset(1, 0)
    b = b + 1
End If
a = a + 1
Loop
End Sub

九、如何在VBA内使用Excel工作表函数

• 格式

application.WorksheetFunction.函数名

例如求所有一年级同学的平均成绩

Sub test()
单元格 = application.WorksheetFunction.averageif(年级列,"一年级",成绩列)
End Sub

十、VBA使用随机数

Rnd 返回一个小于1但大于或等于0的值

• 格式

Int((最大值 - 最小值 + 1) * Rnd + 最小值)

十一、排序

格式:

Sort(key1,order1,key2,type,order2,key3,order3.header,ordercustom,
matchcase,orientation,sortmethod,dataoption1,dataoption2,dataoption3)

参数意义：

• key1,key2,key3,排序关键列，至少使用一个key，最多使用三重排序

• order1，order2，order3排序模式，默认升序

  • order1:=xlAscending 代表key1升序，简写 order 1:=1
  • order2:=xlDescending 代表key2降序，简写order2:=2

• type指定要排序的元素，排序数据透视表的时候使用

• header排序区域是否有表头

  • header:=xlGuess，你猜，让软件辨认，简写 header:=0
  • header:xlYes 有表头，简写 header:=1（第一行不参与排序）
  • header:xlNo 没有表头，简写 header:=2（第一行参与排序）
    案例：对班级进行升序，对成绩进行降序
    

Sub test()
Dim a As Range
Set a = Range("a58").CurrentRegion
'注意如果type没有使用的话要把type空出来
a.Sort Range("a58"), 1, Range("c58"), , 2, Header:=1
End Sub

总结

• 目前所学的VBA 循环类型

循环类型	使用方法	适用场景
For	??????????????????for 变量 = x to y ??????????????????????需要的操作 next	在已知循环次数的情况下，适用于明确指定开始、结束和步长的循环
For Each	?????????????????????for each a in 集合 ?????????????????content next	用于遍历集合（如数组、集合、范围等），适用于不知道循环次数但需要遍历集合的情况
Do Loop	Do ??????????????????????循环的内容 ?????Loop	适用于不知道循环次数的情况
Do While	???????????????????????????????Do While 条件 （条件成立才开始循环） ????????????????循环内容 Loop	在循环开始之前检查条件，只有在条件为 True 时才执行循环
Do Until	??????????????????????????????Do Until 条件（条件成立才退出循环） ??????????????????循环内容 Loop	在循环开始之前检查条件，只有在条件为 False 时才执行循环

就这样。结束。