excelvbascripting.dictionary

Run-time error 424 when returning a dictionary


I have a function that returns a dictionary that is made up of 2 columns from a workbook, one for key & the other the value. I'm able to loop through the dictionary unless I close the workbook using ActiveWindow.Close. Once I do that I'm able to confirm that the count of the dictionary is still correct using .Count, but when I loop through the dict I receive the 424 error. Object Required.

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

Solution

  • dict.Add Cells(i, 1), _
             Cells(i, 10)
    

    is using Range objects, which no longer exist when their parent workbook is closed.

    You likely want:

    dict.Add Cells(i, 1).Value, _
             Cells(i, 10).Value