4/23/2020 - 10:45 AM

VBA Document

ExcelHomeLearn.md

Rendered
Source

Excel VBA

第二章 VBA编辑器

右击工作表（sheet）有查看代码选项，可打开VB编辑器；
在Excel中，一个工作簿就是一个工程，工程名为VBAProject(<工作簿名称>)，一个工程最对可以包含四类对象:Excel对象（包括Sheet对象和ThisWorkbook对象）、窗体对象、模块对象和类模块对象。而我们书写的代码通常保存在模块对象中；

第三章 VBA语法

在Excel中，所有保存在单元格中的信息都可以成为数据；
在Excel的世界里，数据只有文本、数值、日期值、逻辑值、错误值这5种，但VBA不同；
VBA里的变量，就是给数据预留内存空间，它就像我们外出旅游前，提前预定的酒店房间一样；
给数据类型的变量赋值：Let InCount = 100，这里的Let可省略，但可提高可读性；给对象类型的数据赋值：Set sht = Activesheet，注意用的是Set；
提前定义变量为合适的类型，是一个很好的习惯，可使用Option Explicit强制所有变量必须事先声明类型（否则VB允许你使用未声明的变量）；
VB的索引默认从0开始,使用OPTION BASE 1可将起始索引设为1;；
假设一维数组为表格的一行数据，而二维数组为一张表格，三维数组为多张数据表组成的书，四维数组为一箱书，5维数组维一堆装着书的箱子，则arr(5,4,3,2,1)的意思为：第5堆里的第4本书中的第3页表格中的第2行的第1个数据，可见==括号中总是表示高维度的索引在前，最低维度的索引号在后`;
把数组内容赋值给单元格区域时，数组的行数列数必须和单元格的行数列数一一匹配；
函数、属性和方法的获取
- VBA内置的函数可通过输入VAB.后VB编辑器自动弹出，如果不小心关闭了弹出的函数列表，可通过快捷键Command+J再次弹出；
- 对象的属性和方法也是该快捷键弹出，弹出的属性/方法列表中，带绿色图标的是方法；
- 将光标置于某个属性之间，按下F1即可跳出在线帮助；
VBA中的函数、关键字、对象以及属性等等都是首字母大写；
VBA的比较运算符：<>表示不等于，一个等号=就表示等于，不需要两个；
条件语句如果不换行输入，则不需要最后的End If;
For Each ... Next语句可遍历集合（比如工作簿集合、工作表集合、文件夹里的文件集合）和数组元素。当遍历数组元素时，仅能访问数组元素，不能修改数组元素；对于对象数组，你不能更改对象本身，但可修改对象的属性值；
调用一个过程ShtAdd
- 使用Call ShtAdd，建议使用这个，可读性好；
- 省略Call，直接用ShtAdd，个人不建议；
- Application.Run "ShtAdd";
VBA中的引用传递操作同一个变量，而值传递相当于把变量拷贝一份操作的时变量的副本；
VBA中自定义的函数指的是类似于VBA内置函数的函数，即输入VBA.后VB编辑器可弹出的函数。自定义的函数既可在VB编辑器中使用，也可在工作表中像VLOOKUP一样使用；
VBA的函数名是一个特殊的变量，它除了和普通变量一样外，可是函数返回的唯一变量，因此函数的结果都只能借助该变量返回；
如果想让工作表重新计算后，自定义的函数也能随之重新计算，就应该自定义函数为易失性函数：在函数开头添加代码：Application.Volatile True;
- 易失性函数：工作表任意单元格重新计算时，易失性函数都重新计算；
- 非易失函数：只有函数的参数发生改变时才重新计算；
- 除非必要，否则不建议将自定义函数定义为易失性函数；
- 注意理解什么是单元格重新计算，诸如更改单元格背景色并不属于单元格重新计算；
代码可读性：
- 合理缩进；
- 长代码拆分多行：_，注意_前后需有空格；
- 多行短代码合并为一行，用 : 连接短代码；
- 注释：英文单引号'或关键字Rem表示注释；

第四章 Excel对象

Range对象代表Excel中的单元格，可以是单个单元格，也可以是单元格区域；
执行VBA程序时，Excel弹出的警告信息（比如删除工作表时会提示是否确定删除）会中断程序运行，这不是我们期望的结果。此时，可使用Application.DisplaAlerts=False禁用警告，但记得在程序最后恢复告警功能；
在VBA中是不能直接使用工作表函数的（VBA内置函数的函数名首字母大写，而工作表函数的函数名全部大写，如VLOOKUP,SUMIF等）。你需要通过Application.WorksheetFuntion属性调用工作表函数。此外，该属性并不能调用所有工作表函数。
借助宏录制功能找到所需修改内容的属性名；
打开工作表集合Worksheets某个工作簿：
- 通过索引号打开：Excel默认按照工作簿打开的顺序从1开始编号，因此Workbooks.Item(1)打开第一个工作簿，Workbooks(1)本质上等价于Workbooks.Item(1);
- 通过工作簿名称打开：Workbooks("book1")，需要注意以下几点：
  - 如果是新建的工作簿，在不保存（即该文件不存在）的情况下，引用时工作簿的名称不能加扩展名。
  - 如果是已经存在的文件，当系统设置不显示文件的扩展名时，引用时的工作簿名称可以使用扩展名，也可以不使用。
  - 如果是已经存在的文件，当系统设置显示文件的扩展名时，使用工作簿名称引用工作簿时，工作簿的名称必须带上扩展名。
  - 所以，对于一个已经存在的文件，为避免代码出错，使用带扩展名的名称去引用它是一种更准确、规范的做法。
诸如Workbooks("Book1").Close savechanges:=true本质上类似于执行cmd命令：Workbooks("Book1").Close是命令名，而，savechanges:=true是命令的参数；一个命令可有多个参数，参数列表用逗号,分隔。输入命令后，按下空格，VBE会提示命令的所有可用参数。
如果执行的命令带参数且而你想同时设置执行命令得到的新对象的参数，则你可这样设置：Worksheets.Add(before:=Worksheets(1)).Name = "工作表“，也就是命令的参数可以用空格分隔，也可以放入括号中；
引用工作表时，尽量使用工作表的代码名，参考书籍162页;
'Worksheets.Add默认参活动工作表前插入一张工作表，它实际上是默认命令Worksheets.Add before:=Worksheets(1)`的简写。其他不带参数的命令本质上也是默认命令的缩写；
ThisWorkbook是对代码所在工作簿的引用，而ActiveWorkbook是对活动工作簿的引用；
工作表的Select和Activate方法都可激活工作表，在绝大多数情况下，它俩的作用相同，但有两个区别：
- 区别一：当工作表隐藏时，调用Worksheet对象的Select方法会出错，但调用Activate方法却不会出错。也就是说，不能使用Select方法去激活或选中已经隐藏的工作表。
- 区别二：用Activate方法不能同时选中多张工作表，但使用用Select方法可以同时选中未隐藏的多张工作表。
Range对象由工作表中的单元格或单元格区域组成。Cells是工作表所有单元格组成的集合；不管是Cells集合、Rows集合、Columns集合，甚至Range属性自身，它们都返回一个或多个单元格，都属于Range对象。Range对象有点递归定义的意味了：一定范围的单元格整体是Range对象，每个单元格，区域中的每一行都是Range对象，也就是Range对象由Range对象组成。
使用Range属性可以引用单个单元格、多个单元格组成的一个区域、多个区域组成的合并区域、多个区域的公共区域等，使用比较广泛。而使用Cells属性只能引用单个的单元格，不能引用多个单元格组成的单元格区域，只适用于某些问题情境。
选中单元格可用Activate和Select，它们的区别如下：
- 使用Range属性可以引用单个单元格、多个单元格组成的一个区域、多个区域组成的合并区域、多个区域的公共区域等，使用比较广泛。
- 使用Cells属性只能引用单个的单元格，不能引用多个单元格组成的单元格区域，只适用于某些问题情境。
使用Select方法和Activate方法执行选中或激活单元格的操作的区别：
- 选中单元格区域后，再使用Activate方法激活该区域里的一个单元格，该区域依然呈选中状态，只改变活动单元格为激活的单元格。如果使用Select方法选中区域里的一个单元格，则只有用Select方法选中选择的单元格呈选中状态。

