如何将Excel VBA类的Item属性设为默认以省略.Item调用

我创建了名为AppInventory的类用于实验，目标是实现以下格式的单元格数据读写操作：

x = AppInventory(r,c): AppInventory(r,c) = x
x = AppInventory("A1"): AppInventory("A1") = x
x = AppInventory(cellRange): AppInventory(cellRange) = x

目前添加.Item后缀可正常运行（如x = AppInventory.Item(r,c)），但我希望省略.Item，直接默认调用Item属性。

我尝试过两种方法但未彻底解决：

• 在属性声明中添加Default关键字，两种写法都试过：

  Public Default Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
  Default Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
  

• 导出类文件，在Get Item()后添加Attribute Item.VB_UserMemID = 0再重新导入，曾成功一次，但修改注释后失效。

附现有AppInventory类代码：

Private ws As Worksheet
Private targetCell As Range
    
Private Sub Class_Initialize()
    Set ws = ThisWorkbook.Worksheets("AppInventory")
End Sub

Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
    Set targetCell = GetTargetCell(Index1, Index2)
    Item = targetCell.Value
End Property
    
Public Property Let Item(Index1 As Variant, Optional Index2 As Variant, ByVal Value As Variant)
    Set targetCell = GetTargetCell(Index1, Index2)
    targetCell.Value = Value
End Property
    
Private Function GetTargetCell(Index1 As Variant, Optional Index2 As Variant) As Range
    Select Case VarType(Index1)
        Case vbInteger, vbLong ' Numeric, likely row number
            Set GetTargetCell = ws.Cells(Index1, Index2)
        Case vbString ' String, like "A1"
            Set GetTargetCell = ws.Range(Index1)
        Case vbObject ' Range object
            Set GetTargetCell = ws.Range(Index1.Address)
        Case Else
            MsgBox "Invalid input"
    End Select
End Function

可行实现方案 #

1. 正确设置默认属性的步骤

   • 右键AppInventory类模块，选择「导出文件」，保存为.cls格式的文件
   • 用文本编辑器（如Notepad++）打开导出的.cls文件
   • 找到Public Property Get Item和Public Property Let Item的代码块，在每个属性声明的下一行添加Attribute Item.VB_UserMemID = 0，修改后如下：

     Public Property Get Item(Index1 As Variant, Optional Index2 As Variant) As Variant
     Attribute Item.VB_UserMemID = 0
         Set targetCell = GetTargetCell(Index1, Index2)
         Item = targetCell.Value
     End Property
     
     Public Property Let Item(Index1 As Variant, Optional Index2 As Variant, ByVal Value As Variant)
     Attribute Item.VB_UserMemID = 0
         Set targetCell = GetTargetCell(Index1, Index2)
         targetCell.Value = Value
     End Property
     

   • 保存修改后的.cls文件，回到VBA编辑器，删除原有的AppInventory类模块，再右键工程选择「导入文件」，导入修改后的.cls文件

2. 关键注意事项

   • 导入后，不要在VBA编辑器中修改Item属性的声明行（包括在声明附近添加/修改注释），否则VBA会自动移除Attribute标记，导致默认属性失效
   • 后续如果需要修改Item属性的代码，建议直接修改导出的.cls文件后重新导入，避免在编辑器中触碰属性声明部分
   • 一个类只能有一个默认属性，确保Item是唯一设置VB_UserMemID = 0的属性

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