jeudi 2 avril 2015

Sub procedure that takes an argument as a user defined data type to add to data dictionary collection

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.


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.


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.


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)?


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.


Aucun commentaire:

Enregistrer un commentaire