[vba] What does the keyword Set actually do in VBA?

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.

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

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.

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"

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)

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

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

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.

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)

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

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

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.

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)

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.

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.

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.

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.

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"