VBA嵌套字典元素访问报错424：对象要求问题排查

问题场景 #

我在名为"Mapping"的Excel工作表中有如下表格：

我编写了如下VBA代码尝试创建嵌套字典，但最后一行代码抛出424"对象要求"错误，请问如何正确访问字典中的元素？

Sub build_dictionary()
    
    Dim funds As Scripting.Dictionary
    Dim mapping As Scripting.Dictionary
    Dim fund As Variant
    
    'funds represents the outer dictionary
    Set funds = New Scripting.Dictionary
    
    'mapping represents the inner dictionary
    Set mapping = New Scripting.Dictionary
    
    For Each fund In Worksheets("Mapping").Range("A2", Range("a2").End(xlDown)).Cells
        
        'set key value pairs to the mapping(inner) dictionary
        mapping.Add "portfolio", fund.Offset(0, 1).Value
        mapping.Add "structure", fund.Offset(0, 2).Value
        mapping.Add "locationaccount", fund.Offset(0, 3).Value
        
        'in the outer dictionary set key=fund, item = mapping dictionary
        funds.Add fund, mapping
        
        'remove all key-value pairs of mapping dictionary in preparation of next loop
        mapping.RemoveAll
           
    Next fund
    
    'produces error '424' Object required
    Debug.Print funds("F1").Item("portfolio")

End Sub

问题根源 #

1. 内部字典引用共享：循环中复用同一个mapping字典对象，执行RemoveAll会清空该对象的所有内容，而外层字典funds存储的是这个对象的引用，最终所有外层字典条目指向的都是被清空的字典，访问时触发"对象要求"错误。
2. 键类型不匹配：用fund（Range单元格对象）作为外层字典的键，后续用字符串"F1"访问时，因类型不匹配无法找到对应条目。

修正后的代码 #

Sub build_dictionary()
    
    Dim funds As Scripting.Dictionary
    Dim mapping As Scripting.Dictionary
    Dim fund As Variant
    
    Set funds = New Scripting.Dictionary
    
    For Each fund In Worksheets("Mapping").Range("A2", Worksheets("Mapping").Range("A2").End(xlDown)).Cells
        ' 每次循环新建内部字典，避免引用共享
        Set mapping = New Scripting.Dictionary
        
        mapping.Add "portfolio", fund.Offset(0, 1).Value
        mapping.Add "structure", fund.Offset(0, 2).Value
        mapping.Add "locationaccount", fund.Offset(0, 3).Value
        
        ' 用单元格值（字符串）作为外层字典的键
        funds.Add fund.Value, mapping
    Next fund
    
    ' 正确访问嵌套字典元素
    Debug.Print funds("F1")("portfolio") ' 输出：Fund 1
    Debug.Print funds("F2")("locationaccount") ' 输出：Acct_2

End Sub

关键修改说明 #

• 将内部字典的Set语句移到循环内部，每次循环创建全新的字典对象，确保外层字典的每个条目对应独立的内部字典。
• 外层字典的键使用fund.Value（字符串类型），保证后续用字符串索引能准确匹配。
• 访问内部字典元素时，直接使用外层字典("键")("内部键")的简洁语法即可。

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