CODEWITHSUNDEEP
arraysvbadictionarydata-structurescollections
JUN
JUN

Reputation: 5

Should I use collection or dictionary for vba?

I have multiple datasets and sheets of data of these(excel).

image

It's train time tables

depart  a       a      a      a
arrive  t       t      t      t
Train   z1      z2     z3     z4
station
a                                               
as  6:30:00 7:47:00 8:18:00 9:45:00
b                                               
bs  6:33:00 7:50:00 8:21:00 9:48:00
c   6:35:00 7:52:00 8:23:00 9:50:00
cs  6:35:30 7:52:30 8:23:30 9:50:30

I try to put the data into collection or dictionary To pull out data (mainly time) by train and station, or by time and station etc.

For dictionary, Seems like I need nested dictionary

For collection, may I loop through all item by criteria?

Can anyone give me a hint what method to use for getting data(time or station or train)?

Any advice would be appreciated. Thank you

Upvotes: 0

Views: 2078

Answers (1)

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

Whether to put them in a Collection or a Dictionary depends on how you intend to retrieve them afterwards. Start by describing the data of a single record using public fields in a class module:

Option Explicit
Public TrainID As String
Public DepartureStationID As String
Public DepartureUTC As Date
Public ArrivalStationID As String
Public ArrivalUTC As Date

Now you can write a function that can parse a worksheet Range into a single instance of that class, then a procedure that runs this function for each interesting Range to parse (I'm not sure I'm reading the data correctly but that would be the general idea).

If you plan to iterate them all and run some method in a loop, use a Collection (and a For Each loop).

If you plan to retrieve them by ID, then you could use the TrainID or DepartureStationID field as a dictionary key, and have each "item" be a Collection of instances of that class (lest you'll run into duplicate key issues).

If you plan to parse a bunch of data sources and aggregate them into a queryable dataset, you only need a Collection to store the objects you're parsing; you'll be iterating that collection when you dump these objects onto a worksheet/table for pivoting and PowerQuery-ing =)

Upvotes: 1

Related Questions