What is Dim?

DIM or Dimension or Declare in Memory is a keyword that is used in VBA to declare a variable with the different data types (Integer, String, variable, Boolean, Double, etc.) and allocate the storage space as per the specified data type. With the help of Dim, we can also declare either the inbuilt class structure or can declare one created by ourselves. The variable declared with Dim can be used anywhere in the VBA code and all Dim statements are used at the beginning of each Sub or Function procedure.

In any programming language, declaring a variable means specifying the application regarding the variable that we want to use later. For instance, if we’re going to declare any variable with the integer data type, it signifies we can only store integer values in it that particular variable else for any other value apart from int it would throw a type mismatch error.

In VBA, Dim statements are of four types which are as follows: 

  1. Basic variable
  2. Variant
  3. Object
  4. Array

Basic variable – The Basic variable holds value at a time. It uses the commonly used data types in every programming language such as Integer, String, Long, Boolean, Double, Currency, Data, etc.,

Variant – The variant is used when the data type of the variable is not known prior and is decided by the VBA application at runtime. It is usually avoided as it takes the maximum storage space as compared to the basic variable. But still is used in many cases it is a requirement to use them.

Object – In VBA, the object variable can contain data and is associated with multiple methods and properties. The Object variable can also contain other objects. With Dim keyword, you want to use three types of objects which are as follows:

  • Excel objects- Workbook, Worksheet, Sheet, and Range objects.
  • User-Defined objects- Class Modules.
  • External library objects- Dictionary.

Array – Array is known as a group of variables or objects and can hold more than one piece of data. With Dim keyword, you can declare the array as:

  • Static Array
  • Dynamic Array

Is Dim required in VBA?

In VBA, it is not mandatory to use a Dim statement. If the programmer does not use Dim, the compiler will not throw any error. You can even use the variable without declaring the Dim, and in that case, it will be automatically considered as a variant type. However, it is always it is advisable to make it a necessary practice as VBA code without Dim statements are considered as poor code and can generate many problems such as:

  1. All variables are by default considered as variants. A variant is set to 16 bytes, which is the most significant variable type. Thus, taking the maximum storage and increasing the compile time.
  2. Some variable errors will go undetected. VBA will not detect the runtime errors (i.e., Data Mismatch).
  3. VBA also cannot identify the compile-time errors.
  4. It disables the intelliSense feature (this feature will automatically display the available options for the variable where you type only the first few letters and VBA displays the list).

Syntax of Dim Variable

  1. BASIC VARIABLE

Syntax

Parameters used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

VBA Dim

Output

VBA Dim
  • FIXED STRING

Syntax

Parameter Used

Variable name-

Type (optional)– It represents the data type for the variable. The default value is Variant.

 Size (optional)– This parameter denoted the string length.

Code

VBA Dim

Output

VBA Dim
  • VARIANT

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Code

VBA Dim

Output

VBA Dim
  • OBJECT

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

VBA Dim
  • OBJECT USING NEW

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

In the below code we want to read through a range of data. It will only create an object if the range value is greater than 40. At last, we will use Set to create the ClassModule object. 

VBA Dim
  • OBJECT USIGN SET AND NEW

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

  • STATIC ARRAY

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

First (optional) – It represents the upper bound of the Array variable. The default value is 1.

Last (optional)– It represents the lower bound of the array variable. The default value is 1.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

VBA Dim

Output

VBA Dim
  • DYNAMIC ARRAY

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

First (optional) – It represents the upper bound of the Array variable. The default value is 1.

Last (optional)– It represents the lower bound of the array variable. The default value is 1.

Type (optional)– It represents the data type for the variable. The default value is Variant.

Code

VBA Dim

Output

VBA Dim
  • EXTERNAL LIBRARY

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Item – This parameter represents the inbuilt classes or user-designed classes.

Code:

  1. EXTERNAL LIBRARY USING SET

Syntax

Parameter Used

Variable name (required)– It represents the name of the variable and it should be as per the standard variable naming conventions.

Item – This parameter represents the inbuilt classes or user-designed classes.

Code

Using Dim with Multiple Variables

We can declare multiple variables in a single Dim statement. It will help you to make your code shorter. In big program, it saves your compile time.

Syntax

Code

VBA Dim

Output

VBA Dim

Pin It on Pinterest

Share This