返回Scripting.Dictionary时触发Run-time error 424的问题求助

你编写的函数从Excel两列数据生成Scripting.Dictionary，未关闭源工作簿时遍历正常，但关闭后虽能通过.Count确认条目数正确，遍历却触发“Object Required（错误424）”。

原代码 #

Sub Main()
    Dim trackingNumbers As Scripting.Dictionary
    Set trackingNumbers = GetTrackingNumbers

    Dim key As Variant
    For Each key In trackingNumbers.Keys
        Debug.Print key, trackingNumbers(key)
    Next key
End Sub

Function GetTrackingNumbers() As Scripting.Dictionary
     Dim dict As Scripting.Dictionary
     Set dict = New Scripting.Dictionary

     Dim wb As Workbook
     Dim ws As Worksheet
     Dim lastRow As Integer
     Dim i As Integer

     Application.DisplayAlerts = False
     Set wb = Workbooks.Open("C:\Path.xls", True, True)
     Application.DisplayAlerts = True

     Set ws = Sheets(wb.Worksheets(1).Name)
     ws.Activate

     lastRow = ws.Cells(ws.Rows.Count, 1) _
       .End(xlUp).Row ' Getting # of rows

     Do Until i > lastRow ' iterating over each row
         If Cells(i, indexArr(0)) <> "" Then
             dict.Add Cells(i, 1), _
                      Cells(i, 10)
         End If
         i = i + 1
     Loop

       ActiveWindow.Close
       Set GetTrackingNumbers = dict         
End Function

问题根源 #

你向字典中添加的是单元格对象的引用，而非单元格的实际值。当源工作簿关闭后，这些指向单元格的引用会失效——字典的.Count仅统计条目数量，不验证引用有效性，但实际遍历访问时，失效的对象引用就会触发错误424。

解决方法与优化代码 #

核心是存储单元格的值而非对象引用，同时修复代码中的其他潜在问题：

Sub Main()
    Dim trackingNumbers As Scripting.Dictionary
    Set trackingNumbers = GetTrackingNumbers

    Dim key As Variant
    For Each key In trackingNumbers.Keys
        Debug.Print key, trackingNumbers(key)
    Next key
End Sub

Function GetTrackingNumbers() As Scripting.Dictionary
     Dim dict As Scripting.Dictionary
     Set dict = New Scripting.Dictionary

     Dim wb As Workbook
     Dim ws As Worksheet
     Dim lastRow As Long ' 改用Long避免行号超出Integer上限
     Dim i As Integer

     Application.DisplayAlerts = False
     Set wb = Workbooks.Open("C:\Path.xls", True, True)
     Application.DisplayAlerts = True

     Set ws = wb.Worksheets(1) ' 直接引用工作表，无需通过名称中转
     lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

     i = 1 ' 初始化循环起始行（Excel无第0行）
     Do Until i > lastRow
         ' 限定Cells的父对象为ws，读取单元格值而非对象
         If ws.Cells(i, 1).Value <> "" Then ' 替换indexArr(0)为实际列号，此处假设是第1列
             dict.Add ws.Cells(i, 1).Value, ws.Cells(i, 10).Value
         End If
         i = i + 1
     Loop

     wb.Close SaveChanges:=False ' 直接通过wb对象关闭，避免依赖活动窗口
     Set GetTrackingNumbers = dict
End Function

关键优化点说明 #

1. 存储值而非对象：使用.Value（或.Value2/.Text，根据需求选择）确保字典保存的是单元格的实际内容，而非指向单元格的指针，源工作簿关闭后数据依然有效。
2. 避免依赖活动对象：移除ws.Activate和ActiveWindow.Close，直接通过wb对象操作关闭工作簿，代码更健壮。
3. 变量类型修正：将lastRow改为Long，支持Excel超过32767行的场景。
4. 初始化循环变量：明确设置i=1，避免从不存在的第0行开始循环。

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