VBA Data Types, Variables and Constants

This “VBA Variables, Data Types & Constants”  topic covers the concept of what variables and constants are. Knowing the difference between them and where to use them is really important. 

What is a Variable?

A variable is a value that is declared in the code and used to store the value in the computer’s memory. It is very essential to declare your variable names and the data types. The data type defines which type of data will be stored in the variable. 

Syntax:

Sub Module ( )  
Dim  Variable_Name As Variable_DataType 
End Sub

In VBA, you can either declare a variable Implicitly or Explicitly.

1. Implicitly:

If the user is not sure about the data type and if they are likely to change, then they can declare the data type as a variant. The variant data type can store all kinds of data except the fixed-length string data type. Below is an example of a variable declared Implicitly.

Example: Dim myArticle as Variant

2. Explicitly: When you use the “Dim” keyword in syntax, it’s declared explicitly.

Example: Dim age As Integer

To ensure Excel adds the variables to the option explicitly in the VBE, follow the below steps:

1: Go to the tools menu and click on the options. 

2: Under the editor label, make sure that there is a tick mark available beside the required variable declaration. 

How do VBA variables get started?

Before executing the variables, we have to record a macro in the spreadsheet. To record a macro, follow the steps: 

1: Go to the view tab and choose a macro.

2: Provide a name to the macro and click on a new button.

3: Record the Macro.

4: Stop the Macro, once it’s done.

5: Now, click on the edit button in the dialog box to open the vba code editor.

6: Make the changes in the code and execute it.

Now, let’s make use of a Dim statement in VBA to declare the variable. To declare a variable in the VBA. code editor is demonstrated below.

Sub Variable()
Dim DataFlair As String
Dim articles as Integer
End Sub

Once the declaration is done, the variable reserves space in the memory to initialize the variable.

Using Variable in VBA

The below code explains how to initialize a variable in VBA

Sub Initialize()
    Dim articles As Integer
    articles = 5
    MsgBox articles
End Sub

When you save and run the code, you get the following output:

Declaring a Variable in VBA

To declare a variable, follow the below steps:

1: Open an Excel workbook and save it as an Excel macro-enabled workbook. 

2:  Add a button using macros. 

In order to create a macro, 

• Go to the Developer tab and click on insert from the ribbon.
• Choose the command button.
• Assign a macro by providing a name and click on the new button

3: Finally, press ok 

4: Once, the code window appears, type the following code.

Sub Sheet5_Button1_Click()
    Dim name As String, articles As Integer
    name = "DataFlair"
    articles = 2500
    Range("A1") = name
    Range("A2") = articles
End Sub

The user writes the code in the VBA editor. Your GUI appears as follows:

Output

Before naming your variable, you have to ensure that:

1. The variable name is not a reserved keyword. Few reserved keywords are such as Dim, Private, Function, Loop etc.

2. Do not name your variable following a keyword.

3.  Do not use special characters such as !, @, &, ., #

4. Make sure there is no blank between a variable name.

5. The variable name should not exceed 255 characters in length.

6. A variable name cannot start with a number.

Some invalid variable names are 2day, user.name. Some valid variable names are: to_day, costprice.

Pros of Excel VBA Variable Type

1. Using variables, the user can write structured VBA code.

2. Each variable has its capacity, limit, and application property.

3. Variables help the user to declare different types of data as per their requirement.

Data Types in VBA

There are many data types that can be used in VBA. However, there are some common data types that you will be using more frequently. They are:

1. String – this data type stores the text values.

2. Boolean – this data type stores the TRUE or FALSE values.

3. Integer – this data type stores the whole number values.

4. Double – this data type stores the decimal values.

5. Date – this data type stores the dates.

Note: You have to use quotation marks when the value is stored in string, date data type. 

Syntax:

Dim Variable_Name As DataType

VBA String Data Type Example

Sub Sheet6_Button1_Click()
Dim user_name As String
user_name = “DataFlair”
MsgBox suser_name
End Sub

Output:

Date Data Type Example

Sub Sheet6_Button1_Click()
Dim start_date As Date
start_date = "5/5/2021"
MsgBox start_date
End Sub

Output:

Boolean Data Type Example

Sub Sheet5_Button1_Click()
Dim boolVar As Boolean
boolVar = FTrue
If boolVar = True Then
  Range("A1") = "DataFlair Articles are Available"
Else
  Range("A1") = "There is No Article"
End If
End Sub

Once you save and run the code, you get the following output:

Integer Data Type Example

Sub Sheet6_Button1_Click()
Dim a As Integer
a = 650
MsgBox a
End Sub

Once you save and run the code, you will get the following output:

Double Data Type Example

Sub Sheet6_Button1_Click()
Dim a As IDouble
a = 17.2
MsgBox a
End Sub

Once you save and run the code, you will get the following output:

VBA Data Types

How to declare a variant data type in VBA?

We use variant data types when we are not sure about which data type to use. The variant data type cannot store fixed-length string data type alone. 

Syntax:

Dim myValue as Variant

Using Option Explicit in VBA

To go to options, click on the tools and choose options from the editor. Then, check the mark on the required variable declaration.

Note: When we use Option Explicit in VBA, we can declare all the required variables. 

What is a Constant in VBA?

A constant is more like a variable but it stores a value that cannot be changed. 

The user uses the public keyword to declare constant in the public module level and private keyword to declare a private constant and this makes the code easier to read. 

There are two types of constant:

1. Intrinsic Constants 

2. User Defined Constants 

Intrinsic Constants

These are the constants provided by the application itself or we could say that the functions which are built-in are known as intrinsic constants.

User-Defined Constants in VBA

These are the constants that are user-defined

and created for their purposes. The user can create the constant by specifying a value to the declared constant. 

Follow the rules to create a constant:

1. Declare the constant_name preceding with CONST. 

2. Use the word “As” to declare the DataType.

3. Assign the value to the variable with an equal to(=) sign. 

Syntax:

Const Constant_Name As Constant_DataType = Constant_Value

Here, Constant_Name and Constant_Value are the required parameters and the Constant_DataType is an optional parameter. 

The below code demonstrates how to declare a constant in VBA:

Sub Constant()
Const NumberofDays = 1
MsgBox NumberofDays
End Sub

Output:

Before naming a constant, you have to ensure that:

1. The first character is a letter in the constant name.

2. Do not use special characters such as !, @, &, ., #

3. The name should not exceed 255 characters in length.

4. The name is not a reserved keyword. 

Declaring a Constant in VBA

You can also declare a constant as Private and this private constant can be accessed in its own module alone or if you can declare a constant as Public then it means that you can use it in other modules also.

Pros of Excel VBA Constants

1. Declaring one or more variables as a constant would save a lot of time. 

2. This widely helps in reducing the lines of codes. 

3. Once the constants are declared with the values and then whenever the user calls the constant, the value stored comes up. 

Cons of Excel VBA Constants

Sometimes the user has to keep changing the value that is stored in constants if they are using it in different subcategories or classes. 

Summary

Now, we have learnt how to declare a data type, constant and variable in VBA. We also know the differences between the variable and constant. Variables are used to store the value in computer memory.

Constants are very much useful to reduce the code lines. The constants are declared and assigned as per the user’s convenience. There are also many data types and the data type tells about the type of the data.