VARIABLES AND DATA TYPES IN VBA

Table of Contents

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 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 decimalSmallest non-zero number is+/-0.0000000000000000000000000001
DictionaryUnknownUnknown
Double (double-precision floating-point)8 bytes-1.79769313486231E308 to -4.94065645841247E-324 for negative values4.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,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
Object4 bytesAny 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 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.
DIFFERENT DATA TYPES, THEIR SIZE, AND RANGE IN VBA FOR EXCEL

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

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 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.