arrays - VBA 中的属性数组

Question

我有 13 个属性，其语法如下:

Public Property Get Town() As String
    Town = txtTown.Text
End Property

我希望能够使用循环并迭代这些属性的集合，而不是引用 13 个属性中的每一个。我将如何创建这些预先存在的属性的数组。我非常希望他们保留有意义的名字。

编辑:

aSet IDCell = customerDBSheet.Range("CustomerDBEntryPoint").Offset(ID() - 1)
Dim properties()
properties = Array("ID()", "FirstName()", "LastName()", "Address 1()", "Address 2()",     "Town()", "Postcode()", "Phone()", "Email()", "Sex()", "Username()", "PasswordHash()")
For i = 0 To 11
IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))
Next i

我在最后一行之前收到错误:IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))

最终代码:

Dim properties()
properties = Array("ID", "FirstName", "LastName", "Address1", "Address2", "Town", "Postcode", "Phone", "Email", "Sex", "Username", "PasswordHash")
For i = 0 To 11
IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbMethod))
Next i

上面显示的最后使用的代码专门使用了从 Radek 的答案中编辑的 CallByName 函数，因为属性已转换为函数。此外，For 循环需要使用基于 0 的索引。此外，当第四个可选参数是空字符串文字时，会引发异常。

Answer 1

您可以迭代属性名称数组:

Dim vProperties()
Dim vPropertyName

vProperties = Array("Town", "Street", "ZipCode")
For Each vPropertyName In vProperties
    '''Do something
Next

现在是“棘手”部分:“做某事” block 仅将 vPropertyName 设置为连续的属性名称。为了通过字符串变量中的名称访问属性值，请使用 CallByName 函数:

...
For Each vPropertyName In vProperties
    CallByName MyObject1, vPropertyName, VbLet, "" 
Next

通过用户窗体的“Controls”集合进行第二个选项迭代:

Dim vControl As Control

For Each vControl In UserForm1.Controls
    If TypeName(vControl) = "TextBox" OR vControl.Name Like "MyPattern*" Then
        '''Do something
    End If
Next

编辑:

Dim properties()
properties = Array("ID", "FirstName", "LastName", "Address1", "Address2", "Town", "Postcode", "Phone", "Email", "Sex", "Username", "PasswordHash")
For i = LBound(properties) To UBound(properties)
    IDCell.Offset(1, i).Value = CStr(CallByName(frmCustomerEntry, properties(i), VbLet, ""))
Next i

我在你的代码中发现了一些东西

• 括号是不必要的
• 女巫属性“地址 1”和“地址 2”有问题。您不能定义名称中包含空格的属性
• 我相信 LBound 和 UBound 函数比使用显式数组边界更方便