The Option Private statement in VBA (Visual Basic for Applications) is used to limit the visibility of a module’s contents to the project in which the module resides. When you use Option Private at the beginning of a module, it prevents the module’s contents (procedures, functions, and variables) from being visible to other projects. This statement is particularly useful when you are creating a VBA add-in (like an Excel Add-In) and you want to hide certain functions or procedures from other projects or workbooks that might reference your add-in. Here’s how to use Option Private:

Place at the Top of the Module

Option Private should be the first line in your module, before any procedures or variable declarations.

Syntax

Simply type Option Private Module at the beginning of your module.

Scope and Effect

• The Option Private statement applies only to the module in which it is declared.
• It does not affect the visibility of the module’s contents within the project where the module resides. You can still call these procedures or access these variables from other modules within the same project.
• It only restricts access from external projects.

Example

Use Cases

• Add-ins: Commonly used in add-ins where you want to expose only specific functionality to the user or other projects.
• Encapsulation: Helps in encapsulating and protecting your code, ensuring that only intended interfaces are exposed to other projects.

Limitations

• Option Private Module only works for VBA projects that are referenced from other VBA projects. It does not hide the module in the VBA editor from other users who have access to the project.
• It’s not a security feature to protect your code from being viewed or modified; it’s more about controlling the usage and accessibility of the module’s contents.

In summary, Option Private Module is a useful way to control the scope of your VBA modules, especially in larger projects or when creating reusable components like add-ins. It’s part of good coding practice to manage the accessibility of your code effectively.