next
01-15-2014, 09:37 AM
I have several objects, one is Crew class that keeps track of a company that does a service, the other one is Service class. Service class keeps track of bigger picture, so I need to be able to add multiple Crew objects to the same Service object. The problem is that I keep getting "invalid use of property" error.
This is the line that creates the error: Service.Crews Crew and below is full code.
Sub test()
Dim cell As Range, techs As Single, multiTech As Boolean
Dim Crew As cCrew, Service As cService, Equipment As cEquipment
multiTech = False
Set Crews = New Collection
Set Crew = New cCrew
Set Cleanway = New cCleanway
Set Service = New cService
' create a crew for each line item
For Each cell In Range("Labor_Info")
If IsEmpty(cell) Then Exit For
Set Crew = New cCrew
With Crew
.Code = cell
.ServiceStart = cell.Offset(0, 2)
.ServiceEnd = cell.Offset(0, 3)
.InitialRate = cell.Offset(0, 8)
.AdditionalRate = cell.Offset(0, 9)
.TechCount = cell.Offset(0, 1)
End With
Service.Crews Crew
' update sheet values
cell.Offset(0, 4) = Crew.CalculateHours
cell.Offset(0, 5) = Crew.TotalServiceTime
Next cell
MsgBox "ok"
End Sub
Crew Class
Option Explicit
Private mCode As String
Private mTechCount As Single
Private mServiceStart As Date
Private mServiceEnd As Date
Private mInitialRate As Double
Private mAdditionalRate As Double
Private mTripCharge As Double
Private mBonus As Double
Private mNewService As Boolean
' get / set new or continuous service flag
Public Property Let NewService(value As Boolean)
mNewService = value
End Property
Public Property Get NewService() As Boolean
NewService = mNewService
End Property
' get / set crew code
Public Property Let Code(value As String)
mCode = value
End Property
Public Property Get Code() As String
Code = mCode
End Property
' get / set number of techs
Public Property Let TechCount(value As Single)
mTechCount = value
End Property
Public Property Get TechCount() As Single
TechCount = mTechCount
End Property
' get set service start
Public Property Let ServiceStart(value As Date)
mServiceStart = value
End Property
Public Property Get ServiceStart() As Date
ServiceStart = mServiceStart
End Property
' get set service end
Public Property Let ServiceEnd(value As Date)
mServiceEnd = value
End Property
Public Property Get ServiceEnd() As Date
ServiceEnd = mServiceEnd
End Property
' get / set first hour / 2 hours crew rate
Public Property Let InitialRate(value As Double)
mInitialRate = value
End Property
Public Property Get InitialRate() As Double
InitialRate = mInitialRate
End Property
' get / set additional rate charges
Public Property Let AdditionalRate(value As Double)
mAdditionalRate = value
End Property
Public Property Get AdditionalRate() As Double
AdditionalRate = mAdditionalRate
End Property
'get / set trip crew charge
Public Property Let TripCharge(value As Double)
mTripCharge = value
End Property
Public Property Get TripCharge() As Double
TripCharge = mTripCharge
End Property
' get / set money bonus
Public Property Let Bonus(value As Double)
mBonus = value
End Property
Public Property Get Bonus() As Double
Bonus = mBonus
End Property
' calculate hours spent doing the service and round it to the nearest quarter
Public Property Get CalculateHours() As Double
Dim rawTime As Single, result As Single, roundedTime As Single
Dim base As Single, remainder As Single
'calculate service time
rawTime = DateDiff("n", mServiceStart, mServiceEnd)
result = Round(rawTime / 60, 2)
base = WorksheetFunction.Floor(result, 1)
remainder = result - base
'round to the nearest quarter
Select Case (remainder)
Case 0 To 0.12
roundedTime = base
Case 0.12 To 0.37
roundedTime = base + 0.25
Case 0.38 To 0.62
roundedTime = base + 0.5
Case 0.63 To 0.86
roundedTime = base + 0.75
Case Else
roundedTime = base + 1
End Select
CalculateHours = roundedTime
End Property
' calculate total time on site
Public Property Get TotalServiceTime() As Integer
TotalServiceTime = CalculateHours * mTechCount
End Property
Service class:
Option Explicit
Private mDescription As String
Private oCrews As New Collection
' get / set service description
Public Property Let Description(value As String)
mDescription = value
End Property
Public Property Get Description() As String
Description = mDescription
End Property
'get / set service crew
Public Property Set Crews(value As cCrew)
Dim Crew As New cCrew
Set Crew = value
oCrews.Add Crew
End Property
Public Property Get Crews() As Collection
Set Crews = oCrews
End Property
Public Property Get FullServiceTime() As Single
FullServiceTime = Range("Total_Hours").value
End Property
Thank you.
This is the line that creates the error: Service.Crews Crew and below is full code.
Sub test()
Dim cell As Range, techs As Single, multiTech As Boolean
Dim Crew As cCrew, Service As cService, Equipment As cEquipment
multiTech = False
Set Crews = New Collection
Set Crew = New cCrew
Set Cleanway = New cCleanway
Set Service = New cService
' create a crew for each line item
For Each cell In Range("Labor_Info")
If IsEmpty(cell) Then Exit For
Set Crew = New cCrew
With Crew
.Code = cell
.ServiceStart = cell.Offset(0, 2)
.ServiceEnd = cell.Offset(0, 3)
.InitialRate = cell.Offset(0, 8)
.AdditionalRate = cell.Offset(0, 9)
.TechCount = cell.Offset(0, 1)
End With
Service.Crews Crew
' update sheet values
cell.Offset(0, 4) = Crew.CalculateHours
cell.Offset(0, 5) = Crew.TotalServiceTime
Next cell
MsgBox "ok"
End Sub
Crew Class
Option Explicit
Private mCode As String
Private mTechCount As Single
Private mServiceStart As Date
Private mServiceEnd As Date
Private mInitialRate As Double
Private mAdditionalRate As Double
Private mTripCharge As Double
Private mBonus As Double
Private mNewService As Boolean
' get / set new or continuous service flag
Public Property Let NewService(value As Boolean)
mNewService = value
End Property
Public Property Get NewService() As Boolean
NewService = mNewService
End Property
' get / set crew code
Public Property Let Code(value As String)
mCode = value
End Property
Public Property Get Code() As String
Code = mCode
End Property
' get / set number of techs
Public Property Let TechCount(value As Single)
mTechCount = value
End Property
Public Property Get TechCount() As Single
TechCount = mTechCount
End Property
' get set service start
Public Property Let ServiceStart(value As Date)
mServiceStart = value
End Property
Public Property Get ServiceStart() As Date
ServiceStart = mServiceStart
End Property
' get set service end
Public Property Let ServiceEnd(value As Date)
mServiceEnd = value
End Property
Public Property Get ServiceEnd() As Date
ServiceEnd = mServiceEnd
End Property
' get / set first hour / 2 hours crew rate
Public Property Let InitialRate(value As Double)
mInitialRate = value
End Property
Public Property Get InitialRate() As Double
InitialRate = mInitialRate
End Property
' get / set additional rate charges
Public Property Let AdditionalRate(value As Double)
mAdditionalRate = value
End Property
Public Property Get AdditionalRate() As Double
AdditionalRate = mAdditionalRate
End Property
'get / set trip crew charge
Public Property Let TripCharge(value As Double)
mTripCharge = value
End Property
Public Property Get TripCharge() As Double
TripCharge = mTripCharge
End Property
' get / set money bonus
Public Property Let Bonus(value As Double)
mBonus = value
End Property
Public Property Get Bonus() As Double
Bonus = mBonus
End Property
' calculate hours spent doing the service and round it to the nearest quarter
Public Property Get CalculateHours() As Double
Dim rawTime As Single, result As Single, roundedTime As Single
Dim base As Single, remainder As Single
'calculate service time
rawTime = DateDiff("n", mServiceStart, mServiceEnd)
result = Round(rawTime / 60, 2)
base = WorksheetFunction.Floor(result, 1)
remainder = result - base
'round to the nearest quarter
Select Case (remainder)
Case 0 To 0.12
roundedTime = base
Case 0.12 To 0.37
roundedTime = base + 0.25
Case 0.38 To 0.62
roundedTime = base + 0.5
Case 0.63 To 0.86
roundedTime = base + 0.75
Case Else
roundedTime = base + 1
End Select
CalculateHours = roundedTime
End Property
' calculate total time on site
Public Property Get TotalServiceTime() As Integer
TotalServiceTime = CalculateHours * mTechCount
End Property
Service class:
Option Explicit
Private mDescription As String
Private oCrews As New Collection
' get / set service description
Public Property Let Description(value As String)
mDescription = value
End Property
Public Property Get Description() As String
Description = mDescription
End Property
'get / set service crew
Public Property Set Crews(value As cCrew)
Dim Crew As New cCrew
Set Crew = value
oCrews.Add Crew
End Property
Public Property Get Crews() As Collection
Set Crews = oCrews
End Property
Public Property Get FullServiceTime() As Single
FullServiceTime = Range("Total_Hours").value
End Property
Thank you.