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?
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.