VBA父子类模块配置求助：类创建、属性与功能实现问题

1. 实现类似Workbook.Open的命令（可返回对象或仅执行） #

在父类Defects中编写Function类型的方法，VBA允许调用者选择接收返回对象或忽略返回值，完全匹配Workbook.Open的用法：

' 父类Defects代码
Private m_colDefects As Collection

Private Sub Class_Initialize()
    Set m_colDefects = New Collection
End Sub

Public Function Open(ByVal defectID As String) As Defect
    Dim targetDefect As New Defect
    ' 加入初始化逻辑：比如从工作表/数据库加载Defect属性
    targetDefect.ID = defectID
    targetDefect.Description = "加载的缺陷描述"
    
    ' 将初始化后的对象加入父集合
    m_colDefects.Add targetDefect, Key:=defectID
    ' 返回对象供调用者使用
    Set Open = targetDefect
End Function

• 仅执行调用：Defects.Open "DEF001"
• 返回对象调用：Set myDefect = Defects.Open "DEF001"

2. Item属性报错的常见原因及修复 #

常见报错原因 #

• 集合未初始化：m_colDefects未在Class_Initialize中创建实例就访问Item
• 索引类型不匹配：传入字符串索引但集合未设置对应键，或传入无效的数字索引
• 返回类型错误：Item属性返回Object而非Defect强类型
• 集合为空时强制访问Item

修复后的Item属性代码 #

' 父类Defects代码
Public Property Get Item(Index As Variant) As Defect
    ' 有效性校验
    If m_colDefects Is Nothing Then
        Err.Raise 91, "Defects.Item", "集合未初始化"
    End If
    If IsNumeric(Index) Then
        If Index < 1 Or Index > m_colDefects.Count Then
            Err.Raise 5, "Defects.Item", "索引超出范围"
        End If
    Else
        If Not m_colDefects.Contains(Index) Then
            Err.Raise 5, "Defects.Item", "指定的缺陷ID不存在"
        End If
    End If
    ' 返回强类型Defect对象
    Set Item = m_colDefects(Index)
End Property

• 必须在Class_Initialize中初始化集合：Set m_colDefects = New Collection
• 加入前置校验，抛出明确的错误信息便于调试

3. 隐藏私有变量（模拟Office对象的隐藏效果） #

核心逻辑是所有内部变量设为Private，仅通过Public属性暴露需要访问的成员，完全复刻Office对象的私有成员隐藏效果：

' 子类Defect代码
' 所有内部变量设为Private，外部不可见
Private m_ID As String
Private m_Description As String
Private m_Status As String

' 暴露读写属性
Public Property Get ID() As String
    ID = m_ID
End Property
Public Property Let ID(ByVal value As String)
    m_ID = value
End Property

' 暴露只读属性：仅保留Get过程，内部修改用Private Let
Public Property Get Status() As String
    Status = m_Status
End Property
Private Property Let Status(ByVal value As String)
    m_Status = value
End Property

• 禁止在类中使用Public变量，全部用Private变量+Property Get/Let/Set组合
• 只读属性仅对外提供Property Get，内部修改逻辑封装在私有过程中

4. 实现Delete方法和Parent属性 #

Parent属性实现 #

在子类Defect中添加私有父引用，在父类Defects的Add方法中自动绑定：

' 子类Defect代码
Private m_Parent As Defects

' 外部只读的Parent属性
Public Property Get Parent() As Defects
    Set Parent = m_Parent
End Property

' 内部赋值用Friend（同工程内可访问，外部不可见）
Friend Property Set Parent(ByVal value As Defects)
    Set m_Parent = value
End Property

' 父类Defects的Add方法
Public Sub Add(ByVal newDefect As Defect)
    m_colDefects.Add newDefect, Key:=newDefect.ID
    ' 自动绑定父对象引用
    Set newDefect.Parent = Me
End Sub

Delete方法实现 #

提供两种删除方式：父类按索引/ID批量删除，子类自我删除：

' 父类Defects的Delete方法（按索引/ID删除）
Public Sub Delete(ByVal Index As Variant)
    If m_colDefects Is Nothing Or m_colDefects.Count = 0 Then Exit Sub
    m_colDefects.Remove Index
End Sub

' 子类Defect的Delete方法（自我删除）
Public Sub Delete()
    If Not m_Parent Is Nothing Then
        m_Parent.Delete Me.ID
        ' 清空父引用，避免悬空指针
        Set m_Parent = Nothing
    End If
End Sub

内容的提问来源于stack exchange，提问作者arenti