First of all you need to clear what is Variable? Lets see from your end. In formula while you use =A1, then A1 is the variable name which contains some values. In computer programming sometimes you need to tell the computer to keep a calculated value in memory for later use. This calculated value your computer will store in a variable name which you will mention with a Dim command generally. Dim is the abbreviation of "Dimension". For example:
This command will set a value "Hello World" in ValueInMemory word. If you write ValueInMemory, then computer will read "Hello World". This is the example of Variable.
Types of Variable?
Variable are different types, such as String, Integer, Long, Date, Double, Object etc. It depends on which thing you want to add in memory. If it is a text, then use String, if it is a number then use Integer or Long etc. These are generally classified are into 2 main groups. Numerical and Non-Numerical. Below table shows the different types of Numerical Variables and their memory range:
Below table shows the different types of Non-Numerical Variables and their memory range:
Rules of writing variable names:
Simple you just know below 3 things:
Example of Variable:
To store the values in a variable first declare the variable name with a DIM statement. It is highly recommended that use DIM statement to declare the variable name. You can also declare a variable by not using DIM command. In below example I'v written simple codes, that will help you about variable declaring procedures and obviously how to call the variables to get the values from it:
The above code saved under txt1 macro. Dim declared a variable name variabletest. Then a long text value within "" (double quotation mark) is stored under variabletest variable. Finally in Worksheet1 D5 cell the value will paste which is set under variabletest.
Why should use DIM statement before declaring Variable?
To declare the variable, you should use DIM statement or if you wish you do not need to use DIM statement. Obviously you can use with DIM statement as below:
Obviously the above Variable declaring method without DIM statement will also work. But the benefit of using DIM statement is, you are telling the computer about variable name and what types of data will store under this variable such as Date, Integer, Long, String etc. which can calculate faster.
About Option Explicit:
At the beginning of your VBA programming if you use this Option Explicit code, then you must declare all the variables name with a Dim statement on that module. This is a good habit to use this Option Explicit code to resolve errors while running this code. For example, see the below image:
Option Explicit code was already in the top and only one variable has declared vbname. But while vbx used as a variable but not declared yet, then the Option Explicit detected Error while running it.
The error message informing us that an undeclared variable found somewhere in your code, a variable not set up with the Dim keyword. So it's good to type Option Explicit at the top of your code window in order to prevent variables being set up accidentally.
Sub keepinMemory()
Dim ValueInMemory as Text
ValueInMemory = "Hello World"
End Sub
This command will set a value "Hello World" in ValueInMemory word. If you write ValueInMemory, then computer will read "Hello World". This is the example of Variable.
Image 1: Variables and Values
Types of Variable?
Variable are different types, such as String, Integer, Long, Date, Double, Object etc. It depends on which thing you want to add in memory. If it is a text, then use String, if it is a number then use Integer or Long etc. These are generally classified are into 2 main groups. Numerical and Non-Numerical. Below table shows the different types of Numerical Variables and their memory range:
Image 2: Different types of Numerical Variable
Below table shows the different types of Non-Numerical Variables and their memory range:
Image 3: Different types of Numerical Variable
Rules of writing variable names:
Simple you just know below 3 things:
- You must use alphabetical characters first then numbers or underscore. If you start naming by using first number or underscore character then it will detect as wrong.
- You are not allowed any wildcard characters.
- Maximum 40 characters are allowed to naming a variable.
- You are not allowed to use any space or full stop.
- Do not use number and wild card characters in the begining (Example: "$vname", "3vname")
Example of Variable:
To store the values in a variable first declare the variable name with a DIM statement. It is highly recommended that use DIM statement to declare the variable name. You can also declare a variable by not using DIM command. In below example I'v written simple codes, that will help you about variable declaring procedures and obviously how to call the variables to get the values from it:
Sub txt1()
Dim variabletest As String
variabletest = "Learn Variable to grow up your Excel VBA skill"
Worksheets(1).Range("D5").Value = variabletest
End Sub
The above code saved under txt1 macro. Dim declared a variable name variabletest. Then a long text value within "" (double quotation mark) is stored under variabletest variable. Finally in Worksheet1 D5 cell the value will paste which is set under variabletest.
Why should use DIM statement before declaring Variable?
To declare the variable, you should use DIM statement or if you wish you do not need to use DIM statement. Obviously you can use with DIM statement as below:
Sub txt1()
variabletest = "Learn Variable to grow up your Excel VBA skill"
Worksheets(1).Range("D5").Value = variabletest
End Sub
Obviously the above Variable declaring method without DIM statement will also work. But the benefit of using DIM statement is, you are telling the computer about variable name and what types of data will store under this variable such as Date, Integer, Long, String etc. which can calculate faster.
About Option Explicit:
At the beginning of your VBA programming if you use this Option Explicit code, then you must declare all the variables name with a Dim statement on that module. This is a good habit to use this Option Explicit code to resolve errors while running this code. For example, see the below image:
Image 4: Option Explicit
Option Explicit code was already in the top and only one variable has declared vbname. But while vbx used as a variable but not declared yet, then the Option Explicit detected Error while running it.
Image 5: Error while running a variable without Dim statement
The error message informing us that an undeclared variable found somewhere in your code, a variable not set up with the Dim keyword. So it's good to type Option Explicit at the top of your code window in order to prevent variables being set up accidentally.