When to use 'Set'

[VegasNath]

I am maybe confusing myself today...

Workbooks.Open ThisWorkbook.Path & "\" & "xyz.xlsm"
Set wb1 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & "xyz.xlsm")

Is there a specific reason / rule for when Set should / should not be used?

[HansV]

Variables of type Long, String etc. are "simple". They have a value (or a series of values if they are array variables) and that's it. You don't use Set for such variables.

Dim strName As String
strName = "Nathan"

Variables of type Worksheet, Range etc. are objects. They have many properties and methods (and sometimes events). You can use these by typing a dot (period) after the variable name and specifying the name of the property or method.
You can find them in the Object Browser (press F2 in the Visual Basic Editor). Object variables require the keyword Set.

Dim rngCell As Range
Set rngCell = Range("A1")
rngCell.Value = "Green"
rngCell.Interior.Color = vbRed

[StuartR]

In the specific example that you used

Code: Select all

Workbooks.Open ThisWorkbook.Path & "\" & "xyz.xlsm"

will open the workbook xyz.xlsm, but won't define an object pointing to the newly pointed workbook

Code: Select all

Set wb1 = Workbooks.Open(Filename:=ThisWorkbook.Path & "\" & "xyz.xlsm")

will open exactly the same workbook, and will also define the variable wb1 to point to the newly open workbook, so that you can then use wb1 in subsequent lines of code to do things to the workbook.

[VegasNath]

Thanks both.
So in a nutshell, if you want to do more with the workbook than just opening it, use Set, but if you only want to open it with no subsequent commands, there is no need to Set?

Hans, I think I will need to re-read your response a few times before I fully understand. Thanks

[HansV]

So in a nutshell, if you want to do more with the workbook than just opening it, use Set, but if you only want to open it with no subsequent commands, there is no need to Set?

That is correct.

Here is why it can be convenient to use Set if you do want to do something with the workbook later on:

Immediately after opening the workbook, it is the active workbook, so you can refer to sheets and ranges without explicitly mentioning the workbook:

Range("A1").Value = 37

will set the value of the active sheet in the active workbook. But if you may activate or open other workbooks in between, before you want to refer to the workbook again, you have to do so explicitly. You could of course use its name, in Workbooks("..."), but if you have set a variable when you open it, you can simply refer to that variable, and if you want to apply the same code to another workbook, you have to change the name in only one place instead of throughout the code.