In all the programming languages, we have variable access specifiers that define from where a defined variable can be accessed. Excel VBA is no Exception. VBA too has scope specifiers. These scope specifiers can be used to set the visibility/scope of a variable in Excel VBA.
In Excel VBA, we have three types of scope specifiers:
The variables that are declared within a subroutine, can be accessed only in that procedure/function. They have a procedure level scope. They are usually declared with Dim keyword. If the module is option implicit then they the variable may have not been declared, just used directly.
In the below example, we have a subroutine scope test that has variable x and y. When we run the first subroutine, it runs perfectly and prints the sum of X and Y
Option Explicit Sub scopeTest() Dim x, y As Integer ' Procedure level variable in VBA x = 2 y = 3 Debug.Print x + y End Sub Sub sum() x = 5 y = 7 Debug.Print x + y End Sub
But when you run the subroutine sum, it throws an error that the variable is not declared. Why? Because X and Y are private to the procedure scopeTest and subroutine sum does not have access to them. Hence the sub runs into an error.
When you want to declare a variable that needs to be accessed in the whole module, then you declare that variable as private on the top of the module, before any subroutine or function.
The variables declared before any subroutine, in the module, are by default Private. Even if you declare them with Dim keyword. But to be specific, it is wise to use the Private keyword.
The below two procedures are in the same module, module 1.
Option Explicit 'Module level variable in VBA. Both variables x and y are private to this module. and 'can be accessed from any sub or function within this module. Dim x As Integer Private y As Integer Sub scopeTest() 'This can be accessed from any module in the project x = 2 y = 3 Debug.Print x + y End Sub Private Sub sum() ' This can't be accessed from other modules x = 5 y = 7 Debug.Print x + y End Sub
The above two functions will run perfectly fine as the variable x and y are the private variables to this module.
Important: The last values stored in x and y are retained until in whole module. If we run the sub scopeTest first and do not initialize the values of x and y in sub sum and run it, then the initial value of x and y will be unchanged.
The below subroutine is defined in a separate module, module 2. When I try to access the variable from another module, the VBA procedure (or function) runs into an error.
But if I try to call the subroutine sum from module 1 in module 2, this works perfectly. To make your function and subroutines private to the module, use the keyword Private before the sub and function.
Note:- The variables declared before any subroutine or function, in a module are private to the module by default. You may use the Private keyword. But the functions and subroutines are public by default and can be accessed by any module in a project. To make functions and subroutines private to the module you have to use the Private keyword.
As we learned in the above examples, the function and subroutines are by default public and can be accessed from any module but variables are not. To make a variable accessible from any module in Excel VBA, we use Public keyword.
To declare a public variable in a project, you have declared them on the top of the module, before any function or subroutine with the public keyword. The below code is written in module 1.
Option Explicit 'Project level variable in VBA. Public x As Integer Public y As Integer Public Sub scopeTest() 'This can be accessed from any module in the project x = 2 y = 3 End Sub Private Sub sum() ' This can't be accessed from other modules x = 5 y = 7 Debug.Print x + y End Sub
And this subroutine is in another module, module 2.
Option Explicit Sub mul() Call scopeTest 'Call sum ' it won't work as it is private to the module 1 Debug.Print x * y End Sub
This time it runs perfectly.
First I have called the subroutine scopeTest. Since the scopeTest is public, it is called. It initializes the values of x and y. Next, we multiply x and y. Since scopeTest subroutine had initialized it with values 2 and 3, the result we get is 6.
So yeah guys, this is how you can use the variable scope specifiers in Excel to control the visibility of variables, functions, and subroutines in Excel VBA projects.
I tried to explain the Access Specifiers of VBA in the simplest way I could. I hope it was explanatory. If you have any doubts regarding this article or any other VBA related doubt, ask me in the comments section below. I will be happy to here from you.
ByRef and ByVal Arguments |When an argument is passed as a ByRef argument to a different sub or function, the reference of the actual variable is sent. Any changes made into the copy of the variable, will reflect in original argument.
Delete sheets without confirmation prompts using VBA in Microsoft Excel|Since you are deleting sheets using VBA, you know what you are doing. You would like to tell Excel not to show this warning and delete the damn sheet.
Add And Save New Workbook Using VBA In Microsoft Excel 2016|In this code, we first created a reference to a workbook object. And then we initialized it with a new workbook object. The benefit of this approach is that you can do operations on this new workbook easily. Like saving, closing, deleting, etc
Display A Message On The Excel VBA Status Bar The status bar in excel can be used as a code monitor. When your VBA code is lengthy and you do several tasks using VBA, you often disable the screen update so that you don’t see that screen flickering
Turn Off Warning Messages Using VBA In Microsoft Excel 2016|This code not only disables VBA alerts but also increases the time efficiency of the code. Let’s see how.
50 Excel Shortcuts to Increase Your Productivity | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.
The VLOOKUP Function in Excel | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets.
COUNTIF in Excel 2016 | Count values with conditions using this amazing function. You don't need to filter your data to count specific value. Countif function is essential to prepare your dashboard.
How to Use SUMIF Function in Excel | This is another dashboard essential function. This helps you sum up values on specific conditions.
The applications/code on this site are distributed as is and without warranties or liability. In no event shall the owner of the copyrights, or the authors of the applications/code be liable for any loss of profit, any problems or any damage resulting from the use or evaluation of the applications/code.