更新时间:2023-11-04 12:32:34
Sub DICT_OF_DICT()
Dim d1, d2
Set d1 = CreateObject("Scripting.Dictionary")
Set d2 = CreateObject("Scripting.Dictionary")
d1.Add "BPH", "Hello"
d2.Add "Shaun", d1
Debug.Print d2("Shaun").Item("BPH")
End Sub
编辑:如果我想处理使用行/列标题快速访问2-D数组,那么我会倾向于不使用嵌套字典,而是使用两个不同的词典来键入每个维度(行标签字典和列标签)。
if I wanted to deal with quickly accessing a 2-D array using row/column headers then I'd be inclined not to use nested dictionaries, but to use two distinct dictionaries to key into each dimension (a "row label" dictionary and a "column label" one).
您可以将其包装成一个简单的类:
You can wrap this up in a simple class:
'Class module: clsMatrix
Option Explicit
Private dR, dC
Private m_arr
Sub Init(arr)
Dim i As Long
Set dR = CreateObject("Scripting.Dictionary")
Set dC = CreateObject("Scripting.Dictionary")
'add the row keys and positions
For i = LBound(arr, 1) + 1 To UBound(arr, 1)
dR.Add arr(i, 1), i
Next i
'add the column keys and positions
For i = LBound(arr, 2) + 1 To UBound(arr, 2)
dC.Add arr(1, i), i
Next i
m_arr = arr
End Sub
Function GetValue(rowKey, colKey)
If dR.Exists(rowKey) And dC.Exists(colKey) Then
GetValue = m_arr(dR(rowKey), dC(colKey))
Else
GetValue = "" 'or raise an error...
End If
End Function
'EDIT: added functions to return row/column keys
' return a zero-based array
Function RowKeys()
RowKeys = dR.Keys
End Function
Function ColumnKeys()
ColumnKeys = dC.Keys
End Function
示例用法:假设A1是顶部 - 第一行为列标题(col1至colx),第一列为行标题(row1至rowy)的矩形范围单元格 -
Example usage: assuming A1 is the top-left cell in a rectangular range where the first row is column headers ("col1" to "colx") and the first column is row headers ("row1" to "rowy") -
EDIT2 :进行了一些更改,以显示如何管理多个不同的表(没有更改类代码)
EDIT2: made some changes to show how to manage multiple different tables (with no changes to the class code)
'Regular module
Sub Tester()
Dim tables As Object, k
Set tables = CreateObject("Scripting.Dictionary")
tables.Add "Table1", New clsMatrix
tables("Table1").Init ActiveSheet.Range("A1").CurrentRegion.Value
tables.Add "Table2", New clsMatrix
tables("Table2").Init ActiveSheet.Range("H1").CurrentRegion.Value
Debug.Print tables("Table1").GetValue("Row1", "Col3")
Debug.Print tables("Table2").GetValue("R1", "C3")
k = tables("Table1").RowKeys()
Debug.Print Join(k, ", ")
End Sub