First off, kudos for entering the wonderful rabbit hole of OOP!
Short answer: It depends.
(very) long answer:
You'll want to avoid pulling a worksheet [that exists at compile-time] from the Application.Worksheets (or Application.Sheets) collection, and use that sheet's CodeName instead. VBA creates a global-scope object reference for you to use, named after every worksheet's CodeName.
That's how this code gets to compile, with Sheet1 not being declared anywhere:
Option Explicit
Sub Test()
Debug.Print Sheet1.CodeName
End Sub
The problem with implementing worksheet-specific functionality anywhere other than in that worksheet's code-behind, using that global-scope "free" object variable, is that the separate module is now coupled with that Sheet1 object.
Class module depending on a worksheet. Any worksheet.
You want focused, cohesive modules - high cohesion. And low coupling.
By writing worksheet-specific code in another module (be it a standard or a class module), you're creating a dependency and increasing coupling, which reduces testability - consider this code in Class1:
Public Sub DoSomething()
With Sheet1
' do stuff
End With
End Sub
Now Class1 can only ever work with Sheet1. This would be better:
Public Sub DoSomething(ByVal sheet As Worksheet)
With sheet
' do stuff
End With
End Sub
What happened here? Dependency Injection. We have a dependency on a specific sheet, but instead of coding against that specific object, we tell the world "give me any worksheet and I'll do my thing with it". That's at method level.
If a class means to work with a single specific worksheet, and exposes multiple methods that do various things with that worksheet, having a ByVal sheet As Worksheet parameter on every single method doesn't make much sense.
Instead you'll inject it as a property:
Private mSheet As Worksheet
Public Property Get Sheet() As Worksheet
Set Sheet = mSheet
End Property
Public Property Set Sheet(ByVal value As Worksheet)
Set mSheet = value
End Property
And now all methods of that class can work with Sheet... the only problem is that the client code consuming that class now needs to remember to Set that Sheet property, otherwise errors can be expected. That's bad design IMO.
One solution could be to push the Dependency Injection Principle a notch further, and actually depend on abstractions; we formalize the interface we want to expose for that class, using another class module that will act as the interface - that IClass1 class doesn't implement anything, it just defines stubs for what's exposed:
'@Interface
Option Explicit
Public Property Get Sheet() As Worksheet
End Property
Public Sub DoSomething()
End Sub
Our Class1 class module can now implement that interface, and if you've been following this far, hopefully I don't lose you here:
NOTE: Module and member attributes are not visible in the VBE. They're represented here with their corresponding Rubberduck annotations.
'@PredeclaredId
'@Exposed
Option Explicit
Implements IClass1
Private mSheet As Worksheet
Public Function Create(ByVal pSheet As Worksheet) As IClass1
With New Class1
Set .Sheet = pSheet
Set Create = .Self
End With
End Function
Friend Property Get Self() As IClass1
Set Self = Me
End Property
Private Property Get IClass1_Sheet() As Worksheet
Set IClass1_Sheet = mSheet
End Property
Private Sub IClass1_DoSomething()
'implementation goes here
End Sub
This Class1 class module presents two interfaces:
Class1 members, accessible from the PredeclaredId instance:
Create(ByVal pSheet As Worksheet) As IClass1Self() As IClass1
IClass1 members, accessible from the IClass1 interface:
Sheet() As WorksheetDoSomething()
Now the calling code can look like this:
Dim foo As IClass1
Set foo = Class1.Create(Sheet1)
Debug.Assert foo.Sheet Is Sheet1
foo.DoSomething
Because it's written against the IClass1 interface, the calling code only "sees" the Sheet and DoSomething members. Because of the VB_PredeclaredId attribute of Class1, the Create function can be accessed via the Class1 default instance, pretty much exactly like Sheet1 gets accessed without creating an instance (UserForm classes have that default instance, too).
This is the factory design pattern: we're using the default instance as a factory whose role is to create and initialize an implementation of the IClass1 interface, which Class1 just so happens to be implementing.
With Class1 completely decoupled from Sheet1, there's absolutely nothing wrong with having Class1 responsible for everything that needs to happen on whatever worksheet it's initialized with.
Coupling is taken care of. Cohesion is another problem entirely: if you find Class1 is growing hair and tentacles and becomes responsible for so many things you don't even know what it was written for anymore, chances are that the Single Responsibility Principle is taking a beating, and that the IClass1 interface has so many unrelated members that the Interface Segregation Principle is also taking a beating, and the reason for that is probably because the interface wasn't designed with the Open/Closed Principle in mind.
The above couldn't be implemented with standard modules. Standard modules don't play quite well with OOP, which means tighter coupling and thus lower testability.
TL;DR:
There isn't one single "right" way to design anything.
- If your code can deal with being tightly coupled with a specific worksheet, prefer implementing the functionality for that worksheet in that worksheet's code-behind, for better cohesion. Still use specialized objects (classes) for specialized tasks: if your worksheet code-behind is responsible for setting up a database connection, sending a parameterized query over the network, retrieving the results and dumping them into the worksheet, then you're Doing It Wrong™ and now testing that code in isolation, without hitting the database, is impossible.
- If your code is more complex and can't afford tight coupling with a specific worksheet, or if the worksheet doesn't exist at compile-time, implement the functionality in a class that can work with any worksheet, and have a class that's responsible for the model of that runtime-created sheet.
IMO a standard module should only be used to expose entry points (macros, UDFs, Rubberduck test methods, and with Option Private Module, some common utility functions), and contain fairly little code that merely initializes objects and their dependencies, and then it's classes all the way down.
