The Public statement is a commonly used keyword in VBA, which is the programming language. It is used to declare a variable, procedure, or object as accessible throughout the entire project or module, making it available for use in other modules or forms.
This statement is commonly used in conjunction with other keywords such as ‘Private’ and ‘Dim’ to control the scope of a variable or procedure within a project. In this blog post, we will dive deeper into the purpose, syntax, examples, important notes, and concluding remarks regarding the Public statement in VBA.
VBA Public Statement
Purpose of the Public Statement
The main purpose of the Public statement is to provide a way to share data between multiple procedures or modules within a project. By declaring a variable or procedure as public, it becomes accessible to all other procedures, functions, and modules in the project, without the need for additional code or declarations.
This allows for a more organized and efficient coding structure, as well as promoting code reuse and flexibility within a project. Additionally, using the Public statement can help avoid naming conflicts and improve the overall readability of the code.
Syntax of the Public Statement
The syntax for the Public statement is as follows:
Public [Scope] Name [As Type]
The ‘Scope’ parameter is optional and can take on the following values:
- Module: Declares the variable or procedure as accessible within the entire module.
- Global: Declares the variable or procedure as accessible within the entire project.
If the ‘Scope’ parameter is not specified, the default scope is ‘Global’. The ‘Name’ parameter refers to the name of the variable or procedure being declared as public, and the ‘Type’ parameter refers to the data type of the variable or procedure, such as Integer, String, or Object.
Examples of Using VBA Public Statement
Global Variable
One common use of the Public statement is to declare a variable as global. This type of variable is accessible throughout the entire project, allowing information to be shared between different modules or forms. For example:
Public GlobalVar As Integer Sub changeGlobalVar() GlobalVar = 5 End Sub Sub useGlobalVar() MsgBox "The value of GlobalVar is: " & GlobalVar End Sub
In this example, the variable ‘GlobalVar’ is declared as global and can be used and modified by any procedure within the project.
Public Procedure
Similar to declaring a variable as public, the Public statement can also be used to declare a procedure as public. This means that the procedure can be accessed and called by any other procedure in the project. For example:
Public Sub AddNumbers()
Dim num1 As Integer
Dim num2 As Integer
Dim total As Integer
num1 = InputBox("Enter first number:")
num2 = InputBox("Enter second number:")
total = num1 + num2
MsgBox "The total is: " & total
End Sub
By declaring the ‘AddNumbers’ procedure as public, it can be called from any other procedure in the project, simplifying the code and reducing repetition.
Public Object
The Public statement can also be used to declare objects as public. This makes them accessible throughout the entire project, which is useful in cases where multiple forms or modules need to work with the same object. For example:
Public objWorksheet As Worksheet
Sub createObject()
Set objWorksheet = ThisWorkbook.Worksheets("Sheet1")
End Sub
Sub useObject()
objWorksheet.Range("A1").Value = "Hello World!"
End Sub
In this example, the ‘objWorksheet’ object is declared as public and can be accessed and modified by multiple procedures within the project.
Public Array
Arrays can also be declared as public, allowing them to be accessed and modified by multiple procedures within a project. This can be helpful when working with large data sets or when using complex algorithms. For example:
Public arrNumbers(5) As Integer
Sub populateArray()
For i = 0 To 5
arrNumbers(i) = i
Next i
End SubSub printArray()
For i = 0 To 5
Debug.Print arrNumbers(i)
Next i
End Sub
In this example, the ‘arrNumbers’ array is declared as public and can be populated and printed by multiple procedures within the project.
Public Constant
Using the Public statement, constants can be declared as public, making them accessible to all procedures within a project. This can be useful for storing values that are used frequently and need to be consistent throughout the project. For example:
Public Const PI = 3.14 Sub calculateArea(radius) Dim area As Double area = PI * radius^2 MsgBox "The area is: " & area End Sub
In this example, the constant ‘PI’ is declared as public and can be used in the ‘calculateArea’ procedure, as well as any other procedure within the project.
Important Notes & Remarks
- The Public statement can only be used at the module level, meaning it cannot be used inside a procedure or function.
- Public variables and procedures are recognized by all other modules and procedures in a project, regardless of whether or not they have been referenced in the code.
- Using too many public variables and procedures can lead to confusion and make the code more difficult to maintain. It is recommended to use them sparingly and only when necessary.
- In some cases, using global variables and procedures can make the code less efficient, as the program needs to constantly check if they have been modified by other procedures.
- The Public statement is only applicable within a VBA project and has no effect on other applications or programming languages.
Conclusion
In this blog post, we discussed the purpose, syntax, top 5 examples, and important notes regarding the Public statement in VBA. This statement is beneficial for sharing data between procedures and modules within a project, promoting code reuse and flexibility. By using global variables, procedures, objects, arrays, and constants, our code becomes more organized and efficient.
However, it is essential to use the Public statement carefully and only when needed to avoid confusion and potential performance issues.
We hope this post has provided a comprehensive understanding of the Public statement in VBA and how it can be used effectively in your coding projects.
We would love to hear your feedback and views on this topic. Do you have any other examples of using the Public statement in VBA? What other important notes and remarks do you think are relevant to this topic? Let us know in the comments below. Happy coding!
