In VBA (Visual Basic for Applications), a Public statement is used to declare a variable or a procedure that is accessible from all modules within the same project. This is in contrast to Private variables or procedures, which are only accessible within the module where they are declared
Here’s a basic rundown of how to use the Public statement in VBA
Declaring Public Variables
Module-Level Scope
You typically declare public variables at the top of a module (outside of any procedures or functions) to make them accessible throughout the module and other modules in the project.VBA
Public myVariable As IntegerAccessing Public Variables
Once declared, these variables can be accessed or modified from any procedure within any module in the VBA project.VBA
Sub ModifyVariable()
myVariable = 10
End SubDeclaring Public Procedures
Public Sub Procedures
You can declare a Sub procedure as public. This makes it callable from any module.VBA
Public Sub MyProcedure()
' Code here
End SubPublic Function Procedures
Similarly, you can declare a function as public.VBA
Public Function MyFunction() As Integer
' Code here
MyFunction = 1
End FunctionBest Practices
- Global Variables: Use public variables sparingly. Overuse of global variables (variables that are accessible throughout the entire application) can make your code harder to debug and maintain.
- Modular Design: It’s often better to pass variables as parameters to procedures rather than relying on public variables, as this makes your code more modular and easier to understand.
Unlock Your Potential
Excel
Access
Power BI
