excelvbadictionaryscriptingscripting.dictionary

VB Scripting.Dictionary object is visible in Locals window, but cannot be accessed in watches, immediate or macro


I have been working on this the entire morning and it's really starting to drive me nuts. I have written a function which goes through the row header column of a PivotTable object to find all currencies listed. The function is meant to populate a Scripting.Dictionary with the name of the currency and the address of the cell to the left of it (note that, as far as I understand it, I cannot just use a list of the PivotItems, because some of them are going to be invisible due to the filters set on the PivotTable). This dictionary is then returned for some further work (I have also tried passing in an empty dictionary as an argument and populating it; hence using a subroutine instead of a function. The result was the same however). This is my code:

Public Function WriteCurrencyDict(PTable As PivotTable) As Scripting.Dictionary
    Dim ws As Worksheet: Set ws = ActiveSheet

    Dim PTableRange As Range: Set PTableRange = PTable.TableRange1
    Dim PField As PivotField: Set PField = PTable.PivotFields("CURRENCY")
    Dim PItem As PivotItem
    
    Dim currencyDict As New Scripting.Dictionary
    
    Dim rowHeadersCol As Integer: rowHeadersCol = PTable.TableRange1.Column
    Dim firstDataFieldRow As Integer: firstDataFieldRow = PTable.DataBodyRange.row
    Dim numberOfDataRows As Integer: numberOfDataRows = PTable.DataBodyRange.rows.Count
    
    Dim i As Integer
    
    For Each PItem In PField.PivotItems
        For i = 1 To numberOfDataRows
            If ws.Cells(firstDataFieldRow + i - 1, rowHeadersCol).Value = PItem.Name Then
                currencyDict.Add PItem.Name, ws.Cells(firstDataFieldRow + i - 1, rowHeadersCol - 1).Address(True, True)
                Exit For
            End If
        Next i
    Next PItem
    
    Set WriteCurrencyDict = currencyDict
End Function

I then call this function elsewhere:

Dim currencyDict As Scripting.Dictionary
Set currencyDict = CurrencyHandler.WriteCurrencyDict(PTable)
Dim numberOfCurrencies as Integer: numberOfCurrencies = currencyDict.Items.Count

Now here is my error: I cannot access currencyDict, because I keep getting run-time error 424: object required. What is interesting is that the 'Locals' window is clearly showing my currencyDict object along with the items it contains. However, neither the watches window, nor the immediate window, nor the macro code can actually access this object. What is going on here? Am I misunderstanding something fundamental about the Scripting.Dictionary class?


Solution

  • Here's a reminder that it's important to actually use your data structures correctly...

    currencyDict.Items returns an array; so currencyDict.Items.Count won't work. To get the number of items in the dictionary you have to simply do currencyDict.Count, or, alternatively, ubound(currencyDict.Items) - lbound(currencyDict.Items) + 1. Thanks for the solution, Nathan.