How to use Private statement in VBA?

In VBA (Visual Basic for Applications), the Private statement is used to declare variables or procedures that are accessible only within the module where they are declared. This is useful for encapsulating code and keeping parts of your program hidden from other parts of the application.

Here’s how you can use the Private statement in VBA:

Private Variables

To declare a private variable within a module, you use the Private keyword followed by the variable declaration. This variable will only be accessible within the module where it’s declared.

Private x As Integer

Sub ExampleProcedure()
    x = 5
    ' Other code
End Sub

In this example, the variable x is private to the module and cannot be accessed from other modules.

Private Procedures

Similarly, you can declare a subroutine or function as private. This makes the subroutine or function accessible only within the module where it’s declared.

Private Sub MyPrivateSub()
    ' Code for the subroutine
End Sub

Private Function MyPrivateFunction() As Integer
    ' Code for the function
    MyPrivateFunction = 5
End Function

Here, MyPrivateSub and MyPrivateFunction are only accessible within the module.

Key Points

  • Use Private to restrict access to variables and procedures, making them only accessible within the module where they are declared.
  • This is useful for encapsulation and preventing unintentional interactions between different parts of your code.
  • Remember that Private variables or procedures are not accessible from other modules, including standard modules, class modules, or userform modules.

Using Private effectively helps in managing the scope of variables and procedures, making your VBA code more structured and maintainable.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *