VARIABLES AND DATA TYPES IN VBA
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.
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.
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|
|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|
+/-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
|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
|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,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
|Object||4 bytes||Any 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 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 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
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
- Within a sub procedure or function
- Within a module
- Within a project
MODULE VARIABLES are declared the same way as local variables but before the first procedure of the module.
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
const ModName as String = “HELLO”
Public Const AppName as String = “GYANKOSH.NET”
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
|Exponentiation (||Equality (||Not|
|Negation (||Inequality (||And|
|Multiplication and division (||Less than (||Or|
|Integer division (||Greater than (||Xor|
|Modulus arithmetic (||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 2= VALUE
AND SO ON….
Now if we declare an object variable such as
Dim Table as Range
Set Table= Worksheets(“Sheet1”).Range(“A1”)
and so on.
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.
Name As String
Address As String
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
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
YOU MAY LIKE
- HOW TO TROUBLESHOOT A FORMULA IN EXCEL?
- USE OF FONTS, FONT SIZE, INDENTATION, TEXT TO THE RIGHT, TEXT TO THE LEFT, TEXT IN THE MIDDLE ETC.
- how to insert table in excel?
- FOR MORE ARTICLES, CLICK EXCEL OR TYPE IN SEARCH BELOW.