Class module / multidimensional array?

[swoop99]

New to class modules....not sure I really understand them properly.

What I'm trying to do is create a list of engineers, log the dates each has worked and produce a count of jobs per day to calculate an average with.

So....what I was thinking of was something like an array of Engineers(1 to 200) as string and two other variables, one being WorkDay(1 to 10000) date and JobDay(1 to 10000) as integer

However, a co-worker suggested using a class module instead:

cFranchise 

Private cName As String
Private cWorkDay(1 To 10000) As Date
Private cJobDay(1 To 10000) As Integer

Public Property Get Name() As String
    Name = cName
End Property
Public Property Let Name(Value As String)
    cName = Value
End Property


Public Property Get WorkDay() As Date
    WorkDay = cWorkDay
End Property
Public Property Let WorkDay(Value As Date)
    cJobDay = Value
End Property


Public Property Get JobDay() As Integer
    WorkDay = cWorkDay
End Property
Public Property Let JobDay(Value As Integer)
    cJobDay = Value
End Property

Now, I've managed to get it to create multiple instances of the class:

Dim Engineers As Collection
Dim Engineer As cFranchise
Set Engineers = New Collection

For Sweep = 6 To 14
    Set Engineer = New cFranchise
    Engineers.Add Engineer
    Engineer.Name = Cells(Sweep, 5).Value
Next Sweep

And I can read them back in sequential order:

For Each Engineer In Engineers
    MsgBox Engineer.Name
Next Engineer

Now comes the complete bonehead question:

How do I reference an individual instance of the class in order to assign values to the other variables?
Like.....if code is scanning through a data set, finds the engineer name, searches the collection for a matching engineer name......how does it assign the date worked to WorkDay(n) of that particular instance?

Or am I barking up the wrong tree completely and this can be done easily with a multidimensional array (also new to me).


Google has only managed to get me this far....

[Andy Pope]

You need to include an Index parameter in order to specify which element in the array you want to read or write to.

Private cName As String
Private cWorkDay(1 To 10000) As Date
Private cJobDay(1 To 10000) As Integer

Public Property Get Name() As String
    Name = cName
End Property
Public Property Let Name(Value As String)
    cName = Value
End Property


Public Property Get WorkDay(Index As Long) As Date
    WorkDay = cWorkDay(Index)
End Property
Public Property Let WorkDay(Index As Long, Value As Date)
    cJobDay(Index) = Value
End Property


Public Property Get JobDay(Index As Long) As Integer
    WorkDay = cWorkDay(Index)
End Property
Public Property Let JobDay(Index As Long, Value As Integer)
    cJobDay(Index) = Value
End Property

    Dim Engineers As Collection
    Dim Engineer As cFranchise
    Set Engineers = New Collection
    
    For Sweep = 6 To 14
        Set Engineer = New cFranchise
        Engineers.Add Engineer
        Engineer.Name = Cells(Sweep, 5).Value
        Engineer.JobDay(1) = 1
        Engineer.WorkDay(1) = Date
    Next Sweep

The use of JobDay and WorkDay are hard coded in the example as I don't know where you have that data

[swoop99]

Thanks Andy, still not getting it though I'm afraid.

I get the indexing - need that to access the array within the class. How do I access the indexed array JobDay(n) for one particular engineer, ie one specific instance of the class?


Say I've got 10 instances of the class, each with a different engineer.name
Each instance has got multiple entries in the array JobDay(n) with a corresponding value in the WorkDay(n) array.

What I need to do is get the next engineers name from the pre-existing dataset held on a worksheet, search through all the instances of Engineers until I find the one with Engineer.Name = whatever and then search through the JobDay(n) array for that instance to see if there's already an entry matching the date worked from the dataset.....if there is, add 1 to the WorkDay(n) value and if not, add the date worked to the next empty value in the JobDay(n) array and stick a 1 in the same WorkDay(n).

[Andy Pope]

Not following your explanation 100%.

If you use the name, assuming they are unique, as the key then you can set a reference like this.

Sub Test()

    Dim Engineers As Collection
    Dim Engineer As cFranchise
    Set Engineers = New Collection
    
    
    For Sweep = 6 To 14
        Set Engineer = New cFranchise
        
        ' user name as key
        Engineers.Add Engineer, Cells(Sweep, 5).Value
        
        Engineer.Name = Cells(Sweep, 5).Value
        Engineer.JobDay(1) = 1
        Engineer.WorkDay(1) = Date
    Next Sweep
    
    
    Set Engineer = Engineers("Swoop99")
    Engineer.JobDay(1) = 1
    Engineer.WorkDay(1) = Date
    
End Sub

[swoop99]

Not following your explanation 100%.

What I mean is, when I'm searching through WorkDay(n) to find a matching date, how to I ensure I'm searching through the correct instance of WorkDay(n) and not the WorkDay(n) array of another engineer?

[Kyle123]

I suspect a collection/dictionary approach may be more useful:

Option Explicit
Private oDays As Object

Public Sub AddDate(Value As Date)
    Dim sDate As String
    sDate = CStr(Value)
    If oDays.Exists(sDate) Then
        oDays(sDate) = oDays(sDate) + 1
    Else
        oDays.Add sDate, 1
    End If
End Sub

Public Property Get DatesWorked() As Object
    Set DatesWorked = oDays
End Property


Private Sub Class_Initialize()
    Set oDays = CreateObject("Scripting.Dictionary")
End Sub

Sub test()
    Dim oEngineers As Collection
    Dim oEngineer As cEngineer
    Dim vDatesWorked
    Dim vDays
    Dim x As Long
    
    Set oEngineers = New Collection
    
    For x = 1 To 10
        Set oEngineer = New cEngineer
        oEngineers.Add oEngineer, "Kyle" & x
    Next x
    
    oEngineers("Kyle1").AddDate #5/1/2013#
    oEngineers("Kyle1").AddDate #5/2/2013#
    oEngineers("Kyle1").AddDate #5/1/2013#
    oEngineers("Kyle1").AddDate #5/4/2013#
    
    vDatesWorked = oEngineers("Kyle1").DatesWorked.Keys
    vDays = oEngineers("Kyle1").DatesWorked.Items
End Sub

[Kyle123]

Though to be honest, I'd use a pivot table

[swoop99]

This is what I've come up with:

Private cName As String
Private cWorkDay(1 To 10000) As Date
Private cJobDay(1 To 10000) As Integer
Private cWorkingDays As Integer


Public Property Get Name() As String
    Name = cName
End Property
Public Property Let Name(Value As String)
    cName = Value
End Property


Public Property Get WorkDay(Index As Long) As Date
    WorkDay = cWorkDay(Index)
End Property
Public Property Let WorkDay(Index As Long, Value As Date)
    cJobDay(Index) = Value
End Property


Public Property Get JobDay(Index As Long) As Integer
    WorkDay = cWorkDay(Index)
End Property
Public Property Let JobDay(Index As Long, Value As Integer)
    cJobDay(Index) = Value
End Property

Public Property Get WorkingDays() As String
    WorkingDays = cWorkingDays
End Property
Public Property Let WorkingDays(Value As String)
    cWorkingDays = Value
End Property

Dim Sweep As Long
Dim Engineers As Collection
Dim Engineer As cFranchise
Set Engineers = New Collection
Dim Dsweep As Long

For Sweep = 6 To 14
    Set Engineer = New cFranchise
    Engineers.Add Engineer
    Engineer.Name = Cells(Sweep, 5).Value
Next Sweep

For Sweep = 2 To Range("JobData!A1048000").End(xlUp).Row
    For Each Engineer In Engineers
        If Engineer.Name = Range("JobData!BB" & Sweep).Value Then
            If Engineer.WorkingDays < 1 Then
                Engineer.WorkingDays = 1
                Engineer.WorkDay(1) = Range("JobData!X" & Sweep).Value
                Engineer.JobDay(1) = 1
            Else
                For Dsweep = 1 To Engineer.WorkingDays
                    If Engineer.WorkDay(Dsweep) = Range("JobData!X" & Sweep).Value Then
                        Engineer.JobDay(Dsweep) = Engineer.JobDay(Dsweep) + 1
                        Exit For
                    End If
                    If Dsweep = Engineer.WorkingDays Then
                        Engineer.WorkingDays = Engineer.WorkingDays + 1
                        Engineer.WorkDay(Dsweep) = Range("JobData!X" & Sweep).Value
                        Engineer.JobDay(Dsweep) = 1
                    End If
                Next Dsweep
            End If
            Exit For
        End If
    Next Engineer
Next Sweep

However, I'm getting an overflow error trying to add the very first value here:

Engineer.WorkDay(1) = Range("JobData!X" & Sweep).Value

[Kyle123]

Doesn't mine do that?

[swoop99]

I'm still trying to decipher yours.... :-)

[swoop99]

Think I've cracked it.....I had the Class module definitions completely screwed up. Should be:

Private cName As String
Private cWorkDay(1 To 10000) As Date
Private cJobDay(1 To 10000) As Integer
Private cWorkingDays As Integer


Public Property Get Name() As String
    Name = cName
End Property
Public Property Let Name(Value As String)
    cName = Value
End Property


Public Property Get WorkDay(Index As Long) As Date
    WorkDay = cWorkDay(Index)
End Property
Public Property Let WorkDay(Index As Long, Value As Date)
    cWorkDay(Index) = Value
End Property


Public Property Get JobDay(Index As Long) As Integer
    JobDay = cJobDay(Index)
End Property
Public Property Let JobDay(Index As Long, Value As Integer)
    cJobDay(Index) = Value
End Property

Public Property Get WorkingDays() As String
    WorkingDays = cWorkingDays
End Property
Public Property Let WorkingDays(Value As String)
    cWorkingDays = Value
End Property

[Kyle123]

With mine, unless I've missed something (proably )

class cEngineer:

Option Explicit
Private oDays As Object
Private sEngineerName As String

Public Sub AddDate(value As Date)
    Dim sDate As String
    sDate = CStr(value)
    If oDays.Exists(sDate) Then
        oDays(sDate) = oDays(sDate) + 1
    Else
        oDays.Add sDate, 1
    End If
End Sub

Public Property Get DatesWorked() As Object
    Set DatesWorked = oDays
End Property

Public Property Get Name() As String
    Name = sEngineerName
End Property
Public Property Let Name(value As String)
    sEngineerName = value
End Property

Private Sub Class_Initialize()
    Set oDays = CreateObject("Scripting.Dictionary")
End Sub

Then:

Sub sample()

    Dim Sweep As Long
    Dim Engineers As Collection
    Dim Engineer As cEngineer
    
    Set Engineers = New Collection
    
    
    For Sweep = 6 To 14
        Set Engineer = New cEngineer
        Engineer.Name = Cells(Sweep, 5).value
        Engineers.Add Engineer, Engineer.Name
    Next Sweep
    
    
    For Sweep = 2 To Range("JobData!A1048000").End(xlUp).Row
        Engineers(Range("JobData!BB" & Sweep).value).AddDate Range("JobData!X" & Sweep).value
    Next Sweep

End Sub

[Kyle123]

Though this would be faster for a lot of data:

Sub sample()

    Dim Sweep As Long
    Dim Engineers As Collection
    Dim Engineer As cEngineer
    
    Dim vNames, vDates
    Dim lr As Long
    
    Set Engineers = New Collection
    
    
    For Sweep = 6 To 14
        Set Engineer = New cFranchise
        Engineer.Name = Cells(Sweep, 5).value
        Engineers.Add Engineer, Engineer.Name
    Next Sweep
    
    With Sheets("JobData")
        lr = .Range("A1048000").End(xlUp).Row
        vNames = Range("BB2:BB" & lr).value
        vDates = Range("X2:X" & lr).value
    End With
    
    
    For Sweep = 2 To lr
        Engineers(vNames(Sweep,1)).AddDate vDates(Sweep,1)
    Next Sweep

End Sub

But I reckon a pivot table would be even quicker

[swoop99]

I bow to your superior knowledge....but since I don't understand pivot tables either I'll stick with what works and move on to the next thing I don't understand.

[Kyle123]

Have a look at the attached. I reckon that there's a case for saying that pivot tables are the single most useful tool that Excel provides - once you suss them, you'll wonder how you ever did anything without them