VBA 笔记

作者: 雨落随风 | 来源:发表于2021-03-04 23:21 被阅读0次

本文记录笔者在开发 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

这段代码是从网上摘录的，却没想这个广为流传的版本居然还有一个缺陷：

要不然上一步剔除路径最后 “\” 逻辑白写了，sp 数组也冗余一项

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

vba 逻辑写在一句不加冒号 IDE 提示异常
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...

VBA 笔记

写在最后

相关文章

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读

辅助资料