Excel VBA object with validated bounds for an interval

Question

I'm working through a problem I have with Excel VBA. I have two object classes. Class clsLoadStep stores three copies of class clsLoad objects. Class clsLoad simply stores a value and a string stating where that value came from. One clsLoad stores a max value and another a min value. My problem comes to read/write access to the nested clsLoad objects in the clsLoadStep. Currently the properties of the clsLoad objects of clsLoadStep can be changed (via the Get property then the Let property of the clsLoad object) which can result in the Max object storing a value less than the Min object. Is there a way to restrict access to the clsLoad objects in the clsLoadStep object to read-only while still letting the definition of the clsLoad class have both read and write properties? I want to be able to read the properties of the clsLoad objects but not change them.

I have a function in the clsLoadStep object that checks if min> max and swaps the object references if that's the case. The clsLoadStep object still needs to be able to tell if the clsLoad object's values have changed. Is there a recommended way to accomplish this?

For a while I had a "Parent" property of the clsLoad object that pointed to the clsLoadStep object. This allowed the clsLoadStep object to know when the clsLoad object changed (by calling the UpdateMaxMin function of the parent). That was a problem though as mulitple objects could reference the clsLoad object and it would be restricted to one parent.

Class clsLoad

Option Explicit
Private pValue As Double
Private pSource As String
'*****************************************************************
'P R O P E R T I E S
'*****************************************************************
'''''''''''''''''''''''''
' Value property
'''''''''''''''''''''''''
Public Property Get Value() As Double
 Value = pValue
End Property
Public Property Let Value(ByVal Val As Double)
 pValue = Val
End Property
'''''''''''''''''''''''''
' Source property
'''''''''''''''''''''''''
Public Property Get Source() As String
 Source = pSource
End Property
Public Property Let Source(ByVal Val As String)
 pSource = Val
End Property

Class clsLoadStep

Option Explicit
Private pMax As clsLoad
Private pMin As clsLoad
Private pOcc As clsLoad
Private pSource As String
'*****************************************************************
'P R O P E R T I E S
'*****************************************************************
'''''''''''''''''''''''''
' Max property
'''''''''''''''''''''''''
Public Property Get Max() As clsLoad
 Set Max = pMax
 UpdateMaxMin
End Property
Public Property Set Max(ByVal newLoad As clsLoad)
 Set pMax = newLoad
 UpdateMaxMin
End Property
'''''''''''''''''''''''''
' Min property
'''''''''''''''''''''''''
Public Property Get Min() As clsLoad
 Set Min = pMin
 UpdateMaxMin
End Property
Public Property Set Min(ByVal newLoad As clsLoad)
 Set pMin = newLoad
 UpdateMaxMin
End Property
'''''''''''''''''''''''''
' Occur property
'''''''''''''''''''''''''
Public Property Get Occ() As clsLoad
 Set Occ = pOcc
End Property
Public Property Set Occ(ByVal newLoad As clsLoad)
 Set pOcc = newLoad
 UpdateMaxMin
End Property
'''''''''''''''''''''''''
' Source property
'''''''''''''''''''''''''
Public Property Get Source() As String
 Source = pSource
End Property
Public Property Let Source(ByVal Val As String)
 pSource = Val
End Property
'''''''''''''''''''''''''
' UpdateMaxMin sub - Swap max and min objects if max is less than min
'''''''''''''''''''''''''
Private Sub UpdateMaxMin()
 Dim tmpLoad As clsLoad
 If pMax.Value < pMin.Value Then
 Set tmpLoad = pMax
 Set pMax = pMin
 Set pMin = tmpLoad
 Debug.Print "Max less than min. Swapped max and min."
 End If
End Sub

Answer 1

---

As for the first issue

Is there a way to restrict access to the clsLoad objects in the clsLoadStep object to read-only while still letting the definition of the clsLoad class have both read and write properties?

I'd say there's only one way to go: remove all Public Property Get ... As clsLoad and substitute them with a couple of Property Get each, as follows:

'''''''''''''''''''''''''
' Max property
'''''''''''''''''''''''''
'''Public Property Get Max() As clsLoad '<--| remove it and replace with MaxValue and MaxSource 
''' Set Max = pMax
'''' UpdateMaxMin
'''End Property
Public Property Get MaxValue() As Double '<--| this will let the user of 'clsLoadStep' class to get the 'Value' property of 'Max' 'clsLoad' object
 MaxValue = GetValue(pMax)
End Property
Public Property Get MaxSource() As String '<--| this will let the user of 'clsLoadStep' class to get the 'Source' property of the 'Max' 'clsLoad' object
 MaxSource = GetSource(pMax)
End Property

where I used the following helper functions:

'helpers
Function GetValue(p As clsLoad) As Double
 If Not p Is Nothing Then GetValue = p.Value
End Function
Function GetSource(p As clsLoad) As String
 If Not p Is Nothing Then GetSource = p.Source
End Function

of course you have to act similarly for Min and Occ properties

---

As for the second issue

The clsLoadStep object still needs to be able to tell if the clsLoad object's values have changed

I'd put this functionality inside a helper function like follows:

