何時使用靜態變數
在本地宣告的靜態變數不會被破壞,並且在退出 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
通過五十萬行填充靜態物件的 UDF 示例
* 使用 UDF 填充 500K 行的經過時間:
- 使用 Dim rgx As Object :148.74 秒
- 使用 Static rgx As Object :26.07 秒
* 這些應僅考慮進行相對比較。你自己的結果將根據
所執行操作的複雜性和範圍而有所不同。
請記住,UDF 在工作簿的生命週期中不會計算一次。即使非易失性 UDF 會在其引用的範圍內的值發生變化時重新計算。每個後續重新計算事件只會增加靜態宣告變數的好處。
- 靜態變數可用於模組的生命週期,而不是宣告和分配它的過程或函式。
- 靜態變數只能在本地宣告。
- 靜態變數儲存私有模組級變數的許多相同屬性,但具有更受限制的範圍。
相關參考: 靜態(Visual Basic)