169

Hopefully an easy question, but I'd quite like a technical answer to this!

What's the difference between:

i = 4

and

Set i = 4

in VBA? I know that the latter will throw an error, but I don't fully understand why.

ZygD
  • 22,092
  • 39
  • 79
  • 102
Jon Artus
  • 6,268
  • 11
  • 42
  • 41

7 Answers7

106

set is used to assign a reference to an object. The C equivalent would be 

 int i;
int* ref_i;

i = 4; // Assigning a value (in VBA: i = 4)
ref_i = &i; //assigning a reference (in VBA: set ref_i = i)
TylerH
  • 20,799
  • 66
  • 75
  • 101
Treb
  • 19,903
  • 7
  • 54
  • 87
  • 5
    A VB object reference is not quite the same as a C pointer. And there is no equivalent of "&i" in VB. – Tomalak Dec 08 '08 at 14:02
  • 13
    No quite the same, no. But close enough for me to understand the concept. – Treb Dec 08 '08 at 15:09
  • @Tomalak: You could use VarPtr() – Atmocreations Nov 10 '09 at 21:06
  • This is not a good analogy. Take this example (`VBA on the left | C on the right`): `Dim A, B As Range | Range A, B;`. Going with your analogy, `A = B | A = B;` would be correct (and it would be in C), but `Set A = B | A = &B;` is actually correct in VBA (and it would fail in C). In VBA, `A = B` and `Set A = B` are BOTH equivalent to C's `A = B;`! The distinction happens somewhere else. – Niko O May 25 '20 at 08:18
  • @NikoO minor nitpick, but your A variable on the left side is of type Variant, not Range. – SSlinky Feb 04 '21 at 05:43
82

In your case, it will produce an error. :-)

Set assigns an object reference. For all other assignments the (implicit, optional, and little-used) Let statement is correct:

Set object = New SomeObject
Set object = FunctionReturningAnObjectRef(SomeArgument)

Let i = 0
Let i = FunctionReturningAValue(SomeArgument)

' or, more commonly '

i = 0
i = FunctionReturningAValue(SomeArgument)
Tomalak
  • 332,285
  • 67
  • 532
  • 628
49

From MSDN:

Set Keyword: In VBA, the Set keyword is necessary to distinguish between assignment of an object and assignment of the default property of the object. Since default properties are not supported in Visual Basic .NET, the Set keyword is not needed and is no longer supported.

Galwegian
  • 41,475
  • 16
  • 112
  • 158
  • Well found, but a link to the article you found on MSDN would be even better :) – Neil Barnwell Dec 08 '08 at 14:01
  • 2
    @Neil - the link is there if you click MSDN in my post. – Galwegian Dec 08 '08 at 14:10
  • 3
    When copying off the MSDN, then at least the correct article. This one is referring to VB.NET, not to VBA. – Tomalak Dec 08 '08 at 14:33
  • 2
    OP is asking about VBA, and although the info on Set being no longer necessary for use in .NET is useful, it's a off topic and not helpful for people arriving here with the `Object variable or With block variable not set` error from VBA :) – AJP Mar 08 '12 at 22:18
  • 7
    Thank you very much for the extract from MSDN. All other answers, even the accepted one are missing the point. 'set' is all about the DEFAULT PROPERTY. Just read http://stackoverflow.com/a/9924325/717732 – quetzalcoatl Aug 07 '12 at 10:33
  • Just have read the page you mentioned, the author asserts just the opposite: `If you want to assign the object reference then you have to write: Set obj = someObject` – indexless Sep 09 '13 at 10:11
9

Set is used for setting object references, as opposed to assigning a value.

LeppyR64
  • 5,251
  • 2
  • 30
  • 35
1

Off the top of my head, Set is used to assign COM objects to variables. By doing a Set I suspect that under the hood it's doing an AddRef() call on the object to manage it's lifetime.

Sean
  • 60,939
  • 11
  • 97
  • 136
  • 1
    It's not only used for COM objects, but for all objects. The main reason you use SET is explained by Galwegian. – Ikke Dec 08 '08 at 14:21
0

So when you want to set a value, you don't need "Set"; otherwise, if you are referring to an object, e.g. worksheet/range etc., you need using "Set".

Eric Wang
  • 1,009
  • 1
  • 9
  • 16
-1

Set is an Keyword and it is used to assign a reference to an Object in VBA.

For E.g., *Below example shows how to use of Set in VBA.

Dim WS As Worksheet

Set WS = ActiveWorkbook.Worksheets("Sheet1")

WS.Name = "Amit"

Amit Singh
  • 1