Help with adding collection to class

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: Attachment 10606 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!

I've been a supervisor for Maintenance on a manufacturing line and IMO you have too few Data points. For Example I had to track down times by
Manufacturer, Model,Serial Number, Parts Line, Operator, and Repair Tech, Repair Start Time, and Replaced Part Numbers.

YMMV

I would only use one Collection and I would fill it with User Defined Types

Module Declarations

Code:

---

Option Explicit

Type EQDownLog
  eqDate
  eqManufacturer As String
  eqModel As String
  eqSerial As String 'Or Long as needed
  eqDownTimeStart
  eqDownTimeEnd 'Or eqDownTimeDuration
  eqMainCause
  eqSecondaryCause
End Type

---

Module Code

Code:

---

Sub ReadDataSample()
Dim EQLog As EQDownLog
Dim DTLogs As Collection

For Each Row of data in Given Dates
With EQLog
  eqDate = Column A Value
  eqModel = Column B Value
  eqMainCause = Column C Value
  eqSecondaryCause = Column D Value
  EqDuration = Column D value
End With

DTLogs.Add EQlog
Next Row of data
End Sub

---

Note that when Reading the DTLogs Collection,you don't have to read the values from EQlog in any particular order, For instance on a Downtime by Manufacturing Line report you can write to the report only certain values

Code:

---

Sub Sample Down Time by Line(DTLogs As Collection)
Dim R As Long
Dim i As OLong

For i = 1 to DTLogs.Count
  With Sheets(DTLogs(i).eqLine)
      R = LastRow 'A User Defined Function
      Cells(R, "A") = DTLogs(i).eqDate
      Cells(R, "B") = DTLogs(i).eqDuration
  End With
Next i
End Sub

---

Thanks for the reply!

But I'm afraid I don't quite see how your solution will let me find the total downtimes, sorted by category? That is what I'm hoping to get out of the approach I'm outlining in my first post.

I could e.g. look in the collection created for the processing line with the bridge crane as the key. I'd then be able to get out the total downtime for the crane between the two dates submitted, and could then continue to have a look at the collection contained within its class to further look at what the different causes for the downtime was, and so on.

While I don't think it would be too hard to implement a solution like this using the approach you are suggesting, I'd have to calculate and look by cause while looping through the collection of reports, which I might as well do while looping through the sheet. Or I could have misunderstood completely what it is you are doing in the examples you provided, in which case I'd be very happy if you'd take the time to explain how I get out the values I want.

I doubt whether you need any classes/collections at all.

Code:

---

Sub M_snb()
    sn = Sheets("Badrensk").Cells(1).CurrentRegion
   
    With CreateObject("scripting.dictionary")
        For j = 2 To UBound(sn)
          .Item(sn(j, 2)) = .Item(sn(j, 2)) & vbLf & Join(Array(sn(j, 1), sn(j, 3), sn(j, 4), sn(j, 5)), "_")
        Next
       
        j = 20
        For Each it In .keys
          Sheets("Badrensk").Cells(j, 1).resize(,2) = array(it,Mid(.Item(it), 2))
          j=j+1
        Next
    End With
End Sub

---

And probably a pivot table provides the information you are after. See the attachment.

PseudoCode:

Code:

---

For Each It in Collection
If .eqName = "Crane" Then DownTime = DownTime + .eqDuration
Next It

---

With UDTs any "subvariable" can be used as the "Key," and any other as the value.

I was assuming that you were pulling data from a daily report into several permanent logs from which you wanted to generate cumulative reports.

BTW, I may be off track as I still haven't DL'ed your workbook. :omg2:

Thanks for both of your replies. I am very sorry for not replying sooner, my only excuse is that things got rather hectic, and I had to put this project on hold for a while. It's a feeble excuse for not popping in and thanking you for your contributions, but at least I'm here now :P

Of the solutions you present, it looks like snb's is the one that is closest to what I want to do, but I am having some difficulty reading your code. At any rate, I haven't really worked with pivot tables or dictionaries before, so if I am to do that, I'll have to find some place to read up on the basics on them. Any suggestions on some online resources which would be a good place to start?

Hmm. Even if I have been a bit busy, I have made some progress on the workbook. In this workbook:Attachment 10657, you can see how far I've gotten, which might give you a better idea of what I want to do. As you can see I write the values the user asks for to a hidden sheet, and use those to create Pareto diagrams for the different divisions. Next I want to create one sheet per division showing diagrams describing how the downtime is distributed among their machinery. I was, as I mentioned earlier, originally going to try do this by looking at collections inside the classes of each of the divisions, but using a pivot table instead looks like it might be a viable solution. Especially if someone would be so kind as to add some explanation to the solution snb showed in post 4.

