Log in

View Full Version : Custome objects and Collections Classes. Help Please!!!



dblock247
01-27-2011, 08:29 AM
Im new here im have coded before but this is my first time with vba. What i am trying to accomplish is this. I want two custom objects and collections classes. The first being tickets the second employees. Needsless to say i will be tracking the tickets and the employees who write them as well as all sorts of states that are accumulated from the tickets writting.

To record the ticket its just a simple spreadsheet with ticket number the employee who wrote it, price ect. Now what i want to have is an employee object and a ticket object. They both will be put into collections (employee collection and tickets collection). I am having trouble getting every thing to work properly can you look at my code and explain to be what im doin wrong.

Custome employee class properties:

Public empID As String
Public empName As String
Public empRate As Double
Public empIRate As Double
Public empRoll As Integer
Public empDW As Double
Public empPAI As Double
Public empSLP As Double
Public empTickets As Integer

Cutome ticket properties:

Public tktNumber
Public tktType
Public tktSource
Public tktRate
Public tktDW
Public tktPAI
Public tktSLP
Public tktSU
Public tktGAS
Public tktRAP
Public tktGPS
Public tktCORP
Public tktEID

Custome Employee collection class:

Option Explicit
Private AllEmployees As New Collection

Public Sub Add(recEmployee As clsEmployee)
AllEmployees.Add recEmployee, recEmployee.empID
End Sub

Public Property Get Count() As Long
Count = AllEmployees.Count
End Property

Public Property Get Items() As Collection
Set Items = AllEmployees
End Property

Public Sub Remove(myItem As Variant)
AllEmployees.Remove (myItem)
End Sub

Custom ticket collection class:

Option Explicit
Private AllTickets As New Collection

Public Sub Add(recTicket As clsTicket)
AllTickets.Add recTicket, recTicket.tktNumber
End Sub

Public Property Get Count() As Long
Count = AllTickets.Count
End Property

Public Property Get Items() As Collection
Set Items = AllTickets
End Property

Public Sub Remove(myItem As Variant)
AllTickets.Remove (myItem)
End Sub

Tracker class used to manipulate other custom objects

Sub tktAddCollection()
Dim colTickets As New clsTickets
Dim recTicket As New clsTicket
Dim LastRow As Integer, myCount As Integer
Dim tktArray As Variant

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
tktArray = ActiveSheet.Range(Cells(3, 1), Cells(LastRow, 4))

For myCount = 1 To UBound(tktArray)
Set recTicket = New clsTicket
With recTicket
.tktNumber = tktArray(myCount, 1)
.tktType = tktArray(myCount, 2)
.tktSource = tktArray(myCount, 3)
.tktRate = tktArray(myCount, 4)
colTickets.Add recTicket
MsgBox "Ticket num: " & .tktNumber
End With
Next myCount

MsgBox "Total Number of tickets is" & colTickets.Count

Set recTicket = Nothing
End Sub

Sub EmpAddCollection()
Dim colEmployees As New clsEmployees
Dim recEmployee As New clsEmployee
Dim LastRow As Integer, myCount As Integer
Dim empArray As Variant

Dim employees As Worksheet, empInfo As Range
Set employees = ThisWorkbook.Worksheets("Employees")
Set empInfo = employees.Range("A2:B20")

LastRow = ActiveSheet.Cells(ActiveSheet.Rows.Count, 1).End(xlUp).Row
empArray = ActiveSheet.Range(Cells(3, 1), Cells(LastRow, 4))

For myCount = 1 To UBound(empArray)
Set recEmployee = New clsEmployee
With recEmployee
.empID = empArray(myCount, 1)
.empName = empArray(myCount, 2)
colEmployees.Add recEmployee
MsgBox "Employee num: " & .empID
End With
Next myCount

MsgBox "Total Number of tickets is" & colTickets.Count

Set recTicket = Nothing
End Sub

I would be very grateful for some help please!!!

Bob Phillips
01-28-2011, 01:39 AM
Where is the code that loads the object classes?

dblock247
01-28-2011, 04:48 AM
The last two sub routines load both classes and creates collections for them. If I am doin it wrong please give alternative examples

Bob Phillips
01-28-2011, 05:58 AM
Post your workbook with data, and I will work it up from that.

dblock247
01-29-2011, 04:07 AM
i have to post 5 times before i can put a link so this is just bs post

dblock247
01-29-2011, 04:09 AM
Just figured out how to post a file on here

What im trying to do is make a daily tracker that has those objects in them. then i would like to make a master tracker that would looking into all those daily trackers and put together stats from a specific period of time which i will tell it. With some kind of calender selection for the date.

Thank you very much for your help in advance

IBihy
02-21-2011, 08:59 AM
Hi dblock247,

do I understand you correctly:
You are trying to build an object hierarchy, similar to this:



__________________ ________________ ___________
! MasterTracker ! 1...n ! DailyTracker ! 1...n ! Employees!
! !<------ ! !<---- ! !
------------------ ---------------- ------------
A
| ____________
| 1...n ! Ticket !
--------------! !
------------

The simple figure above is derived from what you wrote an what I found in your workbook (henceforth called WB).
In words: You have employees and tickets. Each day you want to know how many tickets were processed by each employee. At any given point of time you would like to track the totals of tickets per employee.

Here I would not want to analyze and debug code yet, I would go back a step:

Are you sure that your object model is correct?

It also helps a lot, when you jot down the use cases, what is the code supposed to do, from a user's eagle view.

Even though Excel does seduce you to quick and dirty programming, a bit of planning doesn't do any harm.
From what I know about OO development, I come up with this:
Nouns in singular of the text above: employee, ticket, day, total, point of time.
Verbs of the text above: want to know, process, track.

For me, the basic "object" in this case is the ticket, identified by whom it was processed (employee) and the day it was processed. So, quite rudimentarily, the ticket has two properties: employee_id, date_processed.
Of course, add anything that identifies the ticket completely.
When the "Ticket" will be created (usually by the "new" method), this information identifying a ticket uniquely will have to be passed. As "side-kick" you have the object "Employee", which might have the properties Emp_Name, Emp_Number, Emp_Id, and then some. How will this object need to be created to identify it uniquely?

If you really want a collection object "DailyTickets" (never call an object by verbs, objects have the nouns, the methods have the words), this beast is identified by a certain date. I wouldn't create an object "MasterTracker", first of all, the name is wrong, see above, but I would want to have the "Main" routine run across all daily tickets within a date range.

I'm not an OO guru. But I'm a preacher for good requirements definition.
Ok, before anybody else beats me up, the above said is only a wake-up call rather than the complete requirements engineering for this case.

Regards, Isabella

IBihy
02-21-2011, 09:13 AM
Arrrgghhh, the graph is a mess. View attachment for my idea.