[VBA] How to Centralize Common Logic for Multiple Sheets in ThisWorkbook | Avoid Writing Code for Each Sheet

Background

While creating VBA programs in Excel, I often found it inconvenient to write the same code repeatedly for multiple sheets.

• Duplicate code leads to poor maintainability.
• Every time specifications change, code in all sheets must be corrected.
• Even though I want the same behavior across sheets, the implementation becomes cumbersome.

To solve this, I tried centralizing the processing in the ThisWorkbook module to unify the common logic for all target sheets.

Desired Specifications

• Execute common processing when any sheet from Sheet1 to Sheet10 is activated.
• Execute common processing (defined in a separate module) when cell content is changed in these sheets.
• No need to write code in each individual sheet module.
• Call a common procedure defined in a standard module (e.g., Module1).

Implementation Code (ThisWorkbook)

The following code is written in the ThisWorkbook module.

' Event executed when any sheet is activated
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    If IsTargetSheet(Sh.Name) Then
        ' Call the specific procedure (define as needed)
        SheetActivated 
    End If
End Sub

' Event executed when a cell changes in any sheet
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If IsTargetSheet(Sh.Name) Then
        ' Call the common processing defined in the standard module
        kokoni_program Target 
    End If
End Sub

' Function to determine if the sheet is a target
Function IsTargetSheet(sheetName As String) As Boolean
    ' Return True if the sheet name matches any of the target names
    IsTargetSheet = (sheetName = "Sheet1" Or _
                     sheetName = "Sheet2" Or _
                     sheetName = "Sheet3" Or _
                     sheetName = "Sheet4" Or _
                     sheetName = "Sheet5" Or _
                     sheetName = "Sheet6" Or _
                     sheetName = "Sheet7" Or _
                     sheetName = "Sheet8" Or _
                     sheetName = "Sheet9" Or _
                     sheetName = "Sheet10")
End Function

Supplement: Configuration of the Called Module

The common processing is defined in a Standard Module (e.g., Module1) as follows:

Public Sub kokoni_program(Target As Range)
    MsgBox "Value changed at: " & Target.Address
End Sub

Customize the processing content as needed.

Benefits of This Configuration

• Centralized Management: Writing in ThisWorkbook allows for managing code in one place.
• Easy Maintenance: Adding or removing target sheets is easy (just update the IsTargetSheet function).
• High Reusability: There is no need to write separate code for each sheet, improving maintainability.

Summary

When you want to apply common VBA processing to multiple sheets, writing it in ThisWorkbook is efficient.

• You can limit processing to specific sheets using conditional branching (If).
• Defining common logic in a standard module results in a clean structure that is easy to maintain.

Please try utilizing this configuration when managing processes across multiple sheets in Excel VBA.