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 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 theRange
containing cell "A1"Set r = Range("A1") ' TypeName(r) returns "Range"
-
r
is set to the value of the default property ofRange("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.
Solution 5
If a variable is defined as an object e.g. Dim myfldr As Folder, it is assigned a value by using the keyword, "Set".
Ram
I am a developer learning Java, J2EE and Ruby. I have about 5 years of experience mostly of Core Java, Eclipse plugins, Servlets. Am currently trying out Ruby and related technologies.
Updated on July 08, 2022Comments
-
Ram almost 2 years
Pardon me as am a newbie in VBA.
Sometimes I use
Dim r as Range r = Range("A1")
Other times I use
Set r = Range("A1")
What is the difference? And when should I use what?
-
Ram over 13 yearsCan I please know which tutorial or book did you refer to understand this?
-
kizzx2 over 12 yearsThis answer doesn't really explain the "why"
-
Rapid over 10 yearsVBA is very clever, it doesn't require you to tell it what the hell you're doing like a lot of languages. This however adds time. If you are using a whole host of different dimensions on all sorts of different varients then it adds up the time. If you tell VBA what to expect when it sees a variable then it doesn't have to work it out. Also if you tell VBA that a variable is an integer not a string, then it won't take up as much RAM. Although the latter point is probably not as valid in common small VBA projects it's still good coding practise.
-
Wolf over 7 years@PierreClaverie Yes :) it's including the original references for Dim and Set
-
RubberDuck over 7 years@Wolf not sure if you're aware, but the VBA language ref is now maintained on github. github.com/OfficeDev/VBA-content/blob/master/VBA/…
-
Wolf over 7 years@RubberDuck I was not aware (I'm new to vb*), thanks for adding this note.
-
RubberDuck over 7 yearsYou're welcome @Wolf. I know the VBA & old VB6 docs can be hard to find these days.
-
solstice333 over 5 yearsis it okay to use
Set
withoutDim
ing the variable first?