Table of Contents
- INTRODUCTION
- DATA TYPES
- HOW TO DECLARE VARIABLES IN VBA FOR EXCEL?
- SCOPE OF A VARIABLE
- DECLARING CONSTANTS IN VBA
- OPERATORS ARE SAME AS IN EXCEL [CLICK HERE TO READ ABOUT OPERATORS]
- SOME ADDITIONAL OPERATORS AND THEIR EFFECT
- OPERATOR PRECEDENCE IN VBA
- USING OBJECTS AS VARIABLES
- CUSTOM VARIABLES
INTRODUCTION
The programming languages mainly solve our problems with the help of manipulating data.
Applying different operations to 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 Variables.
So,
A Variable is a name or reference given to a location in the computer’s memory that can be used to save any value of any type for further use in any functions or procedures.
Now if we have understood the variable, the next thing that comes is the DATATYPE.
As we know that variables hold the values, and what kind of values are being held, are called data types.
The various data types can be boolean (binary), integer, text, string, 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 type | Storage size | Range |
|---|---|---|
| Boolean | 2 bytes | True or False |
| Byte | 1 byte | 0 to 255 |
| Collection | Unknown | Unknown |
| Currency(scaled integer) | 8 bytes | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 |
| Date | 8 bytes | January 1, 100, to December 31, 9999 |
| Decimal | 14 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 decimalSmallest non-zero number is+/-0.0000000000000000000000000001 |
| Dictionary | Unknown | Unknown |
| Double (double-precision floating-point) | 8 bytes | -1.79769313486231E308 to -4.94065645841247E-324 for negative values4.94065645841247E-324 to 1.79769313486232E308 for positive values |
| Integer | 2 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,807Valid on 64-bit platforms only. |
| LongPtr (Long integer on 32-bit systems, LongLong integer on 64-bit systems) | 4 bytes on 32-bit systems8 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 |
| Object | 4 bytes | Any Object reference |
| Single (single-precision floating-point) | 4 bytes | -3.402823E38 to -1.401298E-45 for negative values1.401298E-45 to 3.402823E38 for positive values |
| String (variable-length) | 10 bytes + string length | 0 to approximately 2 billion |
| String (fixed-length) | Length of string | 1 to approximately 65,400 |
| Variant (with numbers) | 16 bytes | Any 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 elements | The 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 IS USED WHEN IN VBA FOR EXCEL ?
Any data type can be used but it should always be planned before designing the program.
Here are a few things that 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 the lowest bytes and which can hold all our values so that our program is fast and efficient.
There shouldn’t come any value that our data type can’t handle at any time else it’ll cause an error or wrong answer.
DEFAULT DATA TYPE If we don’t declare any data type, as you may have noticed in the previous examples, VARIANT is taken by the VBA itself.
Variant has a few beautiful features which we’ll discuss later.
HOW TO DECLARE VARIABLES IN VBA FOR EXCEL?
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, it’s 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 means 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 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
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 functions in the project.
DECLARING CONSTANTS IN VBA
CONSTANTS are the values that 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, the loan interest rates will appear frequently.
We know that interest rates are changed frequently by the central bank.
Suppose there is a change in the interest rate. It means 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 Mod
Name as String = “HELLO”
Public Const App
Name as String = “GYANKOSH.NET”
Now , whoever interest rate is needed, we’ll use Int in place of the number 7.5
THE SCOPE OF CONSTANTS ALSO WORKS THE SAME AS THE VARIABLES
OPERATORS ARE SAME AS IN EXCEL [CLICK HERE TO READ ABOUT OPERATORS]
SOME ADDITIONAL OPERATORS AND THEIR EFFECT
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
OPERATOR PRECEDENCE IN VBA
| Arithmetic | Comparison | Logical |
|---|---|---|
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 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 let us create custom or user-defined variables, and 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 becomes easy to handle such data. Such as
Person(1) has some detail then Person (2) and so on.