VBA Variables in Excel

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.

Rules to Naming a Variable in VBA

We must follow the following rules while declaring a variable name.

  1. Variable Name must be less than 255 characters long
  2. VBA kywords or reserved words cannot use as a Variable Name
  3. It should not begin with number, always must begin with a letter
  4. Variables consists of only letters, numbers and underscore characters.
  5. It doesn’t allow space in the variable name.
  6. Variable name doesn’t allow period(.)
  7. We cannot use special characters like @,#,$.%,&,!, etc.

Implicit Variable Declaration in VBA

Let us see syntax and example to declare the variables Implicitly.

VariableName = Value

Example: iCnt = 10

Explicit Variable Declaration in VBA

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

Scope of Variable in VBA

The scope of Variable helps to determine where the variable to be used. We have three ways to scope a variable in Excel VBA.

Procedure level (Local) Variables in 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.

Procedure Level Variable

Static Variables in VBA

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.

Static Variable declaration

Module level Variables in VBA

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.
Example:
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.
Output:

VBA Variables in Excel
VBA Variables in Excel

Public Variables in VBA

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.

Example:
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.
Output:
Here is the output screenshot of above module.

VBA Project Variable in Excel
VBA Project Variable in Excel

Other Useful Links:

Please click on below links to have a knowledge on specific topics.

VBA Tutorial VBA Constants VBA Data Types

Leave a Comment

Your email address will not be published. Required fields are marked *