Should I use collection or dictionary for vba?

Question

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

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

You can use dictionary and yes you loop through dictionary...Check this question and answer: https://stackoverflow.com/questions/21432222/can-i-loop-through-key-value-pairs-in-a-vba-collection — MD Ismail Hosen, Sep 16 '20 at 04:30
Actually you need array of dictionaries. That could be array of collections, however dictionary is faster than collection and the only advantage of collection to insert new elements at any position isn't engaged. I would also try ADODB [query data from file](https://stackoverflow.com/a/34518882/2165759) or [disconnected recordset](https://stackoverflow.com/a/36189658/2165759), you may fetch data by filtering then. — omegastripes, Sep 16 '20 at 04:47

Mathieu Guindon · Accepted Answer · 2020-09-16T05:16:25.820

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

This is the way to go! One of the great things about this is that you get IntelliSense for the class fields — horst, Sep 16 '20 at 08:07
If I used Train ID and Station ID, how I get the value with two keys? Another key, and value inside a value? — JUN, Sep 16 '20 at 08:56
@JUN a composite key would have to be concatenated together... — Mathieu Guindon, Sep 16 '20 at 12:10

Should I use collection or dictionary for vba?

1 Answers1