Excel VBA Code - Global Module/Sub OOP friendly?

[Uhndun20]

I've been working on some Excel VBA code with the hope of making it expandable, maintainable and easy to read. Some variables in the code require unique hardcoded ranges while others store common/repeated information. I moved the common/repeat code to a global module to reduce the number of times it needs to be defined with the same information, but I'm concerned it will make it too difficult to track the code or leave the code open to errors later by having public variables. Is this a good way to go about coding this or is there a more efficient/user friendly way to make it OOP?

Snippet below.

Module: mCommon

    Option Explicit
    
    Public wrkshtInput As Object
    Public rngPartSize As Range
    Public rngPart2Size As Range
    
    Sub CommonDefinitions()
    
        Set wrkshtInput = Worksheets("INPUT (BOM)")
        Set rngPartSize = Range("C5")
        Set rngPartSize2 = Range("C6")
    End Sub

Module: mUI

    Option Explicit

    Sub PartToggle()
    'OBJECT REF(S):     Sheet2 (INPUT (BOM))
    'METHOD REF(S):     mCommon.CommonDefinitions
    'VARIABLE REF(S):   mCommon.wrkshtInput, mCommon.rngPartSize

        'COMMON VARIABLE DEFINITIONS:
        Call mCommon.CommonDefinitions
    
        'DEFINE VARIABLES:
        Set rngBlueACM = Range("H129:H134, H136:H141, H144:H147")
        Set rngRedACM = Range("H149:H154, H156:H161, H164:H167")
    
        'PART TOGGLE: ON
        If mCommon.wrkshtInput.tglbtnPartToggle.Value = True Then
            mCommon.rngPartSize.Value = ""
            rngBlueACM.Value = "MANUAL"
            rngRedACM.Value = "MANUAL"
        End If
    
        'PART TOGGLE: OFF
        If mCommon.wrkshtInput.tglbtnPartToggle.Value = False Then
            mCommon.rngPartSize.Value = "--"
            rngBlueACM.Value = "--"
            rngRedACM.Value = "--"
        End If
        
    End Sub

    Sub Part2Toggle()
    'OBJECT REF(S):     Sheet2 (INPUT (BOM))
    'METHOD REF(S):     mCommon.CommonDefinitions
    'VARIABLE REF(S):   mCommon.wrkshtInput, mCommon.rngPart2Size
    
        'COMMON VARIABLE DEFINITIONS:
        Call mCommon.CommonDefinitions
    
        'DEFINE VARIABLES
        Set rngWhiteACM = Range("H107:H108")
    
        'PART2 TOGGLE: ON
        If mCommon.wrkshtInput.tglbtnPart2Toggle.Value = True Then
            mCommon.rngPart2Size.Value = ""
            rngWhiteACM.Value = "MANUAL"
        End If
    
        'PART2 TOGGLE: OFF
        If mCommon.wrkshtInput.tglbtnPart2Toggle.Value = False Then
            mCommon.rngPart2Size.Value = "--"
            rngWhiteACM.Value = "--"
        End If
        
    End Sub

 

[vw412]

You could convert your mCommon module to a class module and gain some security since all common values can be considered as readonly if there is only the Get property sub. In the mUI you don't need to create a new clsCommon variable if you use the "with" construct shown, although it might be more code efficient to instantiate such a variable if the "with" construct doen't work for you. Note, you declare Option Explicit as is recommended, but you do not explicitly declare any variables. See following code:

this is in class module clsCommon

Option Explicit

Public Property Get wrkshtInput() As Object
  Set wrkshtInput = Worksheets("INPUT (BOM)")
End Property

Public Property Get rngPartSize() As Range
  Set rngPartSize = Range("C5")
End Property

Public Property Get rngPartSize2() As Range
  Set rngPartSize2 = Range("C6")
End Property

this is the modified mUI module

    Option Explicit
    
    Sub PartToggle()
    'OBJECT REF(S):     Sheet2 (INPUT (BOM))
    'METHOD REF(S):     mCommon.CommonDefinitions
    'VARIABLE REF(S):   mCommon.wrkshtInput, mCommon.rngPartSize
    
        'DEFINE VARIABLES:
        Set rngBlueACM = Range("H129:H134, H136:H141, H144:H147")
        Set rngRedACM = Range("H149:H154, H156:H161, H164:H167")
    
        'PART TOGGLE: ON
        With New clsCommon
          If .wrkshtInput.tglbtnPartToggle.Value = True Then
            .rngPartSize.Value = ""
            rngBlueACM.Value = "MANUAL"
            rngRedACM.Value = "MANUAL"
          End If
    
        'PART TOGGLE: OFF
          If .wrkshtInput.tglbtnPartToggle.Value = False Then
            .rngPartSize.Value = "--"
            rngBlueACM.Value = "--"
            rngRedACM.Value = "--"
          End If
        End With
        
    End Sub

    Sub Part2Toggle()
    'OBJECT REF(S):     Sheet2 (INPUT (BOM))
    'METHOD REF(S):     mCommon.CommonDefinitions
    'VARIABLE REF(S):   mCommon.wrkshtInput, mCommon.rngPart2Size
    
        'DEFINE VARIABLES
        Set rngWhiteACM = Range("H107:H108")
    
        'PART2 TOGGLE: ON
        With New clsCommon
          If .wrkshtInput.tglbtnPart2Toggle.Value = True Then
            .rngPart2Size.Value = ""
            rngWhiteACM.Value = "MANUAL"
          End If
    
        'PART2 TOGGLE: OFF
          If .wrkshtInput.tglbtnPart2Toggle.Value = False Then
            .rngPart2Size.Value = "--"
            rngWhiteACM.Value = "--"
          End If
        End With
    End Sub

 

[vw412]

Was my suggestions helpful? Feedback makes helping much more rewarding.