Mac版Excel VBA无法访问Collection返回对象的属性如何解决

我编写的代码如下：

' [SandwichObject 类模块]
Option Explicit

Public bread As String
Public filling As String
Public calories As String

Function printDesc()
  Debug.Print ("bread: " & bread)
  Debug.Print ("filling: " & filling)
  Debug.Print ("calories: " & calories)
End Function

' [宏模块]
Option Explicit

Sub collTest()

Dim coll As Collection
Set coll = New Collection

Dim s As SandwichObject
Set s = New SandwichObject
s.bread = "white"

Call coll.Add(s)
Call coll(1).printDesc

' 以下两行代码均无法正常运行
' 报错："Variable uses an automation type not supported in visual basic"
'coll(1).bread = "brown"

' 代码能运行，但不会输出任何值，立即窗口执行?coll(1).bread返回Null
' Debug.Print ("Bread is " & coll(1).bread)

End Sub

正如代码注释标注的，尝试访问集合返回的对象属性时会触发报错，但调用该对象的方法可以正常执行，不清楚问题原因。

补充运行环境：上述代码运行于Mac版Excel的VBA环境中。

这是Mac版VBA原生Collection对象的已知固有bug，和代码逻辑写法无关：

• Windows版VBA中，Collection的默认成员会直接返回存入的原对象引用，直接链式访问coll(1).属性可以正常读写
• Mac版VBA中，Collection取成员时的隐式引用转换存在缺陷：调用对象方法时，引擎可以正确识别原对象类型，因此printDesc能正常执行；但直接访问公共字段时，引擎无法完成正确的类型对齐，要么抛出自动化类型不支持的错误，要么直接返回Null值。

修复方式非常简单，不要直接链式调用集合返回值的属性/字段，先把集合取出的对象赋值给显式声明的对应类型变量，再做后续操作即可，修改后的可正常运行代码如下：

Sub collTestFixed()
    Dim coll As Collection
    Set coll = New Collection

    Dim s As SandwichObject
    Set s = New SandwichObject
    s.bread = "white"
    coll.Add s
    
    ' 先将集合内的对象取出，赋值给显式声明类型的变量
    Dim tempObj As SandwichObject
    Set tempObj = coll(1)
    
    ' 后续调用方法、读写属性均正常
    tempObj.printDesc
    tempObj.bread = "brown"
    Debug.Print "Bread is " & tempObj.bread ' 立即窗口正常输出 Bread is brown
End Sub

如果需要频繁操作集合内的自定义类对象，也可以用数组替代Collection，或者封装自定义集合类，都可以绕开Mac端VBA的这个原生bug。

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