VBA Variable Types

What are VBA Variable Types?

In Excel VBA, individuals can use different variable types[1] and constants in their worksheets. A variable is defined as storage in the computer memory that stores information to execute the VBA code.

The type of data stored in the variable depends on the type of data of the variable. For example, if a user wants to store integers in the variable, the data type will be an integer. A variable differs from a constant in that while the variable changes when the code is executed, the constant never changes.

Download VBA Cheat Sheet

Before you continue, use the form below to get access to our comprehensive VBA cheat sheet for an overview of key codes and macros, terminology, and best practices in Excel VBA.

Declaring Variables

By declaring a variable, the user provides information to the VBA compiler about the variable data type and other information such as the level. The data type can either be an integer, text, decimal, Boolean, etc., whereas the variable level can be either procedure level, module-level, or public scoop. Variables should be declared using Dim, Private, Public, or Static statements.

When declaring variables to hold an integer using the Dim statement[2], use the code “Dim rowNumber as Integer.” The term “Dim” should appear at the preceding part of a variable. If the data type is not specified when declaring the variable or the variable is not declared at all, the variable will default to the variable data type that can accommodate any data type. By declaring a variable, the user is essentially telling the computer to reserve memory space for later use, allowing the VBA code to run smoothly.

Why Variables are Used in VBA

When writing code in the VBA Editor, users need variables that they can use in the code to hold a value. The advantage of a variable over a constant is that users can change the value of the variable while the code is in progress.

Rules followed when naming a variable:

• The name is not a reserved keyword. VBA includes some reserved keywords ,such as Private, Next, Loop, etc., that are used in the VBA code and cannot, therefore, be used to name a variable.
• Don’t use special characters when naming variables. Special characters include ($, $, &, !, %).
• Spaces should not be used in the variable name. Instead, use an underscore character to separate values and make them readable.
• VBA is not case-sensitive. You can use mixed case to make variables readable, even though VBA will consider all cases similar.
• Using alphabets, numbers, and punctuations in the variable naming is allowed. That notwithstanding, the first number in the variable’s name should be an alphabet.
• The name of the variable should not exceed 255 characters in length.

Types of Data Types in VBA

The data type in VBA tells the computer the type of variable that the user intends to use. Different types of variables occupy a varied amount of space in the memory, and users should know how much space the variable will occupy in the computer memory beforehand.

A data type specifies the amount of space that a variable needs in the memory. The two main types of data types include:

1. Numerical data type

Numerical data types are used when the user needs to store numbers only. Examples of numerical data types include decimal, currency, long, single, integer, byte, date, and time. Some of these data types are explained below:

Byte: A byte comes with a small capacity, and it can hold values ranging from 0 to 255.

Integer: An integer is a beta version of the byte data type, and it can hold values ranging from -32768 to 32768. Any values that exceed this range will return an error. If decimal values are used in the integer, they will be converted to the nearest whole number. For example, 9.8 will be converted to 10.

Long: The Long data type holds longer values or numbers that the integer data type cannot hold. It holds values that exceed 32768. Long data type can hold values in the range of -2,147,483,648 to 2,147,483,648.

Single: The Single data type is designed for storing decimal values that do not exceed two-digit decimals. For positive values, the single data types range from 1.401298E-45 to 3.402823E+38, whereas negative values range from -3.402823E+38 to -1.401298E-45.

2. Non-numerical data types

Non-numerical data types include the values that are excluded by numerical data types. Examples of numerical data types include the following:

String: The String data type can hold two types of string values, i.e., fixed and variable-length strings.

Boolean: The Boolean data type is used when the expected output is either TRUE or FALSE.

Object: The Object data types include products of Microsoft. Examples of Excel objects include worksheets, sheets, range, etc.

Variant: The Variant data type is compatible with both numerical and non-numerical data types.

Related Readings

In order to help you become a world-class financial analyst and advance your career to your fullest potential, these additional resources will be very helpful:

• Excel VBA Examples
• How to Add a VBA Button in Excel?
• VBA Macros
• VBA Workbook Events
• See all Excel resources