There should be code in VBA that allows excluding document in class module

First, the prestige to enter the wonderful PLO rabbit hole!

Short answer: It depends.

(very) long answer:

You need to avoid pulling a worksheet [that exists at compile time] from the collection Application.Worksheets

(or Application.Sheets

) and use that worksheet CodeName

. VBA creates a reference to a global scale object that you must use, named after each worksheet CodeName

.

The way this code will compile is Sheet1

not declared anywhere:

Option Explicit

Sub Test()
    Debug.Print Sheet1.CodeName
End Sub

      

        
        
        
      

    

The problem with implementing only worksheet functionality other than that worksheet code using this "free" variable of the global scope object is that a separate module is now associated with that object Sheet1

.

Class module depending on the worksheet. Any worksheet.

You want focused, cohesive modules - high cohesion. And low communication.

By writing a specific worksheet in another module (be it a standard one or a class module), you create a dependency and increase the linkage, which reduces testability - consider this code in Class1

:

Public Sub DoSomething()
    With Sheet1
        ' do stuff
    End With
End Sub

      

        
        
        
      

    

Now Class1

can only work with Sheet1

. It 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 that specific object, we tell the world to "give me some worksheet and I'll do my thing with it." This is at the method level.

If a class means working with one specific sheet and provides multiple methods that perform different actions on that sheet, having a parameter ByVal sheet As Worksheet

for each individual method doesn't make much sense.

Instead, you'll be injecting 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 the methods of this class can work with Sheet

... The only problem is that the client code consuming this class must now remember Set

that the property is Sheet

, otherwise errors can be expected. This is bad design IMO.

One solution might be to take the Dependency Injection Principle further and actually depend on abstractions; we formalize the interface we want to expose for this class using another class module that will act as an interface - the class IClass1

doesn't implement anything, it just defines stubs for what is displayed:

'@Interface
Option Explicit

Public Property Get Sheet() As Worksheet
End Property

Public Sub DoSomething()
End Sub

      

        
        
        
      

    

Our class module Class1

can now implement this interface, and if you've followed this far, hopefully I don't lose you here:

NOTE. Module and member attributes are not visible in VBE. They are presented here with the 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 class module Class1

presents two interfaces:

• Class1
  
  available from instance PredeclaredId
  
  :
  • Create(ByVal pSheet As Worksheet) As IClass1
    
    
  • Self() As IClass1
    
    
• IClass1
  
  accessible from the interface IClass1
  
  :
  • Sheet() As Worksheet
    
    
  • DoSomething()
    
    

Now the calling code might look like this:

Dim foo As IClass1
Set foo = Class1.Create(Sheet1)
Debug.Assert foo.Sheet Is Sheet1
foo.DoSomething

      

        
        
        
      

    

Because it is written against an interface IClass1

, the calling code only "sees" the Sheet

and members DoSomething

. Because of the attribute VB_PredeclaredId

Class1

, a function Create

can be accessed through a Class1

default instance , much like Sheet1

it can be accessed without instantiation (the UserForm classes also have this default instance).

This is the factory design pattern: we use the default instance as a factory, whose role is to create and initialize the implementation of an interface IClass1

that Class1

just happens.

If Class1

completely disconnected from Sheet1

, there is absolutely nothing wrong with being in Class1

charge of everything that needs to happen on any sheet that it initialized with.

Deals with communication. Cohesion is another problem: if you find yourself Class1

growing hairs and tentacles and becoming responsible for so many things that you don't even know about, which he wrote more about, chances are that the principle of single responsibility will hit, and that the interface IClass1

has there are so many unrelated members that the Segregation Principle also takes a beating, and the reason for this is probably because the interface was not designed with Open / Closed in mind.

The above cannot be implemented with standard modules. Standard modules don't work very well with OOP, which means tighter coupling and hence lower testability.

TL; DR:

There is more than one "correct" way to create something.

• If your code can be tightly coupled to a particular worksheet , prefer to implement the functionality for that worksheet in that worksheet for better cohesion . Still use specialized objects (classes) for specialized tasks: If your working code is responsible for setting up a database connection, sending a parameterized query over the network, fetching the results and dumping them into a worksheet, then you are doing it Wrong ™ and now testing it code in isolation, without getting into the database, is impossible.
• If your code is more complex and cannot provide a close relationship to a specific worksheet or if the worksheet does not exist at compile time, implement the functionality in a class that can work with any worksheet and they have a class responsible for the model of that worksheet created at run time.

IMO a standard module should only be used to expose entry points (macros, UDFs, Rubberduck test methods and Option Private Module

some common utility functions) and contain quite a bit of code that just initializes objects and their dependencies, and then it goes away completely.