清除单元格的内容或格式：

Range("B2").Clear
Range("B2").ClearComments
Range("B2").ClearContents
Range("B2").ClearFormats
Range("B2").ClearHyperlinks

无论复制的区域包含多少个单元格，在设置目标区域时，都可以只指定一个单元格作为目标区域的左上角的单元格即可；

格式化粘贴：

Sub 复制数值()
    Range("A1:D10").Copy
    Range("F1:I10").PasteSpecial Paste:=xlPasteValues
EndSub

Sub 复制数值()
    Range("F1:I10").Value = Range("A1:D10").Value
EndSub

VBA调试与优化

Kill ThisWorkbook.FullName 试图删除代码所在的工作簿，而代码所在的工作簿是已打开的工作簿，删除正在打开的文件这个操作是不可能完成的，故会报错；
中断程序：按下Esc或者按下Ctrl+Break组合键终止正在运行的程序；
中断模式与调试。当程序运行中断时，VBE进入处于中断模式，可进行调试：
- 程序运行错误时自动中断；
- 设置断点：
  - 调试下拉菜单选中切换断点命令（快捷方式F9）。
  - 点击代码所在行的边界条设置断点；
  - 关键字（或者说命令)Stop起着设置断点的作用；
- 中断模式下：
  - 将鼠标放到变量上，VBE会直接显示变量的值；
  - 打开本地窗口可看到各个变量的值和类型；
