VBA Difference between Dim Set

This Access/Excel VBA tutorial shows the difference between Dim and Set keyword in variable declaration.

You may also want to read:

Excel VBA Option Explicit to force declaration of variables

Difference between ByRef ByVal

Function, Sub, Method, Property Differences

VBA Difference between Dim and Set

In VBA, we can always see the use of Dim and Set, but in some occasions we don’t.

Example 1:

Dim rng as Range 'Optional
Set rng = Range("A1")

Example 2:

Dim s as String 'Optional
s = "I am a String"

I will explain the difference between Dim and Set  in this tutorial.

VBA Dim keyword

Dim Keyword is to declare a variable and assign memory to it. In Example 1, we assign memory to “s” and then store value “I am a String” to it. Whenever s is called, we reference to the value of variable in the memory.

A variable type can be Byte, Boolean, Integer, Long, Currency, Single, Double, Date, String (for variable-length strings), String * length (for fixed-length strings), Object, Variant, a user-defined type, or an object type. Object has Property, such as color, font size, formats, while dates/string/number do not.

You can use New keyword with  Dim to declare an Object instead of using Set, for example

Dim X As New Worksheet

Instead of declaring every variables (which is annoying), you can simply use a variable without using Dim to declare, because Excel can guess the variable type for you and initialize the value as 0, therefore I marked “Optional” in Example 1 and 2.

You may not be able to do so if you have used Option Explicit keyword at Module level, which forces you to declare all variables.

From my experience, you don’t have to declare variables as long as your code is short and clean, but it is better to declare in very long code so that it is easier to debug.

VBA Set keyword

Set Keyword is to assigns an object reference to a variable or property.

Using Example 2 as an example,  we assign memory to a variable called “rng” to store Range Object, and then reference Object Range(“A1″) to rng. Whenever rng is called, we reference to the Object in the memory.

From Example 1 and Example 2, we see that we are not actually determining whether to use Dim or Set (or comparing difference between Dim and Set), because Dim is required for both examples to assign memory to a variable in the first step. The real question is when we need to use the Set keyword, which is used when we reference an Object to a variable.

It is a must to use the Set key word, otherwise we will receive the error

Run-time error ’91′:

Object variable or With block variable not set

VBA Difference between Dim Set