Unable to iterate Hashtable in VBA (Excel)

Question

I am using Hashtable (link to mscorlib.dll). I fill it with data, I can get any element (as long as I convert the request type to the same type that is stored in the hash table) .ContainsValue / Key - it all works. But I can’t go through it through the "For Everyone" cycle. I tried all the methods that I could find on the Internet (for each element ... where the element is a DictionaryEntry, via GetEnumerator), but no one works - I can scroll through the table through an enumerator, but I can’t get to the key nor the value where the iterator is located. What am I doing wrong? I solved this temporarily by setting the keys to numbers and iterating over these numbers, but this will not work, because, at last, there will be the same number twice ...

PS: I cannot use a dictionary instead of a Hashtable, because I need both ContainsKey and ContainsValue, as well as the ability to retrieve elements using keys or values.

EDIT: my current code (I'm looking to replace the "For i" loop, so I don't need keys to be numbers that 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 a pair of keywords / option _value, some keywords conflict with others, hashtables rules_any / yes / no contain keywords that conflict if the checked one is = YES / NO / SOMETHING and sets validcsc to 0 (aka invalid combination keywords).

---

rands - an array of unique numbers - the problem is that there is more than one conflict keyword, then I do not have a number to set as the key, which I will know later, and use it for iteration

Answer 1

There is actually one way to view the .NET hash table 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 problem is casting the return value of the Keys property into IEnumerable before using it in a for-every loop. VBA cannot handle multiple inheritance of an interface from scratch: you need to pass to the interface to which the function / property belongs before you can call it.

Answer 2

VBA cannot handle the DictionaryEntry type (hashtable element) and the ICollection type (which returns the .Keys method), so the only solution was to write my own library.