Results 1 to 14 of 14

Thread: Help with adding collection to class

  1. #1

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

  2. #2
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    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.


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

    Module Declarations
    Option Explicit
    Type EQDownLog
       eqManufacturer As String
       eqModel As String
       eqSerial As String 'Or Long as needed
       eqDownTimeEnd 'Or eqDownTimeDuration
    End Type
    Module 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

    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

  3. #3
    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.

  4. #4
    Knowledge Base Approver VBAX Guru
    Apr 2012
    I doubt whether you need any classes/collections at all.

    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)), "_")
            j = 20
            For Each it In .keys
               Sheets("Badrensk").Cells(j, 1).resize(,2) = array(it,Mid(.Item(it), 2))
        End With
    End Sub
    And probably a pivot table provides the information you are after. See the attachment.
    Attached Files Attached Files
    Last edited by snb; 09-24-2013 at 04:38 AM.

  5. #5
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    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.
    Please take the time to read the Forum FAQ

  6. #6
    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:OEE.xlsm, 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!

  7. #7
    Attached Files Attached Files

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

    Jan Karel Pieterse
    Excel MVP

  9. #9
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    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.
    Please take the time to read the Forum FAQ

  10. #10
    Moderator VBAX Wizard SamT's Avatar
    Oct 2006
    Near Columbia
    I movedg this thread to the Project Assistance Forum Folder because it is a more appropriate location for it.
    Please take the time to read the Forum FAQ

  11. #11
    Quote Originally Posted by Jan Karel Pieterse View Post
    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 View Post
    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"OEE.xlsm.
    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.
    Last edited by EirikDaude; 10-03-2013 at 08:29 PM. Reason: Attached file

  12. #12 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).

    Jan Karel Pieterse
    Excel MVP

  13. #13
    I'll have a look - thanks for all the help and suggestions

  14. #14
    VBAX Wizard
    Apr 2007
    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.

    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts