How do you create nested classes in VBA Classes in Excel?

[mLennon]

Hi guys,
Sorry, again a newbie that has very limited skills in programming.

I work for a company that produces some 'widgets'. We work a 10 hour shift and we record how much we produce each hour.

I planned to capture the shift in a Class - cls_shiftClass which will store details about the shift, eg. date, start time, plant operator, shift supervisor etc. Then inside the shift Class to also include another class for the hours work - cls_hourClass. Inside the hourClass, I was storing the start time, end time, raw material in, product material out, and any rejects amongst other things. I planned to add 10 hours...
simply:

set hour1 = new cls_hourClass
set hour2 = new cls_hourClass
etc...

I thought this was a better model so that I could perform calculations based on the hour, a 2hr grouping, half shift and full shift all by using the raw hourly data.

My problem is I know how to create a custom class and set properties on that, I don't know where to start to declare a class with properties inside a class... and how to access the properties of the nested class.

I have tried this in my cls_shiftClass:


Private Sub Class_Initialize()
Debug.Print "Initialising the cls_shiftClass"
Set hour1 = New cls_hourData
hour1.rejectWidgets = 81
Me.ProductionData.Add hour1


Debug.Print Me.ProductionData(0) & " was the value in rejectWidgets in the Shift Module" ' // no data returned
Debug.Print Me.hour1.rejectWidgets & " was the value in rejectWidgets in the Shift Module" ' // 0 was the value in the rejectWidgets
End Sub



I don't know where to begin.

Appreciate any guidance.

Thanks.
M

[mLennon]

Hi guys,

It looks like it was just trying to do the set property before the class was properly initialised... thanks.

[Paul_Hossler]

Some other things to consider. I only did one property as demo, but you can finish it off


Standard module:




Option Explicit
Sub Demo()
Dim i As Long

Dim DayShift As clsShift

Set DayShift = New clsShift

With DayShift
.ShiftDate = #12/25/2016#

.Hour(1).RawMaterial = 12345
.Hour(2).RawMaterial = 23456

MsgBox .ShiftDate

For i = 1 To 10
MsgBox .Hour(i).RawMaterial
Next I
End With

End Sub





Class Module: clsHour



Option Explicit

Private m_Start As Date
Private m_End As Date
Private m_RawMaterial As Double
Private m_Product As Double
Private m_Rejects As Double

Private Sub Class_Initialize()
m_RawMaterial = 0#
m_Product = 0#
m_Rejects = 0#
End Sub
Property Get RawMaterial() As Double
RawMaterial = m_RawMaterial
End Property
Property Let RawMaterial(R As Double)
m_RawMaterial = R
End Property





Class module: clsShift



Option Explicit
Private m_Hours(1 To 10) As clsHour
Private m_Date As Date
Private m_Start As Date
Private m_Operator As String
Private m_Supervisor As String

Private Sub Class_Initialize()
Dim i As Long

For i = LBound(m_Hours) To UBound(m_Hours)
Set m_Hours(i) = New clsHour
Next i
End Sub
Property Get ShiftDate() As Date
ShiftDate = m_Date
End Property
Property Let ShiftDate(D As Date)
m_Date = D
End Property
Property Get Hour(n As Long) As clsHour
Set Hour = m_Hours(n)
End Property
Property Let Hour(n As Long, H As clsHour)
Set m_Hours(n) = H
End Property

[SamT]

I thought this was a better model so that I could perform calculations based
on the hour, a 2hr grouping, half shift and full shift all by using the raw
hourly data.

Always looking ahead, I would treat the situation as three different problems



Data Input
Data Storage
Data Analysis


Data input can be handled with one or more UserForms

Data Storage with one or more Worksheet Tables

Analysis with Excel BI tools in one or more Workbooks.


First design the Data Storage, as that will set the parameters for both Data Entry and Data Analysis. Then I personally, would do the Data Entry Forms and fill half a dozen lines of data needed for each analysis step. This tests the Data Entry portion and provides enough real data to test the Data Analysis section.

By keeping Data Entry and Storage in a separate book than the Analysis portion, many Users can analyze the data at the same time. It also allows keeping Lists of fixed input values for User convenience.

Maintaining, say, Product lines is as easy as editing a list.

If, later, you decide you need a new Data Field, say, Eye Color of Team Lead, You can add it to the Data Storage and not affect any other operation. To put it in use you can Code the UserForm to handle any number of Controls with the same code. Well... Maybe adding the new Control Name to a Collection or array.