Variable, Datatype and the Scope of Variable in VBA

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

TypeDescriptionRange of ValuesOccupy memory
ByteAn integer0 to 2551 byte
IntegerAn integer having larger range then Byte-32,768 to 32,7672 bytes
LongAn integer having larger range then Integer-2,147,483,648 to 2,147,483,6484 bytes
SingleA floating-point numberfor negative values
-3.402823E+38 to -1.401298E-45

for positive values.
1.401298E-45 to 3.402823E+38
4 bytes
DoubleA floating-point number having a larger ranging in value compare to Singlefor negative values
-1.79769313486232e+308 to -4.94065645841247E-324
 
for positive values.
4.94065645841247E-324 to 1.79769313486232e+308
8 bytes
DecimalIf 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
CurrencyA 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.58078 bytes

B.    Non-Numeric Data Types

TypeDescriptionRange of ValuesOccupy memory
StringString can contain up to
approximately 2 billion characters.
0 to 2 billion characters+ 10 bytes
DateA 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, 99998 bytes
BooleanTrue or False
1 or 0
True or False2 bytes
VariantVariable declare without datatype having Variant datatype which can hold any kind of data and behave according the valueFor 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

Example of Option Explicit in MS Excel VBA

Step:2: Click on “Options…” at Tool menu

Example of Option Explicit in MS Excel VBA

Step:3: “Options” Dialog box open. Click on the Editor tab then select the option “Require Variable Declaration” and then press the OK button.

Example of Option Explicit in MS Excel VBA

Step:4: When you add new module “Option Explicit” statement appear at top by default

Example of Option Explicit in MS Excel VBA

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.

Example of Local-level Variable in MS Excel VBA

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.

Example of Module level Variable in MS Excel VBA

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.

Example of Application-level Variable in MS Excel VBA

Leave a Reply

Your email address will not be published.

*