105

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?

Improve this question
1
  • I made 2 edits to this which were rolled back... just for discussion... the first was removing "in VBA" from the title since that repeats a tag which I thought was against guidelines and redundant. The second was removing the [excel] tag since although an Excel example is given, the core question is really not specific to Excel. And this is the dupe target for stackoverflow.com/q/349613/3195477 which is a totally general question about this topic. (Maybe the dupe should actually go the other way...) Commented Apr 15, 2023 at 14:54

7 Answers 7

Reset to default
92

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
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Can I please know which tutorial or book did you refer to understand this?
This answer doesn't really explain the "why"
VBA 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.
is it okay to use Set without Diming the variable first?
76

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.

Improve this answer

4 Comments

@PierreClaverie Yes :) it's including the original references for Dim and Set
@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/…
@RubberDuck I was not aware (I'm new to vb*), thanks for adding this note.
You're welcome @Wolf. I know the VBA & old VB6 docs can be hard to find these days.
13

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
Improve this answer

Comments

5

Dim simply declares the value and the type.

Set assigns a value to the variable.

Improve this answer

Comments

1

If a variable is defined as an object e.g. Dim myfldr As Folder, it is assigned a value by using the keyword, "Set".

Improve this answer

Comments

1

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!

Improve this answer

Comments

1

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.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.