Sub SetIt(load As clsLoad, newLoad As clsLoad, oldLoad As clsLoad, name As String)
 Set load = newLoad '<--| set the object
 If Not oldLoad Is Nothing Then '<--| if there was a "previous" object
 If oldLoad.Value <> load.Value Then MsgBox name & " changed" '<--| check if value changed
 End If
 Set oldLoad = load '<--| update the "previous" object
End Sub

of course you have to set some new fields:

Private pMaxOld As clsLoad
Private pMinOld As clsLoad
Private pOccOld As clsLoad

and exploit it as follows:

'''''''''''''''''''''''''
' Max property
'''''''''''''''''''''''''
Public Property Set Max(ByVal newLoad As clsLoad)
 SetIt pMax, newLoad, pMaxOld, "Max"
 UpdateMaxMin
End Property

and the likes

---

Finally the clsLoadStep Class code would be:

Option Explicit
Private pMaxOld As clsLoad
Private pMinOld As clsLoad
Private pOccOld As clsLoad
Private pMax As clsLoad
Private pMin As clsLoad
Private pOcc As clsLoad
Private pSource As String
'*****************************************************************
'P R O P E R T I E S
'*****************************************************************
'''''''''''''''''''''''''
' Max property
'''''''''''''''''''''''''
Public Property Set Max(ByVal newLoad As clsLoad)
 SetIt pMax, newLoad, pMaxOld, "Max"
 UpdateMaxMin
End Property
'''Public Property Get Max() As clsLoad
''' Set Max = pMax
'''' UpdateMaxMin
'''End Property
Public Property Get MaxValue() As Double
 MaxValue = GetValue(pMax)
End Property
Public Property Get MaxSource() As String
 MaxSource = GetSource(pMax)
End Property
'''''''''''''''''''''''''
' Min property
'''''''''''''''''''''''''
Public Property Set Min(ByVal newLoad As clsLoad)
 SetIt pMin, newLoad, pMinOld, "Min"
 UpdateMaxMin
End Property
'''Public Property Get Min() As clsLoad
''' Set Min = pMin
''' UpdateMaxMin
'''End Property
Public Property Get MinValue() As Double
 MinValue = GetValue(pMin)
End Property
Public Property Get MinSource() As String
 MinSource = GetSource(pMin)
End Property
'''''''''''''''''''''''''
' Occur property
'''''''''''''''''''''''''
Public Property Set Occ(ByVal newLoad As clsLoad)
 SetIt pOcc, newLoad, pOccOld, "Occ"
End Property
'''Public Property Get Occ() As clsLoad
''' Set Occ = pOcc
'''End Property
Public Property Get OccValue() As Double
 OccValue = GetValue(pOcc)
End Property
Public Property Get OccSource() As String
 OccSource = GetSource(pOcc)
End Property
'''''''''''''''''''''''''
' Source property
'''''''''''''''''''''''''
Public Property Get Source() As String
 Source = pSource
End Property
Public Property Let Source(ByVal Val As String)
 pSource = Val
End Property
'''''''''''''''''''''''''
' UpdateMaxMin sub - Swap max and min objects if max is less than min
'''''''''''''''''''''''''
Private Sub UpdateMaxMin()
 Dim tmpLoad As clsLoad
 If pMax Is Nothing Or pMin Is Nothing Then Exit Sub
 If pMax.Value < pMin.Value Then
 Set tmpLoad = pMax
 Set pMax = pMin
 Set pMin = tmpLoad
 Debug.Print "Max less than min. Swapped max and min."
 End If
End Sub
'''''''''''''''''''''''''
'helpers
'''''''''''''''''''''''''
Sub SetIt(load As clsLoad, newLoad As clsLoad, oldLoad As clsLoad, name As String)
 Set load = newLoad
 If Not oldLoad Is Nothing Then
 If oldLoad.Value <> load.Value Then MsgBox name & " changed"
 End If
 Set oldLoad = load
End Sub
Function GetValue(p As clsLoad) As Double
 If Not p Is Nothing Then GetValue = p.Value
End Function
Function GetSource(p As clsLoad) As String
 If Not p Is Nothing Then GetSource = p.Source
End Function

---

an example of usage of this class is the following:

Option Explicit
Sub main()
 Dim load1 As clsLoad, load2 As clsLoad, load3 As clsLoad
 Dim loadStep As clsLoadStep
 Set load1 = New clsLoad
 Set load2 = New clsLoad
 Set load3 = New clsLoad
 load1.Source = "source1"
 load1.Value = 1
 load2.Source = "source2"
 load2.Value = 2
 load3.Source = "source3"
 load3.Value = 3
 Set loadStep = New clsLoadStep
 With loadStep
 Set .Max = load1
 Set .Min = load3 '<--| this will trigger the "swap" functionality inside 'clsLoadStep' class
 Set .Max = load2 '<--| this will trigger the check against the previous 'Max' 'clsLoad' object of 'LoadStep' class
 Set .Occ = load2
 MsgBox .MaxValue '<--| this will return '2'
 MsgBox .Max.Value '<--| though still allowed by Intellisense, this will not compile
 End With
End Sub