属性Let/Get可以动态调用吗?还是每个属性都需要自己的函数

Question

我有一个Excel VBA类，它具有大约30个不同的属性。

我想避免为它们中的每一个编写Get/Let函数。

是否有任何方法可以动态传递Property和值？

伪代码看起来像：

Private strID As String
Private dblAmt As Double
Private dtMatDate As Date
Property Get PropertyName(arg1)
[get value for whatever Property arg1 represents]
End Property
Property Let PropertyName(arg1,arg2)
[set value for Property arg1 to value of arg2]
End Property

Answer 1

如果要在初始化时使用Key将所有私有属性收集到一个私有集合中，则可以使用Key访问私有属性的值。请注意，您正在削弱您的键入，并且没有真正的方法来确保您通过正确的arg1字符串来获得此方法的集合成员。YMMV

Private strID As String
Private dblAmt As Double
Private dtMatDate As Date
Private myProperties As Collection
Private Sub Class_Initialize()
Set myProperties = New Collection
With myProperties
.Add strID, "ID"
.Add dblAmt, Key:="Amount"
.Add dtMatDate, Key:="MatDate"
End With
End Sub
Private Sub Class_Terminate()
Set myProperties = Nothing
End Sub
Property Get PropertyValue(ByVal arg1 As String) As Variant
PropertyValue = myProperties(arg1)
End Property
Property Let PropertyValue(ByVal arg1 As String, ByVal arg2 As Variant)
myProperties(arg1) = arg2
End Property