PROVIDING ACCURATE, RELIABLE AND STRAIGHT TO THE POINT KNOWLEDGE ABOUT DIFFERENT IT TOOLS EXPLAINED WITH PRACTICAL EXAMPLES

CONTENTS

VARIABLES AND DATA TYPES IN VBA

INTRODUCTION

The programming languages mainly solve our problems with the help of manipulating data.

Applying different operations on the data needs the values to be saved in some locations or addresses. The different names which we use to store these values are called as Variables.

So

Variable is a name or reference given to location in the computer’s memory which can be used to save any value of any type for the further use in any functions or procedures.

Now if we have understood the variable, the next things comes is the DATA TYPE.

As we know that variables hold the values, what kind of values are being held, are called as data types.

The various data types can be boolean (binary), integer, text etc.

 

DATA TYPES

IMPORTANCE OF DATA TYPES

The declaration of the variables with proper data types are very important. We may not feel its importance when we are using less complex programs.

But when we have made a very lengthy, processing intensive, long and complex program, these small changes matter a lot. The main reason for the selection of data types is the use of memory.

All the data types have different memory usage and different limits of the values which it can hold.

Suppose, we need to have a variable to store only two values 0 and 1 and

we have declared a variable e.g.

Dim Response as integer

Now, we should think here that the memory used by INTEGER is 2 Bytes where as there is one more data type, BYTE which is just 1 Byte and can store 0 to 255 values. So we can save one byte here.

It was just an example but while programming you would come to know that we have to use countless variables so the impact of this choice is shown when we run heavy programs.

So this is the reason why DATA TYPE SELECTION is so necessary.

DATA TYPES AVAILABLE

There are different data types available to be used in a program having different size and different capacity to hold values. The following table shows the different data types available and their size and capacity.

Data typeStorage sizeRange
Boolean2 bytesTrue or False
Byte1 byte0 to 255
CollectionUnknownUnknown
Currency(scaled integer)8 bytes-922,337,203,685,477.5808 to 922,337,203,685,477.5807
Date8 bytesJanuary 1, 100, to December 31, 9999
Decimal14 bytes

+/-79,228,162,514,264,337,593,543,950,335 with no decimal point

+/-7.9228162514264337593543950335 with 28 places to the right of the decimal

Smallest non-zero number is+/-0.0000000000000000000000000001

DictionaryUnknownUnknown
Double (double-precision floating-point)8 bytes

-1.79769313486231E308 to -4.94065645841247E-324 for negative values

4.94065645841247E-324 to 1.79769313486232E308 for positive values

Integer2 bytes-32,768 to 32,767
Long (Long integer)4 bytes-2,147,483,648 to 2,147,483,647
LongLong (LongLong integer)8 bytes

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

Valid on 64-bit platforms only.

LongPtr (Long integer on 32-bit systems, LongLong integer on 64-bit systems)

4 bytes on 32-bit systems

8 bytes on 64-bit systems

-2,147,483,648 to 2,147,483,647 on 32-bit systems

-9,223,372,036,854,775,808 to 9,223,372,036,854,775,807 on 64-bit systems

Object4 bytesAny Object reference
Single (single-precision floating-point)4 bytes

-3.402823E38 to -1.401298E-45 for negative values

1.401298E-45 to 3.402823E38 for positive values

String (variable-length)10 bytes + string length0 to approximately 2 billion
String (fixed-length)Length of string1 to approximately 65,400
Variant (with numbers)16 bytesAny numeric value up to the range of a Double
Variant (with characters)22 bytes + string length (24 bytes on 64-bit systems)Same range as for variable-length String
User-defined (using Type)Number required by elementsThe range of each element is the same as the range of its data type.

A Variant containing an array requires 12 bytes more than the array alone.

WHICH DATA TYPE TO BE USED

Any data type can be used but it should always be planned before designing the program.

Here are a few things which can be considered before deciding which data type to be used.

Least value which can come in the program.

Highest value which can appear in the program.

Always use the data type with lowest bytes and which can hold all our values so that our program is fast and efficient.

There shouldn’t come any value which our data type can’t handle at any times else it’ll cause an error or wrong answer.

HOW TO DECLARE VARIABLES

As a special mention, it was stated that if we don’t declare any datatype , a datatype named VARIANT is taken by default. We’ll again visit VARIANT in detail later in this article.
Now we know, why we should declare the variables ourselves if the program is large or complex. Even if the program is small, its a good habit to be developed.
So the first thing is
IF WE WANT TO DECLARE DATA TYPE OURSELVES, WE NEED TO PUT THIS STATEMENT BEFORE STARTING A PROGRAM
 
OPTION EXPLICIT
If this statement is present, VBA won’t execute our program if we haven’t declared any variable.
*OPTION EXPLICIT should be written above everything , outside the SUB procedure or function, something like this.
 
option explicit
sub………….
.
.
end sub
 

SCOPE OF A VARIABLE

