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.

Public myVariable As Integer

Accessing Public Variables

Once declared, these variables can be accessed or modified from any procedure within any module in the VBA project.

Sub ModifyVariable()
    myVariable = 10
End Sub

Declaring Public Procedures

Public Sub Procedures

You can declare a Sub procedure as public. This makes it callable from any module.

Public Sub MyProcedure()
    ' Code here
End Sub

Public Function Procedures

Similarly, you can declare a function as public.

Public Function MyFunction() As Integer
    ' Code here
    MyFunction = 1
End Function

Best 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.

Remember, Public variables and procedures in VBA are accessible from all modules in the same project but not from other projects or applications. If you need to expose functionalities to other applications, you would typically use COM (Component Object Model) interfaces or other methods of inter-application communication.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project