When working with VBA, a variable is a location in your computer’s memory where you can store data. The type of data you can store in a variable would depend on the data type of the variable. For example, if you want to store integers in a variable, your data type would be ‘Integer’ and if you want to store text then your data type would be ‘String’. More on data types later in this tutorial. While a variable’s value changes when the code is in progress, a constant holds a value that never changes. As a good coding practice, you should define the data type of both – variable and constant.
Why Use Variables in VBA?
When you code in VBA, you would need variables that you can use to hold a value. The benefit of using a variable is that you can change the value of the variable within the code and continue to use it in the code. For example, below is a code that adds the first 10 positive numbers and then displays the result in a message box: There are three variables in the above code – Var, i, and k. The above code uses a For Next loop where all these three variables are changed as the loops are completed. The usefulness of a variable lies in the fact that it can be changed while your code is in progress. Below are some rules to keep in mind when naming the variables in VBA:
Data Type of Variables
To make the best use of variables, it’s a good practice to specify the data type of the variable. The data type you assign to a variable will be dependent on the type of data you want that variable to hold. Below is a table that shows all the available data types you can use in Excel VBA: When you specify a data type for a variable in your code, it tells VBA to how to store this variable and how much space to allocate for it. For example, if you need to use a variable that is meant to hold the month number, you can use the BYTE data type (which can accommodate values from 0 to 255). Since the month number is not going to be above 12, this will work fine and also reserve less memory for this variable. On the contrary, if you need a variable to store the row numbers in Excel, you need to use a data type that can accommodate a number up to 1048756. So it’s best to use the Long data type.
Declaring Variable Data Types
As a good coding practice, you should declare the data type of variables (or constants) when writing the code. Doing this makes sure that VBA allocates only the specified memory to the variable and this can make your code run faster. Below is an example where I have declared different data types to different variables: To declare a variable data type, you need to use the DIM statement (which is short for Dimension). In ‘Dim X as Integer‘, I have declared the variable X as Integer data type. Now when I use it in my code, VBA would know that X can hold only integer data type. If I try to assign a value to it which is not an integer, I will get an error (as shown below):
Making Variable Declaration Mandatory (Option Explicit)
While you can code without ever declaring variables, it’s a good practice to do this. Apart from saving memory and making your code more efficient, declaring variables has another major benefit – it helps trap errors caused by misspelled variable names. To make sure you’re forced to declare variables, add the following line to the top of your module. When you add ‘Option Explicit’, you will be required to declare all the variables before running the code. If there is any variable that has not been declared, VBA would show an error. There is a huge benefit in using Option Explicit. Sometimes, you may end up making a typing error and enter a variable name which is incorrect. Normally, there is no way for VBA to know whether it’s a mistake or is intentional. However, when you use ‘Option Explicit’, VBA would see the misspelled variable name as a new variable that has not been declared and will show you an error. This will help you identify these misspelled variable names, which can be quite hard to spot in a long code. Below is an example where using ‘Option Explicit’ identifies the error (which couldn’t have been trapped had I not used ‘Option Explicit’) Note that I have misspelled the word ‘CommissionRate’ once in this code. If I don’t use Option Explicit, this code would run and give me the wrong total commission value (in case the value in cell A1 is less than 10000). But if I use Option Explicit at the top of the module, it will not let me run this code before I either correct the misspelled word or declare it as another variable. It will show an error as shown below:
While you can insert the line ‘Option Explicit’ every time you code, here are the steps to make it appear by default: Once you have enabled this option, whenever you open a new module, VBA would automatically add the line ‘Option Explicit’ to it. Note: This option will only impact any module you create after this option is enabled. All existing modules are not affected.
Scope of Variables
So far, we have seen how to declare a variable and assign data types to it. In this section, I will cover the scope of variables and how you can declare a variable to be used in a subroutine only, in an entire module or in all the modules. The scope of a variable determines where can the variable be used in VBA, There are three ways to scope a variable in Excel VBA: Let’s look at each of these in detail.
Within a Single Subroutine (Local Variables)
When you declare a variable within a subroutine/procedure, then that variable is available only for that subroutine. You can not use it in other subroutines in the module. As soon as the subroutine ends, the variable gets deleted and the memory used by it is freed. In the below example, the variables are declared within the subroutine and would be deleted when this subroutine ends.
Within a Module (Module-level Variables)
When you want a variable to be available for all the procedures in a module, you need to declare it at the top of the module (and not in any subroutine).
Once you declare it at the top of the module, you can use that variable in all the procedures in that module. In the above example, the variable ‘i’ is declared at the top of the module and is available to be used by all the modules. Note that when the subroutine ends, the module level variables are not deleted (it retains its value). Below is an example, where I have two codes. When I run the first procedure and then run the second one, the value of ‘i’ becomes 30 (as it carries the value of 10 from the first procedure)
In All Modules (Public Variables)
If you want a variable to be available in all the procedure in the workbook, you need to declare it with the Public keyword (instead of DIM). The below line of code at the top of the module would make the variable ‘CommissionRate’ available in all the modules in the workbook.
You can insert the variable declaration (using the Public keyword), in any of the modules (at the top before any procedure).
Static Variables (that retains the value)
When you work with local variables, as soon as the procedure ends, the variable would lose its value and would be deleted from VBA’s memory. In case you want the variable to retain the value, you need to use the Static keyword. Let me first show you what happens in a normal case. In the below code, when I run the procedure multiple times, it will show the value 10 everytime. Now if I use the Static keyword instead of DIM, and run the procedure multiple times, it will keep on showing values in increments of 10. This happens as the variable ‘i’ retains its value and uses it in the calculation.
Declaring Constants in Excel VBA
While variables can change during the code execution, if you want to have fixed values, you can use constants. A constant allows you to assign a value to a named string that you can use in your code. The benefit of using a constant is that it makes it easy to write and comprehend code, and also allows you to control all the fixed values from one place. For example, if you are calculating commissions and the commission rate is 10%, you can create a constant (CommissionRate) and assign the value 0.1 to it. In future, if the commission rate changes, you just need to make the change at one place instead of manually changing it in the code everywhere. Below is a code example where I have assigned a value to the constant: The following line is used to declare the constant: When declaring constants, you need to start with the keyword ‘Const‘, followed by the name of the constant. Note that I have specified the data type of the constant as Double in this example. Again, it’s a good practice to specify the data type to make your code run faster and be more efficient. If you don’t declare the data type, it would be considered as a variant data type. Just like variables, constants can also have scope based on where and how these are declared:
How to Record a Macro in Excel Working with Cells and Ranges in Excel VBA Working with Worksheets using Excel VBA Working with Workbooks in Excel VBA VBA Events Excel VBA Loops How to Run a Macro in Excel If Then Else Statement in Excel VBA.
<Private Sub Worksheet_Change(ByVal Target As Range) <On Error Resume Next <If Target.Column = 6 Then <If Target.Validation.Type = " " Then <Application.EnableEvents = False <Target.Offset(0, 1).ClearContents <End If <End If <If Target.Column = 7 Then <If Target.Validation.Type = " " Then <Application.EnableEvents = False <Target.Offset(0, 1).ClearContents <End If <End If <exitHandler: <Application.EnableEvents = True <Exit Sub <End Sub
