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.
-
4Backgrounder here: stackoverflow.com/a/9924325/17034Hans Passant– Hans Passant2012-04-19 19:33:47 +00:00Commented Apr 19, 2012 at 19:33
Add a comment
|
7 Answers
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)
5 Comments
Tomalak
A VB object reference is not quite the same as a C pointer. And there is no equivalent of "&i" in VB.
Treb
No quite the same, no. But close enough for me to understand the concept.
Atmocreations
@Tomalak: You could use VarPtr()
Niko O
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.
SSlinky
@NikoO minor nitpick, but your A variable on the left side is of type Variant, not Range.
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)
1 Comment
DIV
To extend this to the OP's query, "I know that the latter will throw an error, but I don't fully understand why", it seems the issue is that
4 is a literal, not an object, and therefore cannot be referenced. Likewise 0 or "Hello world". learn.microsoft.com/en-us/dotnet/visual-basic/programming-guide/…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.
6 Comments
Neil Barnwell
Well found, but a link to the article you found on MSDN would be even better :)
Galwegian
@Neil - the link is there if you click MSDN in my post.
Tomalak
When copying off the MSDN, then at least the correct article. This one is referring to VB.NET, not to VBA.
AJP
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
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
|
Set is used for setting object references, as opposed to assigning a value.
Comments
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.
1 Comment
Ikke
It's not only used for COM objects, but for all objects. The main reason you use SET is explained by Galwegian.
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".
1 Comment
DIV
Stronger than "don't need": rather "cannot use".
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"
