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

1. Declaration of variable
2. 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

1. Local-level or Procedure Level Scope
2. Module Level Scope
3. 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.