By Jon Artus


2008-12-08 13:54:30 8 Comments

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.

7 comments

@Treb 2008-12-08 14:00:24

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)

@Tomalak 2008-12-08 14:02:45

A VB object reference is not quite the same as a C pointer. And there is no equivalent of "&i" in VB.

@Treb 2008-12-08 15:09:47

No quite the same, no. But close enough for me to understand the concept.

@Atmocreations 2009-11-10 21:06:58

@Tomalak: You could use VarPtr()

@Eric Wang 2014-09-05 07:37:42

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".

@Amit Singh 2013-11-11 05:05:59

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"

@Tomalak 2008-12-08 13:57:56

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)

@Sean 2008-12-08 13:59:49

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.

@Ikke 2008-12-08 14:21:45

It's not only used for COM objects, but for all objects. The main reason you use SET is explained by Galwegian.

@Galwegian 2008-12-08 13:58:45

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.

@Neil Barnwell 2008-12-08 14:01:29

Well found, but a link to the article you found on MSDN would be even better :)

@Galwegian 2008-12-08 14:10:58

@Neil - the link is there if you click MSDN in my post.

@Tomalak 2008-12-08 14:33:06

When copying off the MSDN, then at least the correct article. This one is referring to VB.NET, not to VBA.

@AJP 2012-03-08 22:18:22

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 :)

@quetzalcoatl 2012-08-07 10:33:00

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 stackoverflow.com/a/9924325/717732

@indexless 2013-09-09 10:11:08

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

@LeppyR64 2008-12-08 13:57:41

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

Related Questions

Sponsored Content

26 Answered Questions

[SOLVED] Why does this go into an infinite loop?

2 Answered Questions

[SOLVED] VBA Difference between Set and Assignment

  • 2018-05-13 18:47:53
  • Nicholas Humphrey
  • 463 View
  • 2 Score
  • 2 Answer
  • Tags:   vba parsing

0 Answered Questions

Import C++ DLL Library in VBA

1 Answered Questions

[SOLVED] Word-VBA: checkbox range

  • 2016-12-05 15:45:59
  • user7241882
  • 96 View
  • 0 Score
  • 1 Answer
  • Tags:   vba word-vba

1 Answered Questions

1 Answered Questions

[SOLVED] Reason for setting variables in VBA

2 Answered Questions

[SOLVED] Set VBA Range with Variable End

  • 2013-10-29 21:17:11
  • idalsin
  • 6818 View
  • 1 Score
  • 2 Answer
  • Tags:   excel excel-vba vba

2 Answered Questions

[SOLVED] Excel VBA Conditional Filter

3 Answered Questions

[SOLVED] VBA assigning new object to variable?

2 Answered Questions

[SOLVED] Why not set the value directly

Sponsored Content