Variables in VBA – Declare, Initialize, Examples

Variables are one of the basic building blocks of any programming language. Today we will learn about different variables in VBA, naming conventions for a variable, declaring and initializing the variable.

So let us begin learning all of this.

What are Variables?

Variables act as containers that are assigned storage space in your computer’s main memory when we declare it. We use variables to initialize the value or store intermediate results of our VBA program. The value of a variable can change during the program execution.

The memory space assigned to the variable is automatically deallocated once the program execution completes.

We can use variables to store numbers, dates, text strings, boolean values, and much more.

Declaring and Initializing a Variable in VBA

When we declare a variable, a block of main memory is allocated to that variable. We can declare a variable anywhere in between the VBA program. It is a good practice to initially declare variables at the top of our code.

There are two types of variable declarations in Visual Basic for Applications.

• Explicit Declaration
• Implicit Declaration

Syntax to Declare a Variable – Explicit Declaration

When we declare a variable using the Dim keyword, then the declaration is explicit. We need to specify the name of the variable as well as its data type. If you do not specify the data type of the variable, then it would become a variant whose data type is assigned automatically by Excel depending on the type of value it contains.

Dim <variable_name> As <data_type>

Data Types tell the amount of memory allocated to the variable as well as the type of value we can store in the variable.

For instance, you can create a Sub Procedure to implement the concept of variables.

Sub Declaring_variables()
    Dim Name As String              'here we declare the variables
    Dim birth As Date
    Dim marks As Integer
    Dim pass As Boolean
    
    Name = "John"                   'now we assign values to these variables
    birth = "10-10-2002"
    marks = 86                      'assigning values to variables in known as initialization
    pass = True
    
    MsgBox "Name is:- " & Name       'we are using the variables to print the values
    MsgBox "Date of birth is:= " & birth
    MsgBox "Marks are:= " & marks
    MsgBox "Did he pass?  := " & pass
End Sub

• First, we declare four variables Name, birth, marks, and pass.
• We assign values to these variables using the assignment operator =. This is known as variable initialization when we assign value to the variable after its declaration.
• We print the value contained in these variables using the Msgbox.

Now follow run this macro to see the resultant message boxes printing the values of the variables.

Implicit Declaration of Variables

When you do not use the Dim statement followed by the variable name and its data type, but directly assign value to the variable, then it is called implicit declaration of variable.

For instance, this is implicit declaration of variable.

Sub Impl_Decl()
age=20                  'NO DIM STATEMENT IS USED, WE ARE DIRECTLY ASSIGNING VALUE TO age
Msgbox age
End Sub

When we use implicit declaration, then we are declaring a variant type variable.

It is generally a good practice to explicitly declare the variable specifying its data type because a variant consumes more memory space and can lead to name conflicts.

This was how we declare variables in VBA.

Naming a Variable in VBA

We assign name to the variable at the time of its declaration. We can use this name to access the value that the variable contains in it. There are certain rules to follow while naming a variable.

• A variable name must not contain space, period, an exclamation mark (!), or other characters like @, $, #, or &.
• The first character must be an alphabet or an underscore (_) in the variable’s name.
• The length of the name must not exceed 255 characters.
• We cannot have two variables with the same name under one scope.
• Variable named are case sensitive. For instance, variables having names var and Var would be assigned different memory locations.

Here we have some examples of correct and incorrect variable names in VBA.

This brings us to an end.

Thank you for reading.