将一个参数作为用户定义的数据类型来添加到数据字典集合的子过程。

I am trying to learn how to work with dictionary collections that uses keys. I have a text file which has been delimited by a special character. Using the VBA Macro Recorder, I have learned how to remove consecutive delimiters as well as arranging the columns to prevent the text file coming in with headers in different places.

我正在学习如何使用使用键的字典集合。我有一个被特殊字符分隔的文本文件。通过使用VBA宏记录器，我学会了如何删除连续的分隔符，以及如何排列列，以防止文本文件在不同的地方带有头文件。

The data contains applicant information:

资料包括申请人资料:

Name | Date       | State | University | Age
Ann  | 2015-01-14 | NY    | Boston     | 18
Bob  | 2015-02-11 | MA    | Stanford   | 17

The data is stored on a worksheet, for the sake of this example, on Sheet 1. Starting with the headers on row 1, col 1.

数据存储在工作表中，为了本例，在表1中。从第1行的标题开始，1。

I created a user defined object called Applicant:

我创建了一个用户定义的对象，名为申请人:

Type Applicant
    Name as String
    DateApp as Date
    State as String
    University as String
    Age as Double
End Type

I would like to create a data dictionary 'Applicants' collection to store each instance of Applicant. I am thinking something like this:

我想创建一个数据字典“申请人”集合来存储申请人的每个实例。我在想这样的事情:

Public Sub add_applicant(prsPerson as Applicant)

Is there a way to do this? I understand the basics on using for/each loop to .add to a collection but I have only seen adding single entries being added to a dictionary collection. I would like to expand upon this and add components of the data type from the user defined object. I want single instance of Applicants collection to contain the data for exactly one applicant line.

有办法吗?我了解使用for/each循环到.add to集合的基本知识，但我只看到将单个条目添加到字典集合中。我想对此进行扩展，并从用户定义的对象中添加数据类型的组件。我希望单一的申请者集合实例包含一个申请人行的数据。

Would I do something like this?

我会做这样的事吗?

Dim prsPerson As Applicant
Set prsPerson = New Applicant
With prsPerson
    .Name = "Ann"
    .Age = 18
    .State = "NY"
    .Date = "2015-01-14"
    .University = "Boston"
End With

Applicants.Add Item:=prsPerson

How can I create a for loop that goes through the range block and add for each row, the value in the column (headers such as as Name, Age, State, etc) into the data collection for a given person using the Sub Procedure add_applicant(prsPerson as Applicant)?

如何创建一个for循环，遍历range块并为每一行添加一个for循环，使用子过程add_candidate (prsPerson作为申请人)将列中的值(如名称、年龄、状态等报头)添加到给定人员的数据集合中?

How can I add a key to this? I want the key to be composed of Name. However Ann show up more than once in the data file because she can apply to different schools. Is this allowed? Because I also want to calculate how many times Ann applied etc.

我该怎么添加一个键呢?我希望键由名称组成。然而，Ann不止一次出现在数据文件中，因为她可以申请不同的学校。这是允许的吗?因为我还想计算Ann应用了多少次等等。

1 个解决方案

#1

From what you've described it doesn't sound like you need a dictionary but a collection of Applicants. Collections has optional keys but keys are cleaner with Dictionarys. Either way you can't have different objects for the same key though. You can still loop through the collection and count the number of times Ann comes up.

从你所描述的情况来看，这听起来不像是你需要一本字典，而是一群申请者。集合有可选的键，但是在Dictionarys中键是干净的。不管怎样，同一个键都不能有不同的对象。您仍然可以对集合进行循环，并计算Ann出现的次数。

I've had trouble (using Excel 2007) adding Types to collections though. I get an error of 'Only user-defined types in public object modules can be coerced to or from a variant or passed to late-bound functions'

不过，我遇到了一些麻烦(使用Excel 2007)。我得到的错误是“公共对象模块中只有用户定义的类型可以强制到或从一个变体或传递到后期绑定函数”

You can create an array of Applicants though.

不过，您可以创建一个申请人数组。

dim applicants() as Applicant
redim applicants(0 to 9) 'you may count rows here to get the number you expect to use.

Another alternative to the Type you created is a Class with all the same attributes. There's lots of information out there on how to create a class so I won't go into that here. They are similar to Types but have some differences and more functionality with greater complexity.

您创建的类型的另一种替代方法是具有所有相同属性的类。这里有很多关于如何创建类的信息，所以我不会在这里讲。它们与类型相似，但有一些差异和更复杂的功能。

If you don't want to create a Class and still want to use a collection here's a possible method that uses dictionaries in a different way than you originally intended. You end up with a collection of dictionaries. If you create a class you only have to modified this slightly to change the dictionaries to your new class.

如果您不想创建一个类，但仍然想使用集合，这里有一个可能的方法，它使用字典的方式与您最初的意图不同。最后你得到了一套字典。如果您创建了一个类，您只需稍微修改一下它，将字典更改为您的新类。

Sub test()
    Dim c As Collection
    Dim d As Object
    Dim i As Long
    Dim lastRow As Long

    lastRow = 10 'Change this to fit your data

    Set c = New Collection
    For i = 1 To lastRow
        Set d = CreateObject("Scripting.dictionary")
        d("Name") = Sheets("sheet1").Cells(i, 1)
        d("Date") = Sheets("sheet1").Cells(i, 2)
        'etc
        c.Add d
        Set d = Nothing
    Next

    'to count Ann's applications
    Dim entry As Variant
    Dim appCount As Long

    For Each entry In c
        If entry("Name") = "Ann" Then
            appCount = appCount + 1
        End If
    Next
End Sub