问题描述
我正在尝试创建一个包含动态数组的动态字典。
I'm trying to create A dynamic dictionary that contains dynamic arrays.
电子表格中的示例行:
Facility Name|Contact Name|Contact Role
关系设施和联系人之间是M2M。我想重新创建一个看起来像这样的工作表:
The relationship between facilities and contacts are M2M. I would like to recreate a sheet that looks like this:
Contact Name| Facility1 - role, Facility2 - role
我想做的是创建一个具有唯一名称的字典用作键的名称
What I would like to do is create a dictionary of names with unique names serving as keys
New Dictionary Names(name)
Names(name)的值将是显示该名称的所有行号的数组。例如,说乔罗斯出现在第3、7和9行:
The values for Names(name) will be an array of all the row numbers where this name appears. For instance, say "Joe Rose" appears in rows 3, 7 and 9:
names("Joe Rose") = [3,7,9]
我知道如何在JS,Python,PHP中做到这一点,但是VBA让我发疯了!
I know how I could do this in JS, Python, PHP, but VBA is driving me crazy!
这是到目前为止我所能得到的:
Here is what I kind of got so far:
Dim names As Dictionary
Set names = New Dictionary
Dim name
For i=1 To WorkSheets("Sheet1").Rows.Count
name = WorkSheets("Sheet1").Cells(i,2)
If Not names(name) Then
names(name) = i
Else
'help!
'names(name)) push new i, maybe something with redim preserve?
End If
Next i
即使只是将我指向某篇文章,可以参考会很棒! VBA源自PHP背景,实在令人沮丧!
Even just pointing me to some article that I could reference would be great! VBA has been so frustrating coming from a PHP background!
谢谢
推荐答案
这有点棘手,因为您必须从字典中拉出数组才能使用它,然后放回去:
It's a bit tricky since you have to pull the array out of the Dictionary to work with it, then put it back:
Sub Tester()
Dim names As Dictionary
Set names = New Dictionary
Dim name, tmp, ub, i, k
For i = 1 To Worksheets("Sheet1").UsedRange.Rows.Count
name = Trim(Worksheets("Sheet1").Cells(i, 2).Value)
If Len(name) > 0 Then
If Not names.Exists(name) Then
names(name) = Array(i)
Else
tmp = names(name)
ub = UBound(tmp) + 1
ReDim Preserve tmp(0 To ub)
tmp(ub) = i
names(name) = tmp
End If
End If
Next i
For Each k In names.Keys
Debug.Print k, Join(names(k), ",")
Next k
End Sub
这篇关于具有动态数组的VBA词典的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!