VBA Dim Statement

What is the VBA DIM Statement?

VBA DIM statement stands for “declare” that you must use when you need to declare a variable. When you use it, it tells VBA that you are declaring a specific name as a variable as assigning a specific data type to it. It can be used to declare the following:

• Basic Variables
• Variants
• Objects
• Array

Use DIM Statement in VBA

1. First of all, type the keyword Dim to declare a variable.
2. After that, enter the name of the variable that you want to use. In our example, we are using the name myVariable.
3. Next, you need to use the word “as” and you’ll get an instant list of data types.
4. In the end, choose the data type that you want to assign to the variable.

As I said when you declare a variable you need to assign a data type with that. See the following snapshot.

Declaring Multiple Variables with One Dim Statement

When you need to declare more than one variable you can do that by using the Dim statement for a single time. Consider the following example.

Using Option Explicit

This is highly recommended to declare all the variable which makes your code perform better and increase the efficiency as well. But if you skip declaring a variable and then use it in a code, VBA will take that variable as a variant.

The best way to never miss to declare a variable is to use the Option Explicit statement that forces you to declare a variable and shows an error every time you miss it.

Dim Statement with Arrays

You need to use the DIM statement when you need to declare an array as well. Consider the following example that I have shared.

In the above example, you have two arrays that have been declared with the dim statement.

In the first array, you have three items and in the second, there’s no item declared. Here’s the point when you need to declare an array you need to use parentheses to declare items of the array and blank parentheses if you want a dynamic array.

Use DIM with Excel Objects

You can also refer to an object using the DIM statement. Let’s say you want to count sheets from a workbook, you can declare that an object and count the worksheet from it.

In the above example, you have declared wb as a workbook then set the workbook “book1” to use it further in the code.

Where to Put the Dim Statement?

You can use declare a variable using the DIM statement anywhere within the code but, make sure to declare it before you use it. So let’s say you are referring to a variable in line 2 of the code and you have used the Dim in line 3 to declare, well, you going to get an error.

So, make sure to declare a variable before you want to use it.