clsWarrantyClaim: one object per claim, with its own behaviour
' ===== Class Module: clsWarrantyClaim =====
Option Explicit
Private mstrFrameNumber As String
Private mdatPurchaseDate As Date
Private mstrFaultDetail As String
Private mblnIsResolved As Boolean
Public Property Get FrameNumber() As String
FrameNumber = mstrFrameNumber
End Property
Public Property Let FrameNumber(ByVal strValue As String)
mstrFrameNumber = strValue
End Property
Public Property Get PurchaseDate() As Date
PurchaseDate = mdatPurchaseDate
End Property
Public Property Let PurchaseDate(ByVal datValue As Date)
mdatPurchaseDate = datValue
End Property
Public Property Get FaultDetail() As String
FaultDetail = mstrFaultDetail
End Property
Public Property Let FaultDetail(ByVal strValue As String)
mstrFaultDetail = strValue
End Property
Public Property Get IsResolved() As Boolean
IsResolved = mblnIsResolved
End Property
Public Sub MarkResolved()
mblnIsResolved = True
End Sub
' DaysRemainingUnderWarranty
' --------------------------
' Ride Me Cycles' standard warranty is 24 months from purchase. Returns
' the number of whole days left before it lapses, or 0 once it already
' has -- never a negative number, so a caller can test "> 0" directly
' instead of also having to check the sign.
Public Function DaysRemainingUnderWarranty() As Long
Const WARRANTY_MONTHS As Integer = 24
Dim datExpiryDate As Date
datExpiryDate = DateAdd("m", WARRANTY_MONTHS, mdatPurchaseDate)
DaysRemainingUnderWarranty = _
Application.WorksheetFunction.Max(0, DateDiff("d", Date, datExpiryDate))
End Function
' ===== Standard Module =====
Option Explicit
' BuildOpenClaimsCollection
' -------------------------
' Reads the Warranty Claims sheet into a Collection of
' clsWarrantyClaim objects, keyed by frame number, so a claim can be
' retrieved directly by colOpenClaims(strFrameNumber) instead of a
' linear search through parallel arrays.
Public Function BuildOpenClaimsCollection() As Collection
Dim colOpenClaims As New Collection
Dim wksClaims As Worksheet
Dim varClaimsData As Variant
Dim lngRowIndex As Long
Dim objClaim As clsWarrantyClaim
Set wksClaims = ThisWorkbook.Worksheets("Warranty Claims")
varClaimsData = wksClaims.Range("A2:D" & _
wksClaims.Cells(wksClaims.Rows.Count, "A").End(xlUp).Row).Value
For lngRowIndex = 1 To UBound(varClaimsData, 1)
Set objClaim = New clsWarrantyClaim
With objClaim
.FrameNumber = varClaimsData(lngRowIndex, 1)
.PurchaseDate = varClaimsData(lngRowIndex, 2)
.FaultDetail = varClaimsData(lngRowIndex, 3)
End With
' Collection keys must be unique -- On Error traps the rare case
' of two claims logged against the same frame number, rather
' than letting the whole build fail on one bad row.
On Error Resume Next
colOpenClaims.Add objClaim, Key:=objClaim.FrameNumber
On Error GoTo 0
Next lngRowIndex
Set BuildOpenClaimsCollection = colOpenClaims
End Function
Every field a claim has lives inside one object now, so sorting, filtering or passing a claim around always carries its frame number, purchase date and fault detail together. There's no longer a second or third array that has to be kept in step by hand.
DaysRemainingUnderWarranty is what a class module adds that four arrays never could: real behaviour attached to the data. Any code holding a claim object can ask it directly, rather than that calculation being copied out at every place it's needed and risking drifting out of sync with itself.
Keying the Collection by frame number means a specific claim is retrieved directly with colOpenClaims(strFrameNumber), rather than looping through every claim to find the one that matches.