本文记录笔者在开发 VBA 程序中收集到的一些零零碎碎的代码片段以及一些吐槽,另外笔者的 vba 没有系统学习过，如有疏漏欢迎斧正。
VBA 中 Sub 一旦带参数，默认的宏加载器中找不到它了。
换言之：如果想要从默认宏加载器中运行 Sub 一定不能带参数
换言之：入口函数不能带参数（笔者总结）VBA 中 Sub 过程没有返回值，而 Function 过程可以有返回值
VBA 中 引用类型赋值必须要使用 Set 关键字，值类型则不能使用 Set
-
VBA 中 逻辑中的变量可以不需要声明随处写随时用
但是不使用 Dim 声明一下，智能感知提示（ctrl+j）中就不会出现哦。
在 VBA 文件顶部写上Option Explicit，IDE就会温馨的提醒你忘了声明变量了
胆敢不写，就是弹窗警告
- VBA 中 Function 怎么获取返回值，这点很意外，那就是调用时要传参的必须补全小括号
Set returnvalue = SomeFunction() '不带参数的也可不加括号'
Set returnvalue = SomeOtherFunction(someparameter) '带参数的就必须带括号，不带括号就代表你不要返回值'
之所以要强调小括号，是因为如果 Function 有返回值的情况下，你加了小括号却不声明个变量来接受它，就会报错：
而解决方案也很简单，那就是非要加括号，那你就接受返回值呀：
- VBA 中 For Each 居然可以一边遍历一边做删除操作，C# foreach语法表示想都不敢想：
'VBA 中如何遍历文件夹内的文件并依次删除文件，如果删除失败，会报错要求重试'
Sub DeleteFiles()
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
Set distFolder = fs.GetFolder(你的路径)
On Error GoTo DeletFileError
For Each fbx In distFolder.Files
fs.DeleteFile fbx
Next
On Error GoTo 0
Exit Sub
DeletFileError:
Dim tempFilePath As String: tempFilePath = fbx.path
MsgBox Prompt:=fbx.Name & " 删除失败,文件被占用，请解除占用后重试！", Title:="请重试"
If fs.FileExists(tempFilePath) Then Resume Else Resume Next '如果文件存在则尝试继续删除它否则处理下一个文件，避免用户此时手动删除导致的 File Not Found 报错'
End Sub
// 这个没上面用户体验好，遇到报错不会弹出自定义提示。
//https://zhidao.baidu.com/question/1574800320302008660.html
Sub Test()
Dim fs As Object
Set fs = CreateObject("scripting.filesystemobject")
fs.deletefile "D:\Test\temp\*.*"
fs.deleteFolder "D:\Test\temp\*.*"
End Sub
测试中发现下面无法实现依次遍历并删除文件的功能，因为Set fbx = distFolder.Files(5)跑不通。
If forceoverride And distFolder.Files.Count Then
Dim fbx As File
For index = distFolder.Files.Count To 1 Step -1
Set fbx = distFolder.Files(5)
fs.DeleteFile fbx
Next
End If
- VBA 中文件操作要用到 FileSystemObject ，在没有引用 Microsoft.Scripting.Runtime.dll 情况下，使用下面这种方式也是可以使用的：
Dim fs As Object
Set fs = CreateObject("Scripting.FileSystemObject")
但是加了这个DLL的引用，就可以直接声明为 FileSystemObject 了，快捷键 ctrl + j 也能出现智能感知提醒了，就这一点为代码编写带来了诸多便利。
Dim fs As FileSystemObject
Set fs = CreateObject("Scripting.FileSystemObject")
-
VBA 中 Error 标签的位置放在哪儿好？
刚接触 VBA ，对错误处理程序处理很迷：
a. 不知道错误处理程序放在哪儿，感觉错误标签无处安放总是被无端访问到。
b. 不知道错误处理程序处理后怎么重试，我就想点重试，不停的重试。
下面用一个示例来说明白这 2 个问题
Sub SomeSub()
On Error Goto Err0
被 Err0 监控的代码
On Error Goto Err1
被 Erro1 监控的代码
On Error GoTo 0
其他代码 '请注意，On Error GoTo 0 ，表示接下来如果发生报错交给 IDE 弹窗提醒，这是合理的，否则会由上一个错误程序处理报错，必然会输出误导性的告警。'
Exit Sub '这一句很重要，表示退出 Sub，如果不在此拦截，下面的 Error 标签全都会被执行'
Err0:
里面写一些友情提示的弹窗，关闭后执行出错语句的接下来的语句
Resume Next
Err1:
里面写请重试的弹窗，关闭后重试上次出错的操作，区别上一个错误处理程序是 Resume 后少了个 Next
Resume
End Sub
小结：
1. Error 程序放在 End Sub 之上，Exit Sub 之下，所有的 Error 标签都放这里管理
2. Resume 就能实现重试功能了，只要弹窗被点掉，出错时执行的语句会再次被执行，表象为不停提示重试，直至成功处理。
3. 而 Resume Next 则会跳过出错的那个动作去访问下一条语句。类比 C# 中的 Continue 关键字
4. Err 属性中包含了一些报错具体消息/错误码，可以输出供调试
VBA 中 Debug.Print 可以把过程中关心的变量输出到 Immidate 窗口，方便调试
-
VBA 中 字符串的拼接使用 & 进行。而且符号之间要加空格，你不加IDE都会自动加的那种，但是，空格建议自己加，因为会出现换行时被自动加分号
;的情况，动图为证
-
VBA 中 Sub 内的变量作用域是真的大，一处声明全Sub范围访问无压力，比如下面这段会报错的 C# 代码，VBA 内就不会
上下文不存在名称“Item”
反观 VBA 中，声明的临时变量（按C#理解，我就叫他临时变量了）作用域可以延展到 For 循环之外：
Item Dim 不 Dim ，作用域都一样能延展到 For 循环之外
得益于变量超级大的作用域，VAB 远隔千里的错误处理程序（Error 标签）中可以输出报错时的上下文信息。比如第 6 条示例中在删除文件报错时，远隔千里的错误处理程序中可以输出文件的名称，告知用户是那个文件异常了。
Tips：VBA 中 怎么在 For 循环中正确的表示数组的长度，不是 arr.Lenght，不是 arr.Count 而是 UBound(arr) 。for循环中取返回值，UBound（arr）的括号一定是不能掉的，这个问题上面已经提到过了。
- VBA 中实现按路径逐层依次创建文件夹
'给个路径只要路径节点上的文件夹不存在就逐层创建出来'
Sub hMkDir(fPath As String)
Dim sp() As String, k%, strP$
If fPath = "" Then Exit Sub
strP = IIf(Right(fPath, 1) = "\", Mid(fPath, 1, Len(fPath) - 1), fPath)
sp = Split(strP, "\"): strP = ""
Do While k < UBound(sp) + 1
strP = IIf(strP = "", sp(0), strP & "\" & sp(k))
If Dir(strP, vbDirectory) = "" Then MkDir strP
k = k + 1
Loop
End Sub
这段代码是从网上摘录的，却没想这个广为流传的版本居然还有一个缺陷：
- VBA 中 怎么使用 Dictionary / 字典的操作
Sub TestDictionaryOperate()
Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "keyA", "itemA"
dict.Add "keyB", "itemB"
dict.Add "keyC", "itemC"
dict.Add "keyD", "itemD"
'通过 Key 检查是否存在'
If dict.Exists("keyA") Then
MsgBox ("存在此项。")
dict.Remove ("keyB")
End If
' 读取指定 Key 的值,如果 key 不存在会自动添加一项 value 为 Empty'
MsgBox (dict.item("keyB"))
Dim item
For Each item In dict.Items
Debug.Print item
Next
dict.RemoveAll '移除所有'
Set dict = Nothing
End Sub
Tips: 引用 Microsoft Scripting Runtime ，用 Dim dict As Dictionary 替换 Dim dict As Object, Ctrl + J 是不是就有了智能提示了呢？
- VBA 中使用正则
'使用正则替换掉字符串中出现的特殊 mark'
Const markPattern As String = "xxx" '这里是你的正则表达式'
Function RemoveMark(ByVal target As String) As String
Dim reg As Object
Set reg = CreateObject("vbscript.regexp")
With reg
.Global = True
.Pattern = Chr(10) & markPattern
RemoveMark = .Replace(target, "")
End With
Set reg = Nothing
End Function
Tips：
1. 引用 Microsoft VBScript Regular Expressions 5.5 ，用 Dim reg As New RegExp 替换 Dim reg As Object,Ctrl + J 可使用智能提示。
2. New 允许隐式创建对象的关键字。如果在声明对象变量时使用New，则在第一次引用该对象时将创建该对象的新实例，因此不必使用Set语句来分配对象引用
-
VBA 中怎么导出 utf-8 编码的文档
这个示例曾在 EXCEL 中使用，当时是为了导出 SQL 文件（Sqlite要求文件编码是 utf-8）
Sub GenerateSqliteCommand()
Dim headCommand As String
Dim dataSheets As Variant
Dim sheetName As Variant
Dim fullFilePath As String
Dim outStream As Object
Dim binStream As Object
fullFilePath = ThisWorkbook.Path & "\anynameasyouwish.sql" '在当前这个Excel文档根目录下创建.sql文件.'
Set outStream = CreateObject("ADODB.Stream")
outStream.Open
outStream.Charset = "utf-8"
outStream.Type = adTypeText
Set binStream = CreateObject("ADODB.Stream")
binStream.Open
binStream.Type = adTypeBinary
outStream.WriteText ("一些你采集好了的文本数据")
' outStream.SaveToFile fileSaveName, 2 如果直接保存文件写入的内容编码为“UTF-8+”而不是“UTF-8”'
outStream.Position = 3
outStream.CopyTo binStream
binStream.SaveToFile fullFilePath, 2 '需要将内容偏移两位去掉“UTF-8+”的bom，fileSaveName为写入数据的文件路径和名字'
binStream.Close
outStream.Close
MsgBox "Sql数据导出完毕！"
End Sub
-
VBA 中的冒号
:
冒号运算符是 VBA 中的语句分隔符，在笔者看来每读到一个冒号就是声明这是一个新语句
'冒号运算符：是VBA中的语句分隔符'
Public Sub TestMe()
If 1 = 1 Then: Debug.Print 1 '这句还不如写成下面这句，不写 End IF 不报错仅仅是因为语法特性：写在一行可省略 End IF'
If 1 = 1 Then Debug.Print 1
If 3 = 0 Then: '冒号运算符对换行的语句不起作用，你可以看到Debug.Print 3会被执行'
Debug.Print 3
'下面这样写一排冒号也是可行的，IDE会把每一个冒号都执行到 '
'故而笔者觉得冒号不加语句类似换行但不等于空行，估摸着冒号也参与了编译。'
::::::::::::::::::::::::::::
'下面示例中，输出结果为 “8c”'
' 解读 IF 后再多的冒号，直到遇见 Else 之前都是属于 If 中的逻辑块，这也是为什么不输出 8a 和 8b 的原因'
If 8 = 0 Then Debug.Print "8a"::: Debug.Print "8b" Else Debug.Print "8c"
'同时，如果多个逻辑写在一句，如果没有冒号，则 IDE 报错，这是冒号存在的道理'
End Sub
Tips: 冒号运算符在语句很零碎时推荐使用。
- VBA 中判等，大于，小于
大于等于 >=
小于等于 <=
不等于 <>
- VBA 中如何禁止（UserForm）用户窗体关闭按钮（窗体右上角的小叉）
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then Cancel = True
End Sub
-
VBA 中不能使用常量数组，别人是这么解释的：
在给常量赋值的表达式中，不能使用变量，用户自定义的函数，或 Visual Basic 的内部函数（如 Chr）。Array属于 Visual Basic 的内部函数，所以不能用Array给常量赋值。
既然不能声明常量数组，那就申明常量字符串呗，然后再使用的时候 Splite 一下不就行了。
Const AccessString As String = "ElementID|Description|ModifiedTime"
Sub Example()
Dim AccessStringArr() As String
AccessStringArr = Split(AccessString, "|")
End Sub
-
VBA 中使用 Array初始化数组，数组需要声明为 Variant 而不知特定数值类型
NG 代码,报错为：Type mismatch
Dim arr1() As Interior
arr1 = Array(1, 3, 4)
Dim arr2() As String
arr2 = Array("1", "3", "4")
OK 代码
Dim arr As Variant
arr = Array("1", "3", "4")
经测试，VBA中以下初始化数组 NG,编译报错：Expected：end of statement
Dim str() = {"1","2","3","4","5"}
查询帮助文档，确认 VBA 中 Array 返回值是一个包含数组的 Variant 实例
写在最后
- 本文以佛系模式持续更新。
- VBA 用的不多，权当微笔记以备忘，如对读者有帮助也是极好。
- 每个条目前都加 VBA 关键字，只是想每一条都能被便捷的搜索到，并不是笔者罗嗦哈~
- 笔者 C#-er, VBA 才不是我想要的选择，Emm...
