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