Code Within the Same File with Excel VBA

First, let’s consider an example demonstrating how procedures and functions can be defined and used within the same VBA project file.

The following code is located in the standard code module named Module1:

Sub ModularSameFile()
    ' Calling procedure from another standard module
    Summe1 3, 5
    MsgBox "Sum 2: " & Summe2(3, 5)    
    ' Calling procedure and function from a class module associated with the worksheet object "Sheet1"
    Sheet1.Summe3 3, 5
    MsgBox "Sum 4: " & Sheet1.Summe4(3, 5)
End Sub

Next, in another standard code module named Module2, we have the following procedure and function:

Sub Summe1(a As Integer, b As Integer)
    MsgBox "Summe 1: " & (a + b)
End Sub

Function Summe2(a As Integer, b As Integer) As Integer
    Summe2 = a + b
End Function

Finally, within the class module associated with the worksheet object named Tabelle1, the following procedure and function are defined:

Sub Summe3(a As Integer, b As Integer)
    MsgBox "Summe 3: " & (a + b)
End Sub

Function Summe4(a As Integer, b As Integer) As Integer
    Summe4 = a + b
End Function

Explanation:

By default, procedures and functions defined in standard code modules have Public visibility, unless explicitly declared as Private Sub or Private Function. This means that the procedure Summe1() and the function Summe2() defined in Module2 can be directly called from anywhere within the VBA project, including from Module1 as shown in ModularGleicheDatei().

In contrast, procedures and functions within class modules (such as the one tied to the worksheet object Sheet1) have Private visibility by default. Consequently, the procedure Summe3() and the function Summe4() defined in the class module cannot be called directly by their name alone. Instead, you must prefix the call with the class module’s object name—in this case, Sheet1.—to access them. For example, you call Sheet1.Summe3 and Sheet1.Summe4 to invoke these members.

This distinction is important because it controls the scope and accessibility of code elements, ensuring encapsulation when working with object-oriented structures like class modules, while allowing more open access in standard modules.