具有动态数组的VBA词典 [英] VBA Dictionary with Dynamic Arrays

问题描述

我正在尝试创建一个包含动态数组的动态字典。

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词典的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！