vba - 像公共(public)常量填充字典之类的东西

Question

在 VBA 中声明 的最佳方式是什么？公共(public)常量 像这样具有多个属性的元素的集合/字典？

Dim fruits as new dictionary
fruits.add "banana", array("yellow", "long", "curved")
fruits.add "watermelon", array("red", "big", "sferic")
fruits.add "blueberry", array("blue", "little", "sferic")

我可以更改 Dim fruits as new dictionary进入 Public fruits as new dictionary移到顶部(外部程序)，但是我如何为将使用它的多个子/函数填充一次该字典？

我可以将所有三个“添加”指令放在一个名为“fruits_populate()”的专用子中，并在我使用它的每个子/函数的开头调用这个子，但有更好的解决方案吗？

Answer 1

一种解决方案是模拟一个内存的 setter/getter :

Public Function FRUITS() As Dictionary
    Static obj As Dictionary  ' Static keeps the object between calls '

    If obj Is Nothing Then
        Set obj = New Dictionary
        obj.add "banana", Array("yellow", "long", "curved")
        obj.add "watermelon", Array("red", "big", "sferic")
        obj.add "blueberry", Array("blue", "little", "sferic")
    End If

    Set FRUITS = obj
End Sub

然后得到一个项目:

Debug.Print FRUITS.Item("banana")(1)

另一种方法是实现一个类模块 ClsFruits.cls :

Dim base As Dictionary

Private Sub Class_Initialize()
    Set base = New Dictionary
    base.add "banana", Array("yellow", "long", "curved")
    base.add "watermelon", Array("red", "big", "sferic")
    base.add "blueberry", Array("blue", "little", "sferic")
End Sub

Public Property Get Item(Key)
    Item = base.Item(Key)
End Property

然后得到一个项目:

Dim fruits As New ClsFruits

Sub Test()
    Debug.Print fruits.Item("banana")(1)
End Sub