VBA嵌套字典类对象集合报错：Object variable not set

需求说明 #

• 创建Class Objects Collection，每个类对象包含：
  • 获奖者姓名
  • 存储分配礼物的Scripting.Dictionary
  • 礼物总价
• 需按预算从高价到低价分配礼物

错误原因分析 #

"Object variable or With block variable not set"错误本质是对象变量未完成初始化，常见触发场景：

1. 类模块中声明的Scripting.Dictionary仅做了变量声明，未创建实例
2. 存储类对象的Collection本身未实例化
3. 类对象未实例化就直接调用其属性/方法

修正方案与示例代码 #

1. 正确编写类模块（假设类名为AwardRecipient） #

在类的初始化事件中创建Scripting.Dictionary实例，确保每次创建类对象时，字典已就绪：

Public Name As String
Public Gifts As Scripting.Dictionary
Public TotalGiftValue As Double

Private Sub Class_Initialize()
    ' 初始化字典对象，避免后续赋值时出现对象未设置错误
    Set Gifts = New Scripting.Dictionary
End Sub

2. 主模块中正确创建集合与分配礼物 #

确保集合、类对象都完成实例化，再进行礼物分配与排序：

Sub AllocateGiftsByBudget()
    Dim recipients As Collection
    Dim currentRecipient As AwardRecipient
    
    ' 初始化集合对象
    Set recipients = New Collection
    
    ' 创建第一个获奖者并分配礼物
    Set currentRecipient = New AwardRecipient
    currentRecipient.Name = "Alice"
    currentRecipient.Gifts.Add "高端相机", 12000
    currentRecipient.TotalGiftValue = 12000
    recipients.Add currentRecipient
    
    ' 创建第二个获奖者并分配礼物
    Set currentRecipient = New AwardRecipient
    currentRecipient.Name = "Bob"
    currentRecipient.Gifts.Add "游戏主机", 5000
    currentRecipient.TotalGiftValue = 5000
    recipients.Add currentRecipient
    
    ' 按礼物总价从高到低排序集合
    Dim i As Integer, j As Integer
    Dim tempRecipient As AwardRecipient
    For i = 1 To recipients.Count - 1
        For j = i + 1 To recipients.Count
            If recipients(i).TotalGiftValue < recipients(j).TotalGiftValue Then
                Set tempRecipient = recipients(i)
                recipients.Remove i
                recipients.Add tempRecipient, , j
            End If
        Next j
    Next i
    
    ' 输出验证结果
    For Each currentRecipient In recipients
        Debug.Print "获奖者：" & currentRecipient.Name & " | 礼物总价：" & currentRecipient.TotalGiftValue
        Dim giftKey As Variant
        For Each giftKey In currentRecipient.Gifts.Keys
            Debug.Print "  - " & giftKey & "：" & currentRecipient.Gifts(giftKey)
        Next giftKey
    Next currentRecipient
End Sub

关键排查点 #

• 检查类模块是否有Class_Initialize事件并完成字典实例化
• 确认所有对象（Collection、类实例、Dictionary）都通过Set ... = New ...完成初始化
• 避免在未实例化对象时直接调用其属性或方法

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