- 监视窗口，选中某个变量后，选择 调试 -> 快速监视(shift+F9)命令，可为该变量设置探针;
Debug.Print后代码的执行结果会输出在立即窗口中，可在代码出错的可疑代码前添加Debug.Print查看输出；
处理运行时错误：

    ' 如果运行错误则转到特定标签
    On Error Goto a           '如果发生错误，则转到标签a的语句继续执行
    Worksheets("abs").Select      '选中名称为 abc 的工作表
    a:  MsgBox "运行中发生了错误" '显示对话框

    ' 如果程序发生错误，则忽略错误的代码跳到下一行继续执行
    On Error Resume Next

    ' 关闭错误捕捉的示例
    ' 运行时若发生错误则不报错中断，跳转到标签a继续执行
    On Error Goto a
    Worksheets("abs").Select
    '关闭错误捕捉，在该语句后发生错误，即使有 On Error Goto a,程序依然会报错中断
    On Error Goto 0
    ' 显示对话框
    a:  MsgBox "运行中发生了错误"

编程好习惯

合理使用变量，尽量少占用内存：
- 在满足要求的前提下，优先定义占用内存少的变量类型；
- 尽量避免使用Variant类型的变量；
- 如果变量只在一个过程中使用，则不要将它声明为公共变量；若变量不再使用，则应释放掉：用Nothing释放掉对象：Set rng = Nothing

不要用长代码多次重复引用相同的对象：使用with和将相同的部分定义为变量；

' 使用 With 简化对象的引用，With后的的内容类似于前缀
With ThisWorkbook.Worksheets(1).Range("A1")
    .Clear  ' .clear 拼接到上面 With 后的内容
    With .Font  ' With 的嵌套，.Font 为它对应的 With 结构中所有属性的前缀
        ' .Name 先先以 .Font 为前缀组成 .Font.Name，再以第一个 With 后的内容为前缀
        .Name = "宋体"  
        .Size = 16
    End With
End With

' 使用变量代替长对象，简化引用
Dim rng As Range
Set rng = ThisWorkbook.Worksheets(1).Range("A1")
rng.Clear
rng.Value = "Excel Home"

' 变量和 With  搭配
Dim rng As Range
Set rng = ThisWorkbook.Worksheets(1).Range("A1")
With rng
    .Clear
    .Value = "Excel Home"
End With

尽量使用（内置）函数完成计算；
不要让代码执行多余的操作：宏录制得到的代码大多包含多余的代码；
合理使用数组：
- 先将数据存储到数组中，再一次性写入单元格中；
- 其他……
如果不需要和程序互动，就关闭屏幕更新；

变量定义

    Dim txt As String   '字符串变量用 txt 表示，或作为前缀
    Dim IntCount As Integer '计数器变量，带上数据类型 Int
    Dim sht As Worksheet    ' 工作表变量用 sht 表示，或做前缀
    Dim arr(1 to 100) As Byte ' 数组用 arr 表示，或做前缀
    Dim arr(1 to 100, 1 to 50) ' 声明多维数组

语法定义	说明
`Public / Dim myArr(1 To 100) As Integer`	通过起始和终止索引号定义数组的大小
`Dim myArr (99) As Integer`	如果使用一个自然数确定数组的大小，则默认起始索引号位`0`， `(99)` 等价于 `(0 To 99)`

缩写的定义

术语	释义
VBE	Visual Basic Editor，即VB编辑器

快捷键

快捷键	作用
`alt+F11`	打开VB编辑器

VBA CodingFaultCollect.md

Rendered
Source

VBA编程错误收集

Dir函数接受的路径参数中最后的\一定不要漏，很多函数返回的目录路径是没有这个\的，要记得加上去，如C:\Users\Cott\Desktop作为Dir的参数就会出问题，必需是C:\Users\Cott\Desktop\才行；

Cacher is the code snippet organizer for pro developers

We empower you and your team to get more done, faster

VBA Document

Excel VBA

第二章 VBA编辑器

第三章 VBA语法

第四章 Excel对象

VBA调试与优化

编程好习惯

变量定义

缩写的定义

快捷键

VBA编程错误收集