Dictionary object returning keys before they are added

riddley · Jun 26th 2012

I am using a dictionary object from the MS Scripting Runtime library to store a series of arrays and perform operations on the array cells as necessary. There is a for loop to go through the process of creating all of these entries. My issue is that when using the .exists property, it is returning a True even before the item has been added. Closer debugging indicates that the key is being added to the dictionary at the beginning of the for loop, even though no .add command is used and will not be used until the end of the loop. The result is that the values in the arrays do not totalize as intended.

Code for the dictionary build has been included below. I have attached the Excel file which contains the appropriate module which calls that function

Code

Option Explicit[ATTACH]46705[/ATTACH]




Function DictAppTable(Loop_Range As Integer, Key_Range As Range, Dim_1 As Range, Dim_2 As Range)




Dim DictTable As Dictionary
Dim AppElement(0 To 0, 0 To 1) As Variant
Dim iD As Integer
Dim strAppID As String
Dim strAppIDnum As String
Dim ProtoElement As Variant
Dim intSize As Integer
Dim iK As Integer
Dim MtrElement(0 To 0, 0 To 1) As Variant


Set DictTable = New Dictionary
DictTable.RemoveAll
DictTable.CompareMode = TextCompare


For iD = 1 To Loop_Range
    
    strAppID = Key_Range.Rows(iD).Value
    strAppIDnum = Right(strAppID, 5)
    
    AppElement(0, 0) = strAppID
    AppElement(0, 1) = Dim_2.Rows(iD).Value
    
    MtrElement(0, 0) = Dim_1.Rows(iD).Value
    MtrElement(0, 1) = Dim_2.Rows(iD).Value
 
    
    If DictTable.Exists(strAppID) Then
        DictTable(strAppID)(0, 1) = MtrElement(0, 1) + DictTable(strAppID)(0, 1)
        DictTable(strAppID) = CombineTwoDArrays(DictTable(strAppID), MtrElement)
        
        Else
        ProtoElement = CombineTwoDArrays(AppElement, MtrElement)
                    
        DictTable.Add Key:=strAppID, Item:=ProtoElement


        End If


Debug.Print DictTable(strAppID)(0, 0)
Debug.Print DictTable(strAppID)(0, 1)


Debug.Print DictTable(strAppID)(1, 0)
Debug.Print DictTable(strAppID)(1, 1)


Next iD


arKey = DictTable.Keys

Display More

My apologies for the previous post, as the pared down excel file which I posted had a slight error which prevented it from running

vwankerl · Jun 27th 2012

Re: Dictionary object returning keys before they are added

I downloaded your sample workbook and tested it. It appears that the Add is treating the ProtoElement as two separate items with the same key instead of as 1 object. You might try to force ProtoElement to an actual array rather than a Variant but I don't know if that will do anything or not. I can't find any reference to what the Dict.Add method does with different item types. I will keep looking and maybe test a couple of ideas. I will let you know what I find.

riddley · Jul 9th 2012

Re: Dictionary object returning keys before they are added

Thank you vwankerl,

I have since tried a few different implementations, and it appears that any time that I use that dictionary object, the items are being added before I .add them. Of course this suggests an error in the way that I am implementing them, but they are fairly simple and I believe that I am following the examples faithfully. Here is another, slightly different example.

Code

Dim dTotals As Dictionary
Set dTotals = New Dictionary


dTotals.CompareMode = BinaryCompare


For Each cell In rAppID
    If Not dTotals.Exists(cell) Then
    Set rAppIDCells = Find_Range(cell, rAppID)
    Set rAppIDValues = rAppIDCells.Offset(0, 6)
    dAppIDTotal = WorksheetFunction.Sum(rAppIDValues)
    dTotals.Add Key:=cell.Value, Item:=dAppIDTotal
    End If
Next cell

Display More

For each cell, the first conditional statement is returning a false (key does exist) despite the fact that the dictionary is new. I am completely bumfuzzled.

Thanks,

riddley

Dictionary object returning keys before they are added

Participate now!

Share