When writing VBA code, it is important to consider the scope of variables and procedures, as this can greatly impact the functionality and efficiency of your code. One way to define the scope of a variable or procedure is through the use of the Private statement.
In this blog post, we will explore the purpose and syntax of the VBA Private statement, provide examples of how it can be used, and discuss important notes and remarks to keep in mind when using this statement.
VBA Private Statement
Purpose of the Private Statement
In VBA, the Private statement is used to declare a variable or procedure that is only accessible within the same module or class in which it is declared. This means that any other modules or classes within the same project will not be able to access or modify this variable or procedure. It is a way to encapsulate data and procedures, keeping them within the boundaries of a specific module or class.
The use of Private statement can be particularly helpful when developing larger or more complex VBA projects. It allows for better organization and control of variables and procedures, ensuring that they are only used in the intended manner and minimizing any potential conflicts that may arise.
Syntax of the Private Statement
The Private statement can be used to declare both variables and procedures. The syntax for declaring a private variable is as follows:
Private VariableName As VariableType
The keyword Private is followed by the name of the variable and the desired data type. This statement should be placed at the top of the module or class, before any procedures are defined.
To declare a private procedure, the following syntax is used:
Private Sub ProcedureName()
'Code goes here
End Sub
This statement is used at the beginning of the procedure, just like any other procedure declaration.
Examples of Using the Private Statement in VBA
Private Variables
Private variables can be used to store data that is only relevant within a specific module or class. Let’s say you are working on an Excel worksheet and you want to display a message box with the current date and time whenever a particular cell is clicked. Using the Private statement, you can declare a variable to store the current date and time and display it in the message box.
Private dateVariable As Date
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
dateVariable = Now()
MsgBox "Current Date and Time: " & dateVariable
End Sub
In this example, the ‘dateVariable’ is only accessible within the ‘Worksheet_SelectionChange’ procedure and cannot be modified or accessed by any other modules or classes.
Private Procedures
Private procedures can be used to create custom functions or tasks that are only relevant within a specific module or class. For instance, in an Access database, you may have a form for entering employee details. The form may have a button to add new employees, and you want to include some validation before the data is added to the database. By using a private procedure, you can easily validate the data and add it to the database using the ‘DoCmd.RunSQL’ method, all within the same module.
Private Sub AddEmployee()
If Not IsNull(txtName.Value) And Not IsNull(txtTitle.Value) Then
DoCmd.RunSQL "INSERT INTO tblEmployees (EmployeeName, EmployeeTitle) VALUES ('" & txtName.Value & "', '" & txtTitle.Value & "')"
MsgBox "Employee added successfully!"
Me.Requery
Else
MsgBox "Please enter employee name and title."
End If
End Sub
Private Sub btnAdd_Click()
AddEmployee
End Sub
In this example, the ‘AddEmployee’ procedure is only accessible within the same form module and cannot be called or modified by any other modules or classes.
Private Constants
Private constants can be used to declare fixed values that are only used within a specific module or class. This can help to improve the readability and maintainability of your code. Let’s say you are creating a VBA function that converts temperatures from Fahrenheit to Celsius and vice versa. You can use a private constant to store the conversion factor and use it throughout the function without having to repeat the value.
Private Const FAHRENHEIT_CELSIUS_FACTOR As Double = 5 / 9
Function ConvertToCelsius(ByVal fahrenheitTemp As Double) As Double
ConvertToCelsius = (fahrenheitTemp - 32) * FAHRENHEIT_CELSIUS_FACTOR
End Function
In this example, the ‘FAHRENHEIT_CELSIUS_FACTOR’ constant is only accessible within the same module and cannot be changed by any other modules or classes.
Private Properties
Private properties can be used to define custom properties for a class, which are only accessible within the same class. This can be useful for creating objects with specific characteristics and behaviors. For instance, you may have a ‘Car’ class in your VBA project, and you want to set the color of a car as a private property. The color can only be set and retrieved through the car’s methods and is not accessible outside of the class.
Private strColor As String
Public Sub SetColor(ByVal newColor As String)
If newColor = "blue" Or newColor = "red" Then
strColor = newColor
Else
MsgBox "Invalid color. Only blue or red allowed."
End If
End Sub
Public Function GetColor() As String
GetColor = strColor
End Function
In this example, the ‘strColor’ property is only accessible within the ‘Car’ class and cannot be accessed or modified by any other classes or modules.
Private Events
Private events can be used to create custom events that are only raised within a specific class or form. This can be helpful when developing complex user interfaces, as it allows for better control and organization of events. For instance, you may have an Access form with several text boxes, and you want to raise an event when any of the text boxes’ values are changed. By using a private event, you can easily handle all the changes within the same form module and keep the code contained.
Private Event ValueChanged()
Private Sub txtBox1_Change()
RaiseEvent ValueChanged
End Sub
Private Sub txtBox2_Change()
RaiseEvent ValueChanged
End Sub
Private Sub txtBox3_Change()
RaiseEvent ValueChanged
End Sub
In this example, the ‘ValueChanged’ event is only accessible within the same form module and cannot be raised or handled by any other modules or classes.
Important Notes and Remarks
- The Private statement can only be used within a module or class and cannot be used in standard VBA procedures.
- Private variables and procedures are not accessible outside of their declared module or class, including any child modules or classes.
- Private properties and events can only be accessed within their declared class or form, even when using qualified referencing.
- In VBA, all variables and procedures are private by default, unless otherwise specified using the keywords ‘Public’ or ‘Dim’.
- The Private statement should be used sparingly and only when necessary, as it can make it difficult to debug or modify code in the future.
Concluding Remarks
The Private statement in VBA is a powerful tool that can be used to control the scope of variables and procedures within a project. It allows for better organization and encapsulation of data and procedures, ensuring that they are only used in the intended manner.
In this blog post, we have explored the purpose and syntax of the Private statement, provided examples of how it can be used in different scenarios, and discussed important notes and remarks to keep in mind when using this statement. By using the Private statement effectively, you can write more efficient and maintainable VBA code.
Was this blog post helpful? Do you have any other examples of using the Private statement that you would like to share? We would love to hear your feedback and views, so please leave a comment below.