I've also gotten a bit further with getting the classes/collections to work (see next post). The only trouble is that it doesn't count the way I think it should. Are any of you guys able to see where I've gone wrong?

And again, thanks a ton for the help you've already given!

.

Isn't this problem easily solved by using a pivot table? (hard to say without some relevant data)

Unfortunately, neither Collections nor Dictionaries can use your equipment names as keys. Both Objects require the Key to be a single word with no spaces or punctuation.

I suggest inserting an "alias" column before the Utstyr column, and adding a cross reference table to a hidden sheet.

Cross Reference Example:

Alias	Utstyr
Rullebaner1	Rullebaner, løftebord, tippelomme, ch.kran
RullebanerTwo	Rullebaner, løftebord, tippelomme, ch.Other
Grovrens	Grovrens
Monorail_1	Monorail #1
Monorail_Other	Monorail2

I used several variations. Note the allowed use of an Underscore. VBA Considers all the Aliases to be one word.

It is very simple to create and use a Dictionary a cross reference table.The code would use the Alias Key to return the Equipment Name Item.

As snb often says, "Structure Precedes Process."

This simply means that all your workbook sheets should be complete, before you start coding. Note that the sheets don't need data, but must have all Data columns labeled and if a Report sheet, should have all data cells Named.

During the design phase, I like to put columns for every conceivable data, if there is any possible way that data might be used in the future.

If you are going to use UserForms, complete the Sheets first, then the UserForms, Then start coding.

I movedg this thread to the Project Assistance Forum Folder because it is a more appropriate location for it.

Quote:

---

Originally Posted by Jan Karel Pieterse

Isn't this problem easily solved by using a pivot table? (hard to say without some relevant data)

---

Yes, and as I mentioned in my previous post it looks like the solution snb suggested looked very viable. Therefore I asked if someone please could clarify what he was doing in his code, or point me to a place where I can start reading up on pivot-tables. There is a bit of sample data in the sheet "Badrensk". Obviously not as much as there will be once the workbook is in use, but I think it should be enough to at least get a general idea of the layout.

Quote:

---

Originally Posted by SamT

Unfortunately, neither Collections nor Dictionaries can use your equipment names as keys. Both Objects require the Key to be a single word with no spaces or punctuation.

---

Are you certain? I am fairly sure that I've used keys with both punctuation and spaces before, and that is not where I'm having trouble in my procedure anyway. I.e., the macro runs just fine, but my code returns a count different from what I am expecting (there is a more or less working iteration over the collections in the OEE_test workbook, which I made especially to see how the collections behaved in a less complex environment).


In regards to the rest of your post, there isn't really all that much more that is going to go into the workbook. How I was planning on constructing it (and has done so far) was to first create the sheets for entering data, then create the macro to collect data, and finally create the userform which gives the macro its input and the graphs which use the data collected.

This was more or less what I was going to do with the next part of the sheet too. That is, before I am able to collect the data on the individual equipment I don't see how I can easily create the graphs using that information as its basis. I could of course use some dummy information just to get the graphs up, but I don't think that is going to be an issue, once I manage to collect the information I need.

I do appreciate your input though, and would very much like to hear any further input you have to offer.


-edit- Updated workbook with a page showing what I want to automate, in regards to data collection and graphin (see sheets "Stanstider", "Grafar badrensk" and "Grafar"Attachment 10664.
Hmm, come to think of it, it may be easier if I just write the data straight to that sheet, instead of storing it in collections/classes first :P I am still rather curious about what I am doing wrong in regards to those though.

www.contextures.com has lots of Pivot table examples. I highly recommend to leave VBA alone for this problem for now, as I'm sure built-in functionality suffices (and is easier to maintain).

I'll have a look - thanks for all the help and suggestions :)

If you could have or get the Department sheets into a single list on a single sheet, then pivot tables become a lot easier and more useful

I took snb's example, and just copied the data (please don't laugh) without understanding the values and formatted a multi-department report. Making the Department field a PT Page Field allows a single department view. Rearranging the data fields can also help with data analysis

If you're using Excel 2010, then the new Slicers can help make a nice management dashboard

As JKP said, there are examples aplenty.
Excel will allow multiple sheets to consolidate, but I find it more straight-forward to stick with one sheet if I can.

http://www.contextures.com/xlPivot08.html


Paul