Cannot iterate Hashtable in VBA (Excel)

Question

I am using Hashtable (mscorlib.dll referenced). I fill it with data, I can get any item (as long as I convert the request type to exactly same type what's stored in hashtable), .ContainsValue/Key - all that works. But I cannot iterate through it via For Each loop. I've tried all methods I could find on the internet (For Each element..., where element is DictionaryEntry, via GetEnumerator), but none works - I can roll through the table via enumerator, but I can't reach the key nor value of where the iterator is positioned. What am I doing wrong? I've solved it temporarily by setting keys to numbers and iterating over those numbers, but it won't work, because finally there will be the same number twice...

P.S.: I can't use Dictionary instead of Hashtable, because I need both ContainsKey and ContainsValue and also being able to retrieve items by keys or by values.

EDIT: my current code (what I am looking for is to replace "For i" loop, so I don't need keys to be numbers I already know)

For i = 1 To UBound(rands)
        chopped_yes = Split(ThisWorkbook.Worksheets(1).Range("Z" & rands(i)))
        chopped_no = Split(ThisWorkbook.Worksheets(1).Range("AA" & rands(i)))
        chopped_any = Split(ThisWorkbook.Worksheets(1).Range("AB" & rands(i)))
        For Each part In chopped_yes
            If rules_yes.ContainsValue(cscs.item(CLng(rands(i)))) Then
                validcsc = 0
                GoTo WriteIt
            End If
        Next part
        For Each part In chopped_no
            If rules_no.ContainsValue(cscs.item(CLng(rands(i)))) Then
                validcsc = 0
                GoTo WriteIt
            End If
        Next part
        For Each part In chopped_any
        pepa = cscs.item(CLng(rands(i)))
        chopped_pepa = Split(pepa, "=")
            If rules_any.ContainsValue(CStr(chopped_pepa(0))) Then
                validcsc = 0
                GoTo WriteIt
            End If
        Next part
    Next i

What the code does:

cscs hashtable contains keyword/option_value pair, some keywords conflict with others, hashtables rules_any/yes/no contain keywords which conflict, if the checked one is keyword=YES/NO/SOMETHING and set validcsc to 0 (aka invalid combination of keywords).

rands is array of unique numbers - trouble is if there is more than one conflict keyword, then I don't have any number to set as key which I will know later and use it to iterate

I ended up writing my own library, because VBA cannot handle DictionaryEntry type (elemen of hashtable) nor ICollection type (that's what .Keys method returns). — Destabilizator
– Destabilizator, Commented Jan 6, 2012 at 7:32

Julien Crozon · Accepted Answer · 2013-03-09 17:05:31Z

7

Actually there is one way to browse a .NET hashtable from VBA :

Dim hash As mscorlib.Hashtable
Set hash = New mscorlib.Hashtable

Call hash.Add("foo", "bar")
Call hash.Add(4, 8)

Dim keys As IEnumerable
Set keys = hash.keys

Dim key As Variant
For Each key In keys

    Dim value As Variant

    value = hash.Item(key)
Next

The main issue being "casting" the return value of the property "Keys" into a IEnumerable before using it in a "for each" loop. VBA can not handle multiple interface inheritance from scratch: you have to cast to the interface the function/property belongs to before you can call it.

answered Mar 9, 2013 at 17:05

Julien Crozon

1161 silver badge3 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

omegastripes Over a year ago

Note, you have to add the reference to mscorlib.dll in VBA project (browse for mscorlib.tlb).

Destabilizator · Accepted Answer · 2012-02-01 05:23:14Z

1

VBA cannot handle DictionaryEntry type (element of hashtable) nor ICollection type (that's what .Keys method returns), so only solution was to write my own library.

answered Feb 1, 2012 at 5:23

Destabilizator

5056 silver badges24 bronze badges

Collectives™ on Stack Overflow

Cannot iterate Hashtable in VBA (Excel)

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related