VBA Excel 2010 dictionary object required error

Question

I'm trying to write code that looks at an array "arr", then loops through each value in that array to compare to a newly created dictionary. If the key already exists, the value (count) of the key is supposed to be incremented by 1, otherwise the key should be added with a value of 1.

However, the below line is throwing an Object Required error:

        If dic.Exists(c.Value) Then ' Runtime Error 424: Object Required

The entire sub is below:

Private Sub PODic()


    Dim arr As Variant
    Dim Counter As Long
    Dim dic As Object
    Dim lrow As Long
    Dim c As Variant


    Set dic = CreateObject("Scripting.Dictionary") ' late bind
        dic.CompareMode = vbTextCompare

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With


    lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 4).End(xlUp).row
    lrow = lrow - 1
        Debug.Print lrow

    arr = ActiveSheet.Range("d2", ActiveSheet.Cells(lrow, "d")).Value

        For Each c In arr
            Debug.Print c
            If dic.Exists(c.Value) Then ' Runtime Error 424: Object Required
                dic(c.Value) = dic(c.Value) + 1
            Else
                dic.Add c.Value, 1
            End If
        Next

    For Each k In dic
        Debug.Print k & "," & dic(k)
    Next k



    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

    MsgBox "Populate dictionary macro complete."



End Sub

I believe this might be something to do with the variable type used for c (or for the dic perhaps) but I can't figure out where the problem is occuring. I also tried solving this by creating an On Error GoTo but had the same issue.

         On Error GoTo ERRINCVAL
            dic.Add cell.Value, 1
            On Error GoTo 0



ERRINCVAL:

    dic(c.Value) = dic(c.Value) + 1 ' Same error thrown on this line, if I try to use GoTo instead of If
    Resume Next

Help very much appreciated.

Answer 1

@sktneer has the correct answer. I just wanted to demonstrate a cleaner way of writing it.

Using a With statements makes the code more readable and is more efficient.

There is no reason to have a Last Row variable.

Range("D2", Cells(Rows.Count, "D").End(xlUp)).Value

It is not necessary to use a temporary array variable for use in a For Each loop. The VBA will automatically create one when it initializes the loop.

For Each Key In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)).Value

There is no need to test if a Key exists and then add the Key with value 1 or increment the existing Key. The VBA will automatically create the Key it it doesn't exist.

dic(Key) = dic(Key) + 1

Reuse the same Key variable when Adding Key/Value pairs or iterating over the Dictionary.

dic.Add Key, 1

For Each Key in dic

You can use Join to print all the Keys and Items to the immediate window.

Debug.Print "Keys: "; Join(dic.Keys(), ",")

Debug.Print "Items: "; Join(dic.Items(), ",")

Private Sub PODic()

    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

    Dim dic As Object, Key As Variant

    Set dic = CreateObject("Scripting.Dictionary")
    dic.CompareMode = vbTextCompare

    With ActiveSheet
        For Each Key In .Range("D2", .Cells(.Rows.Count, "D").End(xlUp)).Value
            dic(Key) = dic(Key) + 1
        Next
    End With

    Debug.Print "Keys: "; Join(dic.Keys(), ",")
    Debug.Print "Items: "; Join(dic.Items(), ",")

    For Each Key In dic
        Debug.Print Key & "," & dic(Key)
    Next

    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With

    MsgBox "Populate dictionary macro complete."

End Sub

Answer 2

You could use simplified form of assigning value to dictionary:

dic("key1") = dic("key1") + 1

Answer 3

c.value doesn't make sense here. c here is an item in the array so you can directly refer to it.

You should try it like this...

For Each c In arr
    Debug.Print c
    If dic.Exists(c) Then
        dic(c) = dic(c) + 1
    Else
        dic.Add c, 1
    End If
Next

Or you can use the following approach as well to get the desired output...

Dim i As Long
arr = ActiveSheet.Range("d2", ActiveSheet.Cells(lrow, "d")).Value

For i = 1 To UBound(arr, 1)
    Debug.Print arr(i, 1)
    If dic.Exists(arr(i, 1)) Then ' Runtime Error 424: Object Required
        dic(arr(i, 1)) = dic(arr(i, 1)) + 1
    Else
        dic.Add arr(i, 1), 1
    End If
Next

Answer 4

Final code ended up:

Private Sub PODic()

Dim arr As Variant
Dim Counter As Long

Dim lrow As Long
Dim c As Variant

Dim dic As Object
Set dic = CreateObject("Scripting.Dictionary") ' late bind
    dic.CompareMode = vbTextCompare

With Application
    .ScreenUpdating = False
    .Calculation = xlCalculationManual
End With


lrow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 4).End(xlUp).row
lrow = lrow
    Debug.Print lrow

Dim i As Long
    arr = ActiveSheet.Range("d2", ActiveSheet.Cells(lrow, "d")).Value

For i = 1 To UBound(arr, 1)
    Debug.Print arr(i, 1)
    If dic.Exists(arr(i, 1)) Then
        dic(arr(i, 1)) = dic(arr(i, 1)) + 1
    Else
        dic.Add arr(i, 1), 1
    End If
Next

For Each k In dic
    Debug.Print k & "," & dic(k)
Next k
Debug.Print dic.Count


With Application
    .ScreenUpdating = True
    .Calculation = xlCalculationAutomatic
End With

End Sub

Answer 5

You need to declare the dictionary object.

We declare a dictionary as follows:

Dim dict As New Scripting.Dictionary

or

Dim dict As Scripting.Dictionary
Set dict = New Scripting.Dictionary

Lots more information about setting up and using dictionaries in VBA at this site.

---

Also as a rule of thumb, use Option Explicit at the top of every module (especially when troubleshooting) to make sure your variables and objects are all setup properly.

Answer 6

Using c.value implies you're treating it as a range object and accessing its 'value' property -- which your code will let you do as c is declared as variant.

However, what I think you're doing is looping through each element in the array -- in which case you shouldn't use c.value, just c on its own.

I think it would be better to loop through the array using a for-next loop between array's ubound and lbound.

So try:

    Dim Index as Long

    For Index = lbound(arr,1) to ubound(arr,1)

If dic.Exists(arr(Index,1)) Then

' Array arr is 1-based but items in dic might be 0-based, so adjust if necessary'

dic(arr(Index-1,1)) = dic(arr(Index-1,1)) + 1
' if dic is not 0-based, get rid of the -1 above.'

Else

dic.Add arr(Index,1), 1

End If

    Next index

Untested, written on mobile.