The scope of the variables mean the domain in which they will be applicable.

It means the scope defines in which function or procedure, the variable can be used or not.

 

TYPES OF SCOPE AND THEIR DECLARATION

The variable can be be given the scope of
  • Within a sub procedure or function
  • Within a module
  • Within a project

 

LOCAL VARIABLES

The variables having the scope in a procedure or function only, in which they are defined are known as Local Variables.
Following is the way to define them
LOCAL VARIABLES ARE DEFINED WITHIN A PROCEDURE OR FUNCTION WITH A DIM OR STATIC STATEMENT
EXAMPLE
 
Dim x as Integer
or
Dim x As Integer, y As Integer, z As Integer
*STATIC variables keep the value even after the procedure ends.
 

MODULE VARIABLES

The variables having the scope in a module only, in which they are defined are known as MODULE Variables.
Following is the way to define them

MODULE VARIABLES are declared the same way as local variables but before the first procedure of the module.

 
Dim x as Integer
sub name……..
 
end sub
sub 2….
 
end sub
 
The variables defined as module variables are available for all the procedures in that module.
 

PROJECT/PUBLIC VARIABLES

If we want our variables to be available to each and every component under the project, we need to declare it as public before all the procedures or functions.

It is declared as

Public A as integer

Now A is available to all the modules and all the procedures or function in the project.

 

DECLARING CONSTANTS IN VBA

CONSTANTS are the values which are fixed or never change like a variable.
These are declared at the desired level and used accordingly. To have an understanding let us take an example.
 
Suppose, we have to calculate the interest on a loan given by the bank. Now for the different calculations, loan interest rate will appear frequently.
we know that interest rates are changed frequently by the central bank.
Suppose there is a change in interest rate. It mean that we need to change the interest rate at all the places in our code or we can declare a constant and make the change in just one statement.
The constant is declared as
const Dim Int as Integer =7.5
const Rate = 3.5, Period = 45
const ModName as String = “HELLO”
Public Const AppName as String = “GYANKOSH.NET”
Now , whever interest rate is needed, we’ll use Int in place of the number 7.5
 
THE SCOPE OF CONSTANTS ALSO WORK SAME AS THE VARIABLES

Operator                                                                                      What It Does
Not                                                                           Performs a logical negation on an expression
And                                                                          Performs a logical conjunction on two expressions
Or                                                                            Performs a logical disjunction on two expressions
Xor                                                                           Performs a logical exclusion on two expressions
Eqv                                                                           Performs a logical equivalence on two expressions
Imp                                                                          Performs a logical implication on two expressions

ArithmeticComparisonLogical
Exponentiation (^)Equality (=)Not
Negation (-)Inequality (< >)And
Multiplication and division (*, /)Less than (<)Or
Integer division (\)Greater than (>)Xor
Modulus arithmetic (Mod)Less than or equal to (< =)Eqv
Addition and subtraction (+, -)Greater than or equal to (> =)Imp
String concatenation (&)Like, Is 

USING OBJECTS AS VARIABLES

Sometimes objects can be declared as variables for an ease of programming and a better structure.

It can help in a quicker execution too.

For example we can define a Range Variable.

Let us check the benefit of defining an object variable.

Suppose we need to use a Range A1 to set up few properties.

We’ll use the following statement for that.

Worksheets(“Sheet1”).Range(“A1”).PROPERTY 1=VALUE
Worksheets(“Sheet1”).Range(“A1”).PROPERTY 2= VALUE

AND SO ON….

Now if we declare an object variable such as

Dim Table as Range

Set Table= Worksheets(“Sheet1”).Range(“A1”)

Table.property 1=value

Table.property 2=value

and so on.

CUSTOM VARIABLES

Many programming languages lets us create custom or user defined variables, so does VBA.

LOCATION OF DEFINING CUSTOM VARIABLES

These are defined at the top of the module before any procedure.

EXAMPLE

Type PersonalDetails
                    Name As String

                    Address As String
.

.
End Type

Now the PersonalDetails has become a variable.

Whenever we need to create any data from these variables it’ll be used as described below.

They can be used single or in arrays.

Dim Person as PersonalDetails

Person.Name=”Gyankosh.net”

Person.Address=”Address”

and so on.

If we define an array, it become easy to handle such data. Such as

Person(1) has some detail then Person (2) and so on.

OTHER WAYS TO REACH THIS ARTICLE

  • WHAT IS EXCEL, HOW EXCEL WORKS, WRITE FORMULA IN EXCEL, EXAMPLE OF USING FORMULA IN EXCEL, ANIMATED EXAMLPE OF USING FORMULA IN EXCEL

LEARN

Encourage us by following at …

If you have any queries or suggestions or any problem which you would like us to solve for you, kindly mail at

gyankosh060309@gmail.com

Pin It on Pinterest

Share This

KINDLY SPEND FEW SECONDS HERE....

%d bloggers like this: