Variables in Excel VBA plays a major role in VBA Programming. Declaring variables helps programer to avoid mistakes. Let us see the complete tutorail about VBA Variables, syntax and how we can declare variables implicitly and explicitly.
We must follow the following rules while declaring a variable name.
- Variable Name must be less than 255 characters long
- VBA kywords or reserved words cannot use as a Variable Name
- It should not begin with number, always must begin with a letter
- Variables consists of only letters, numbers and underscore characters.
- It doesn’t allow space in the variable name.
- Variable name doesn’t allow period(.)
- We cannot use special characters like @,#,$.%,&,!, etc.
Let us see syntax and example to declare the variables Implicitly.
VariableName = Value
Example: iCnt = 10
Let us see syntax and example to declare the variables explicitly. Here we use Dim statement to declare a variable explicitly.
Dim VariableName As DataType
Example: Dim iCnt As Integer
The scope of Variable helps to determine where the variable to be used. We have three ways to scope a variable in Excel VBA.
In procedure level, all variables are declared inside a procedure. These variables only available or accessible within that procedure only. It can’t be accessible from other procedures or modules or projects. These variables also called as local variables.
Let us see the example on procedure level variables.
Sub VBAF1_Procedure_Level_Variables_Ex1() 'Procedure Level Variable Declaration Dim SName As String SName = "Test" Call VBAF1_Procedure_Level_Variables_Ex2 End Sub
Sub VBAF1_Procedure_Level_Variables_Ex2() MsgBox SName, vbInformation, "VBAF1" End Sub
Explanation: There are two procedures specified above. In the first procedure a variable named ‘SName’ declared and assigned. This variable can’t be accessible in second procedure (VBAF1_Procedure_Level_Variables_Ex2). It will not display any output. Here is the output screen shot for your reference.
All static variables are declared within a procedure and can’t declare outside procedure. Static variable always retains its value even after the procedure ends until the project terminates. This static variable is not available for other procedures.
Let us see the example on static variable.
Sub VBAF1_Static_Variables() 'Variable declaration Dim SName As String Static iCnt As Integer iCnt = iCnt + 1 SName = "Test" & iCnt MsgBox SName, vbInformation, "VBAF1" End Sub
Note: To test the output run above macro twice and check the output each time. Notice the difference between two outputs. Here is the output screenshot of above macro.
We can have multiple modules in visual basic editor window in Excel. Each module contains different types of variables. One among is a Module level variable. It is declared in the declaration section of the module and outside of procedures. It is also called as Private Module Level variable. We can write any number of procedures in one module. Module level variable can be used by any procedure within a declared module. A variable can be either Public or private. We can also declare private variable using Dim statement. There is no difference between Dim and Private statement at module level. Please find the following example and its explanation for better understand.
Let’s see an example macro to understand a Module level variables.
'Module Level Variuables Dim firstVal As Integer Private secondVal As Integer 'First Procedure Sub VBAF1_Procedure_Ex1() 'Procedure Level Variable Dim iResult As Integer 'Assign values to module level variables firstVal = 40 secondVal = 60 iResult = firstVal + secondVal MsgBox "The sum of first value and second value is : " & iResult, vbInformation, "VBAF1" End Sub
'Second Procedure Sub VBAF1_Procedure_Ex2() MsgBox "The fist module level is :" & firstVal & vbCrLf & "The second module level is : " & secondVal & vbCrLf & _ "The value of iResult is : " & iResult End Sub
Explanation: We have two module level variables firstVal and secondVal and iResult is a procedure level module. In the first procedure we have assigned two values to variables which are declared as module level variables. In the second procedure we are displaying all the specified values of respective variables. But it displays only first and second value. It doesn’t display third variable vale. Because it is declared at procedure level. In the 2nd procedure we can’t access other procedure values. Run 2nd procedure after running first procedure. Please find screenshots of above procedures.
Let us see about public variables in VBA. It is same like module level variable. only difference is we use public keyword instead of dim or public keywords. These variables can declare in standard declaration section of module not in class module or userform module. It has to declare at the beginning of module like module level variable. These variables are visible to all modules. It is also called as Global variables. The value of public variable is retained until the End statement is used or workbook closes.
You can place following code in the 1st module.
'Public/Global Level Variuables Public firstVal As Integer Public secondVal As Integer Public iResult As Integer 'First Procedure Sub VBAF1_Procedure1_Module1() 'Assign values to module level variables firstVal = 40 secondVal = 60 iResult = firstVal + secondVal MsgBox "The sum of first value and second value is : " & iResult, vbInformation, "VBAF1" End Sub
You can place following code in the 2nd module.
'Second Module Sub VBAF1_Procedure2_Module2() MsgBox "The fist module level is :" & firstVal & vbCrLf & "The second module level is : " & secondVal & vbCrLf & _ "The value of iResult is : " & iResult, vbInformation, "VBAF1" End Sub
We have declared all three variables in 1st module and accessed by both 1st module and 2nd module.
Here is the output screenshot of above module.
Other Useful Links:
Please click on below links to have a knowledge on specific topics.