Excel VBA Code - Global Module/Sub OOP friendly?

Question

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

Why don't you work with named ranges instead of hardcode them? — FunThomas, Mar 15 '21 at 14:05

Tim Williams · Answer 1 · 2021-03-15T17:08:04.570

This

Set rngPartSize = Range("C5")

has a flaw in that it doesn't have a worksheet qualifier, so will return a range from whatever happens to be the active sheet.

Your "common" code should also not need to be initialized any time you want to use a part of it - that's a little awkward to have those calls spread over your code.

If you want to be "object-oriented" then a better approach (IMO) is to:

First: Change the codename(s) of your worksheet(s) instead of constantly needing to run things like:

Set wsData = ThisWorkbook.Worksheets("Data")

you can just refer directly to Data, Lists etc in your code.

Next: use the worksheet code modules to define methods which "belong" to those sheets. A good example of this would be an "Inputs" sheet where various parameters are entered. So say you have "Account Number" you can put this in your worksheet module for Inputs:

Const RNG_ACCT_NUM As string = "D10"

Property Get Accountnumber()
    Accountnumber = Me.Range(RNG_ACCT_NUM).Value
End Property
Property Let Accountnumber(v)
    Me.Range(RNG_ACCT_NUM).Value = v
End Property

Now you can use this in your code:

Dim acct
acct = Inputs.AccountNumber
'or
Inputs.AccountNumber = "AB3456"

instead of

Dim wsInputs As Worksheet, acct 
Set wsInputs = ThisWorkbook.Worksheets("Inputs")

acct = wsInputs.Range("D10")
'....
wsInputs.Range("D10") = "AB3456"

Excel VBA Code - Global Module/Sub OOP friendly?

1 Answers1