Dim
simply declares the value and the type.
Set
assigns a value to the variable.
If a variable is defined as an object e.g. Dim myfldr As Folder, it is assigned a value by using the keyword, "Set".
Dim
is short for Dimension and is used in VBA and VB6 to declare local variables.
Set on the other hand, has nothing to do with variable declarations. The Set
keyword is used to assign an object variable to a new object.
Hope that clarifies the difference for you.
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
According to VBA help on SET statement it sets a reference to an object.so if you change a property the actual object will also changes.
Dim newObj as Object
Set var1=Object1(same type as Object)
Set var2=Object1(same type as Object)
Set var3=Object1(same type as Object)
Set var4=Object1(same type as Object)
Var1.property1=NewPropertyValue
the other Vars properties also changes,so:
Var1.property1=Var2.property1=Var3.property1=Var4.property1=Object1.Property1=NewpropertyValue`
actualy all vars are the same!
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.
r
is declared as a Variant
`Dim r` ' TypeName(r) returns "Empty", which is the value for an uninitialized variant
r
is set to the Range
containing cell "A1"
Set r = Range("A1") ' TypeName(r) returns "Range"
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.
Source: Stackoverflow.com