Hi! While not a total newbie to to VBA, I haven't really had any formal training in it, and what I have been doing up to now has been fairly simple, so please forgive me if my question is a bit stupid/has a really easy solution.

I have three worksheets, each for a different department, in which the stop-times of various equipment/machines are listed:

  • in column A, the date of the incident is listed.
  • in column B, the name of the equipment is entered
  • in column C, is the main reason the machine stopped
  • in column D, you can find the secondary reason it stopped
  • in column E, you can find the duration of the stop.


Now what I'm trying to do is to loop through column A collecting informtion from the other columns between two given dates.

Then I get the name of the piece of equipment causing the stop and enter it into a collection of "equipment"-classes, each containing the name of the piece of equipment and the amount of stop-time it's caused. Obviously I don't need multiple entries for the individual pieces of equipment, so if it's already in the collection I rather add the extra time to the previous entry. To make this easier I also use the names of the equipment as keys to the collection.

So far, so good. Now, what I am having some trouble achieving is the next step of the procedure. I want each "equipment-class" to contain a collection of main causes for stops, new ones getting added to the collection as it finds them in the spreadsheet. Each cause of stop will also have the amount of minutes it's added to the downtime of the machine as a property. If it already exists, add to previous as above.

I might just add the secondary causes as new collections within the main causes, but at this point I suspect that this shouldn't be too hard if I manage to solve the problem I described in the previous paragraph. Rather it would at this point be more of a question of if the additional information I capture will add to the overall picture or just create clutter, but that's not really a problem you need to consider

I would, however, like to know if you people think that generating all the collections and classes above might cause any problems in regards to the amount of memory the workbook would require? My suspicion is that it shouldn't, but that is purely driven by my gut-feeling, as I don't have much experience working with classes or collections :P

Anyway, my main question is the following: How do I add a collection of objects to a class, and how do I afterwards ensure that it doesn't get duplicate entries?

I'm also attaching the file I'm having trouble finishing: OEE_test.xlsm The code in question is in the class modules and in the module "modTellMinutt". Please note that I'm currently mid-edit in it, so I don't think the macros will currently run. Also it's very sparsely commented (in Norwegian) and most of the variable-names are not in English, so it might be a bit tricky to read.

I am very much looking forward to hearing your feedback!