What is the difference between dim and set in vba

Solution 1:

There's no reason to use set unless referring to an object reference. It's good practice to only use it in that context. For all other simple data types, just use an assignment operator. It's a good idea to dim (dimension) ALL variables however:

Examples of simple data types would be integer, long, boolean, string. These are just data types and do not have their own methods and properties.

Dim i as Integer
i = 5

Dim myWord as String
myWord = "Whatever I want"

An example of an object would be a Range, a Worksheet, or a Workbook. These have their own methods and properties.

Dim myRange as Range
Set myRange = Sheet1.Range("A1")

If you try to use the last line without Set, VB will throw an error. Now that you have an object declared you can access its properties and methods.

myString = myRange.Value

Solution 2:

• Dim declares the variable.

  Dim r As Range
  

• Set sets the variable to an object reference.

  Set r = Range("A1")
  

However, I don't think this is what you're really asking.

Sometimes I use:

    Dim r as Range
    r = Range("A1")

This will never work. Without Set you will receive runtime error #91 Object variable or With block variable not set. This is because you must use Set to assign a variables value to an object reference. Then the code above will work.

I think the code below illustrates what you're really asking about. Let's suppose we don't declare a type and let r be a Variant type instead.

Public Sub test()
    Dim r
    debug.print TypeName(r)

    Set r = Range("A1")
    debug.print TypeName(r)

    r = Range("A1")
    debug.print TypeName(r)
End Sub

So, let's break down what happens here.

1. r is declared as a Variant

   `Dim r` ' TypeName(r) returns "Empty", which is the value for an uninitialized variant
   

2. r is set to the Range containing cell "A1"

   Set r = Range("A1") ' TypeName(r) returns "Range"
   

3. r is set to the value of the default property of Range("A1").

   r = Range("A1") ' TypeName(r) returns "String"
   

In this case, the default property of a Range is .Value, so the following two lines of code are equivalent.

r = Range("A1")
r = Range("A1").Value

For more about default object properties, please see Chip Pearson's "Default Member of a Class".

As for your Set example:

Other times I use

Set r = Range("A1")

This wouldn't work without first declaring that r is a Range or Variant object... using the Dim statement - unless you don't have Option Explicit enabled, which you should. Always. Otherwise, you're using identifiers that you haven't declared and they are all implicitly declared as Variants.

Solution 3:

Dim: you are defining a variable (here: r is a variable of type Range)

Set: you are setting the property (here: set the value of r to Range("A1") - this is not a type, but a value).

You have to use set with objects, if r were a simple type (e.g. int, string), then you would just write:

Dim r As Integer
r=5

Solution 4:

Dim simply declares the value and the type.

Set assigns a value to the variable.