Variable, Datatype and the Scope of Variable in VBA
What is Variable?
The variable is used to hold data in memory. Whenever we want to store data in a computer’s memory during program execution at that time, we have to assign a name to the memory location to access that location to store data. So Variable is the name of a memory location that is used to hold a value that can be changed or used during the program.
A variable name can be defined by the programmer and also can use to store value within the program. VBA properly manages the place in the computer’s memory which is used to store any value.
The variable in any Programming language can hold any kind of data but we have to tell compiler or interpreter to that which type of data variable can hold.
So at the time of declaration, we have to define its data type also
Variable and Datatype in VBA Video in Hindi
There are mainly two states of any variable
- Declaration of variable
- Initialization of variable
1. Declaration of Variable
Declaration of Variable means we are telling to program is that we need a specified variable. Generally Variable is declared at the top of the program rather than in between program because it is a nice practice or it is a convention. But it is compulsory to declare a variable before to use.
There are three keywords and one word, use to declare any variable.
Syntax to declare variable.
Dim <variable_name> As <variable_DataType>
Where…
Dim: it is a keyword which uses to declares the variable
: the variable name, it is user define name
As: it is also keyword use to declares the variable type
: Variable data type
2. Initialization of Variable or assign value to variable
Initialization of variable means give or assign value to variable using “=” symbol
Example.
Dim roll As Integer roll = 33
Important rules Keep in mind in VBA for Variable names
Length of the variable name is Maximum 255 character
Fist letter must be an alphabet.
Cannot use space between the name
Cannot use any symbol except underscore “_”
Cannot use VBA reserved keywords as a variable name.
Within a single program or procedure, all variable names must be unique.
Datatype in VBA
Datatype defines which type of data Variable will be going to hold.
According to the datatype, the compiler or interpreter manipulate the variable value. For example if any digit stored as a numeric datatype then it is possible to perform an arithmetic operation on it but if it stores a string character then it is not possible to perform any arithmetic operations.
We can divide all datatype into two categories. There is no such category available but for easy understanding we categories all datatype into two category
A. Numeric Data Types
| Type | Description | Range of Values | Occupy memory |
|---|---|---|---|
| Byte | An integer | 0 to 255 | 1 byte |
| Integer | An integer having larger range then Byte | -32,768 to 32,767 | 2 bytes |
| Long | An integer having larger range then Integer | -2,147,483,648 to 2,147,483,648 | 4 bytes |
| Single | A floating-point number | for negative values -3.402823E+38 to -1.401298E-45 for positive values. 1.401298E-45 to 3.402823E+38 | 4 bytes |
| Double | A floating-point number having a larger ranging in value compare to Single | for negative values -1.79769313486232e+308 to -4.94065645841247E-324 for positive values. 4.94065645841247E-324 to 1.79769313486232e+308 | 8 bytes |
| Decimal | If we use integer number then its length is 0 to 28 digits And if we use floating-point number then it may have a maximum 28 decimal point. | if no decimal is use +/- 79,228,162,514,264,337,593,543,950,335 if decimal is use +/- 7.9228162514264337593543950335 (28 decimal places). | 12 bytes |
| Currency | A floating-point number with up to 15 digits at the left of the decimal point and up to 4 digits at the right of the decimal point | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 | 8 bytes |
B. Non-Numeric Data Types
| Type | Description | Range of Values | Occupy memory |
|---|---|---|---|
| String | String can contain up to approximately 2 billion characters. | 0 to 2 billion characters | + 10 bytes |
| Date | A floating-point number in which the date is at left of the decimal point and the time at the right of it. | January 1, 100 to December 31, 9999 | 8 bytes |
| Boolean | True or False 1 or 0 | True or False | 2 bytes |
| Variant | Variable declare without datatype having Variant datatype which can hold any kind of data and behave according the value | – | For numerical value 16 bytes For character + 22 bytes |
Assigning value to String type variable
Assigning a value to string type variable with “ ” (double inverted comma). Any string Value must be between “ ”.
Example
Dim name As String name = “Johan Carter”
Assigning value to Date type variable
Assigning a value to Date type variable with # (hash pound sign). Any Date and time Value must be between # #.
Example
Dim birthDate As Date Dim birthTime As Time birthDate = #26 January 1995# birthDate = #01/26/1995# birthTime = #03:30 AM#
Different ways to declare variable
Declaring multiple variables at separated line
Dim mark1 As Integer Dim mark2 As Integer Dim mark3 As Integer
Declaring multiple variable with in single line
Dim mark1 As Integer, mark2 As Integer, Dim mark3 As Integer
Make Compulsory declaration of Variable in VBA
In VBA default it is not compulsory to declare variable user can use the variable directly but sometimes it may create some problem so it is best practice to set up VBA like that so it compulsory tell to declaration variable. If you write the “Option Explicit” statement at the top of the program then VBA forcefully tells us to declare all the variables before use.
Another option is to set up the “Option Explicit” statement default writes at top of the all program automatically. Below is the step.
Step:1: Click on Visual Basic Option or press “Alt + F11” to open VBA code window
Step:2: Click on “Options…” at Tool menu
Step:3: “Options” Dialog box open. Click on the Editor tab then select the option “Require Variable Declaration” and then press the OK button.
Step:4: When you add new module “Option Explicit” statement appear at top by default
The Variable Scope
Scope of Variable means which part of our program can access or use the declared variable. For example variable which is declared within the Sub Procedure or in the Function is only use within that outside it is not accessible.
Two things are important for the variable, which is “lifetime” and “Visibility” of variables. A variable’s lifetime means how long it will exist to use? And visibility means in which part of the program we can access it.
There are mainly three types of Variable Scope in VBA
- Local-level or Procedure Level Scope
- Module Level Scope
- Application Level or Global Level Scope
1. Local-level or Procedure Level Scope
The visibility of variables depends on where it is declared. If variable declared within the Procedure then it can only accessible within that procedure.
2. Module Level Scope
The visibility of variables or variables can use to the entire module for more than one procedure or function. If variable declared at the top of the module, outside of all Procedure then it can accessible by more one procedure within the same module.
3. Application Level or Global Level Scope
Application-level variables can be used at any module. It means such variables are available at the whole application. This type of variable also calls public variable. In below example, we declare variable “n1” as public in Module1 and it also used at Module2.
