无法在VBA(Excel)中迭代Hashtable [英] Cannot iterate Hashtable in VBA (Excel)

问题描述

我正在使用Hashtable(已引用mscorlib.dll).我用数据填充它，我可以获得任何项目(只要将请求类型转换为哈希表中存储的完全相同的类型)、. ContainsValue/Key-都可以.但是我无法通过For Each循环对其进行迭代. 我已经尝试了所有可以在互联网上找到的方法(对于每个元素...，其中元素是通过GetEnumerator的DictionaryEntry)，但没有任何效果-我可以通过枚举器浏览表，但无法找到键也没有迭代器位置的值.我究竟做错了什么? 我已经通过将键设置为数字并遍历这些数字来临时解决了该问题，但这将不起作用，因为最终会有相同的数字两次...

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 .:我不能使用Dictionary而不是Hashtable，因为我既需要ContainsKey和ContainsValue，还需要通过键或值来检索项目.

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.

我当前的代码(我正在寻找的是替换"For i"循环，因此我不需要将键设置为我已经知道的数字)

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

代码的作用:

cscs哈希表包含关键字/option_value对，某些关键字与其他关键字冲突，哈希表rules_any/yes/no包含冲突的关键字，如果选中的关键字为YES = NO/NO/SOMETHING并将validcsc设置为0(又称为有效组合)关键字).

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是唯一数字的数组-麻烦的是，如果有多个冲突关键字，那么我没有任何数字可以设置为键，以后我会知道并使用它进行迭代

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

推荐答案

实际上，有一种方法可以从VBA浏览.NET哈希表:

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

主要问题是将"Keys"属性的返回值强制转换"为IEnumerable ，然后在"for each"循环中使用它. VBA无法从头处理多个接口继承:您必须将其强制转换为功能/属性所属的接口.

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.

这篇关于无法在VBA(Excel)中迭代Hashtable的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！