arrays - MS Access VBA 使用 ReDim Preserve 在需要时增加数组的大小（如在按钮单击事件处理程序方法中或在循环中）

Question

我是 Microsoft Office Professional Plus 2013 Access 的新手。

我正在使用以下方法开发应用程序：

-Microsoft Office Professional Plus 2013 Access

我是我的 VBA 编辑器，我有以下类模块：

Option Explicit
Option Compare Database

Private cntrollingPersonFullNameProp As String
Private cntrollingPersonIsNameAddressProvidedProp As String
Private cntrollingPersonIsDOBProvidedProp As String
Private cntrollingPersonIsTaxResidenceProvidedProp As String
Private cntrollingPersonIsControllingPersonTypeProvidedProp As String
Private cntrollingPersonIsSignedAndDatedProp As String

Public Property Get CntrollingPersonFullName() As String
    CntrollingPersonFullName = cntrollingPersonFullNameProp
End Property

Public Property Let CntrollingPersonFullName(lCntrollingPersonFullName As String)
    cntrollingPersonFullNameProp = lCntrollingPersonFullName
End Property

Public Property Get CntrollingPersonIsNameAddressProvided() As String
    CntrollingPersonIsNameAddressProvided = cntrollingPersonIsNameAddressProvidedProp
End Property

Public Property Let CntrollingPersonIsNameAddressProvided(lCntrollingPersonIsNameAddressProvided As String)
    cntrollingPersonIsNameAddressProvidedProp = lCntrollingPersonIsNameAddressProvided
End Property

Public Property Get CntrollingPersonIsDOBProvided() As String
    CntrollingPersonIsDOBProvided = cntrollingPersonIsDOBProvidedProp
End Property

Public Property Let CntrollingPersonIsDOBProvided(lCntrollingPersonIsDOBProvided As String)
    cntrollingPersonIsDOBProvidedProp = lCntrollingPersonIsDOBProvided
End Property

Public Property Get CntrollingPersonIsTaxResidenceProvided() As String
    CntrollingPersonIsTaxResidenceProvided = cntrollingPersonIsTaxResidenceProvidedProp
End Property

Public Property Let CntrollingPersonIsTaxResidenceProvided(lCntrollingPersonIsTaxResidenceProvided As String)
    cntrollingPersonIsTaxResidenceProvidedProp = lCntrollingPersonIsTaxResidenceProvided
End Property

Public Property Get CntrollingPersonIsControllingPersonTypeProvided() As String
    CntrollingPersonIsControllingPersonTypeProvided = cntrollingPersonIsControllingPersonTypeProvidedProp
End Property

Public Property Let CntrollingPersonIsControllingPersonTypeProvided(lCntrollingPersonIsControllingPersonTypeProvided As String)
    cntrollingPersonIsControllingPersonTypeProvidedProp = lCntrollingPersonIsControllingPersonTypeProvided
End Property

Public Property Get CntrollingPersonIsSignedAndDated() As String
    CntrollingPersonIsSignedAndDated = cntrollingPersonIsSignedAndDatedProp
End Property

Public Property Let CntrollingPersonIsSignedAndDated(lCntrollingPersonIsSignedAndDated As String)
    cntrollingPersonIsSignedAndDatedProp = lCntrollingPersonIsSignedAndDated
End Property

在表单代码文件中，

Dim cntrollingPersonsArray()  As CntrollingPerson


Private Sub AddControllingPersonBtn_Click()
     Dim cntrlPerson As New CntrollingPerson
    cntrlPerson.CntrollingPersonFullName =  …….
    cntrlPerson.CntrollingPersonIsNameAddressProvided =  …..

  ReDim Preserve cntrollingPersonsArray(UBound(cntrollingPersonsArray)+ 1)   
cntrollingPersonsArray(UBound(cntrollingPersonsArray)) = cntrlPerson 

 End Sub

该应用程序抛出：

'91' 对象变量或未设置块变量

在以下行

cntrollingPersonsArray(UBound(cntrollingPersonsArray)) = cntrlPerson

我尝试了一堆不同的代码修改

ReDim Preserve cntrollingPersonsArray(UBound(cntrollingPersonsArray))

或者

ReDim Preserve cntrollingPersonsArray(0 to UBound(cntrollingPersonsArray))

或者

 ReDim Preserve cntrollingPersonsArray(1 to UBound(cntrollingPersonsArray))

有人可以告诉我要采取哪些步骤来纠正上述问题吗？

标签： arraysvbams-accessdimensions

Answer 1

使用集合对象而不是数组。你所有的问题都解决了！

例子：

Option Explicit

Private cntrollingPersons As New Collection

Private Sub AddControllingPersonBtn_Click()
    Dim cntrlPerson As New CntrollingPerson
    cntrlPerson.CntrollingPersonFullName = ""
    cntrlPerson.CntrollingPersonIsNameAddressProvided = ""

    cntrollingPersons.Add cntrlPerson
End Sub

相关阅读：https ://excelmacromastery.com/excel-vba-collections/