何时使用静态变量

Created: November-22, 2018

在本地声明的静态变量不会被破坏，并且在退出 Sub 过程时不会丢失其值。对程序的后续调用不需要重新初始化或赋值，尽管你可能希望将任何记住的值归零。

当后期绑定一个被重复调用的’helper’子对象时，这些特别有用。

代码段 1： 在许多工作表中重用 Scripting.Dictionary 对象

Option Explicit

Sub main()
    Dim w As Long
    
    For w = 1 To Worksheets.Count
        processDictionary ws:=Worksheets(w)
    Next w
End Sub

Sub processDictionary(ws As Worksheet)
    Dim i As Long, rng As Range
    Static dict As Object
    
    If dict Is Nothing Then
        'initialize and set the dictionary object
        Set dict = CreateObject("Scripting.Dictionary")
        dict.CompareMode = vbTextCompare
    Else
        'remove all pre-existing dictionary entries
        ' this may or may not be desired if a single dictionary of entries
        ' from all worksheets is preferred
        dict.RemoveAll
    End If
    
    With ws
        
        'work with a fresh dictionary object for each worksheet
        ' without constructing/destructing a new object each time
        ' or do not clear the dictionary upon subsequent uses and 
        ' build a dictionary containing entries from all worksheets
    
    End With
End Sub

代码段 2： 创建一个后期绑定 VBScript.RegExp 对象的工作表 UDF

Option Explicit

Function numbersOnly(str As String, _
                     Optional delim As String = ", ")
    Dim n As Long, nums() As Variant
    Static rgx As Object, cmat As Object

    'with rgx as static, it only has to be created once
    'this is beneficial when filling a long column with this UDF
    If rgx Is Nothing Then
        Set rgx = CreateObject("VBScript.RegExp")
    Else
        Set cmat = Nothing
    End If
    
    With rgx
        .Global = True
        .MultiLine = True
        .Pattern = "[0-9]{1,999}"
        If .Test(str) Then
            Set cmat = .Execute(str)
            'resize the nums array to accept the matches
            ReDim nums(cmat.Count - 1)
            'populate the nums array with the matches
            For n = LBound(nums) To UBound(nums)
                nums(n) = cmat.Item(n)
            Next n
            'convert the nums array to a delimited string
            numbersOnly = Join(nums, delim)
        Else
            numbersOnly = vbNullString
        End If
    End With
End Function

StackOverflow 文档
通过五十万行填充静态对象的 UDF 示例

* 使用 UDF 填充 500K 行的经过时间：

- 使用 Dim rgx As Object ：148.74 秒

- 使用 Static rgx As Object ：26.07 秒

* 这些应仅考虑进行相对比较。你自己的结果将根据
所执行操作的复杂性和范围而有所不同。

请记住，UDF 在工作簿的生命周期中不会计算一次。即使非易失性 UDF 会在其引用的范围内的值发生变化时重新计算。每个后续重新计算事件只会增加静态声明变量的好处。

静态变量可用于模块的生命周期，而不是声明和分配它的过程或函数。
静态变量只能在本地声明。
静态变量保存私有模块级变量的许多相同属性，但具有更受限制的范围。