有没有一个类似字典的对象，允许我将数组存储为键？

Question

假设我在Excel中有某种类型的ListObject，如下所示：

KeyCol1     KeyCol2     KeyCol3     ValueCol1
Chevy       Lumina      2003        $75
Chevy       Camaro      2018        $50
Dodge       Charger     2004        $13
Toyota      Camry       2015        $35

我想创建一个类似字典的对象，如下所示(psuedocode)：

Dim dict As Object
Set dict = CreateObject("Scripting.Dictionary")
dict.Add [Chevy, Lumina, 2003], $75
dict.Add [Chevy, Camaro, 2018], $50
dict.Add [Dodge, Charger, 2004], $13
dict.Add [Toyota, Camry, 2015], $35

从本质上讲，我想要KeyCol1、KeyCol2、KeyCol3、ValueCol1的键、值对

但是字典不能有键的数组，所以我有点卡住了。有没有什么东西可以让我获得字典的O(1)性能，但是将数组作为“键”？

谢谢大家。

Answer 1

您可以将数组元素连接成一个字符串，并将其用作键。根据实际的键，您可能需要使用分隔符，以便最终字符串的哪一部分与哪个键相关。

只是为了好玩，你也可以创建一个字典树。为此，您可以使用以下函数：

Sub AddNested(dict As Object, keys As Variant, value As Variant)
    Dim parent As Object
    Dim i As Long
    Dim key As String

    Set parent = dict
    For i = LBound(keys) To UBound(keys) - 1
        key = keys(i)
        If Not parent.Exists(key) Then
            parent.Add key, CreateObject("Scripting.Dictionary")
        End If
        Set parent = parent(key)
    Next
    parent.Add keys(UBound(keys)), value
End Sub

Function GetNested(dict As Object, keys As Variant)
    Dim parent As Object
    Dim i As Long
    Dim key As String

    Set parent = dict
    For i = LBound(keys) To UBound(keys) - 1
        key = keys(i)
        If Not parent.Exists(key) Then
            Exit Function
        End If
        Set parent = parent(key)
    Next
    GetNested = parent(keys(UBound(keys)))
End Function

显示如何添加到此结构并从中读取的示例：

Dim dict As Object
Dim i As Long

Set dict = CreateObject("Scripting.Dictionary")
AddNested dict, Array("Chevy", "Lumina", 2003), 75
i = GetNested(dict, Array("Chevy", "Lumina", 2003))
Debug.Print i ' = 75

这里的优点是，单个键将其数据类型保留在数据结构中:例如，数字键保持数字。

更通用

如果还需要将值与部分组合键相关联，则以上内容将不够用。在这种情况下，创建一个真正的树，其中每个节点都可以有一个值和子节点。这可以通过更改上述Sub and函数来完成，如下所示：

Sub AddNested(dict As Object, keys As Variant, value As Variant)
    Dim parent As Object
    Dim key As String
    Dim children As Object

    Set parent = tree
    For Each key In keys
        If Not parent.Exists("Children") Then
            parent.Add "Children", CreateObject("Scripting.Dictionary")
        End If
        Set children = parent("Children")
        If Not children.Exists(key) Then
            children.Add key, CreateObject("Scripting.Dictionary")
        End If
        Set parent = children(key)
    Next
    If parent.Exists("Value") Then parent.Remove "Value"
    parent.Add "Value", value
End Sub

Function GetNested(dict As Object, keys As Variant)
    Dim parent As Object
    Dim key As String
    Dim children As Object

    Set parent = tree
    For Each key In keys
        If Not parent.Exists("Children") Then Exit Function
        Set children = parent("Children")
        If Not children.Exists(key) Then Exit Function
        Set parent = children(key)
    Next
    GetNested = parent("Value")
End Function

Answer 2

使用ParamArray参数将这3个值连接到一个字符串。正如iby @trincot所提到的，唯一分隔符的想法很好：

Option Explicit

Sub TestMe()

    Dim dict As Object
    Set dict = CreateObject("Scripting.Dictionary")
    dict.Add addToString("Chevy", "Lumina", "2003"), 75
    dict.Add addToString("Chevy", "Camaro", "2018"), 50
    dict.Add addToString("Dodge", "Charger", "2004"), 13

    If dict.exists("uniqueChevyuniqueLuminaunique2003") Then
        Debug.Print dict("uniqueChevyuniqueLuminaunique2003")
    End If

End Sub

Public Function addToString(ParamArray myVar() As Variant) As String

    Dim cnt     As Long
    Dim val     As Variant
    Dim delim   As String: delim = "unique"

    For cnt = LBound(myVar) To UBound(myVar)
        addToString = addToString & delim & myVar(cnt)
    Next cnt

End Function

在添加到字典之前，最好检查给定的键是否存在。dict.Exists(key)。

ParamArray的思想是，您可以提供任意多的参数。