Variables in Excel VBA plays a major role in VBA Programming. Declaring variables helps programmer to avoid mistakes. Let us see the complete tutorial about VBA Variables, syntax and how we can declare variables implicitly and explicitly.
- Rules to Naming a Variable in VBA
- Implicit Variable Declaration in VBA
- Explicit Variable Declaration in VBA
- Scope of Variable in VBA
- Module level Variables in VBA
- Public Variables in VBA
- Instructions to Run VBA Macro Code
- Other Useful Resources
We must follow the following rules while declaring a variable name.
- Variable Name must be less than 255 characters long
- VBA keywords 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 use. 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 known 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 specify 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 declare 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 use 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 user form 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.
You can refer the following link for the step by step instructions.
Click on the following links of the useful resources. These helps to learn and gain more knowledge.