VBA Set Statement

VBA Set is a statement that assigns any value key that says an object or a reference to a variable. We use this function to define the parameter for a certain variable. For example, if we write Set M = A, the M reference has the same values and attributes as A.

In VBA, an object is a core of Excel because, without objects, we cannot do anything. Objects are a workbook, worksheet, and range. When we declare a variable, we need to assign a data type. We can also assign objects as data types. To assign a value to declared object variables, we need to use the word "SET." The word "Set" refers to a new object in VBA. For example, the particular range of the particular worksheet.

#1 - Set Statement with Range Object Variables

For example, assume you want to use the range A1 to D5 quite often. Then, instead of writing the code as Range("A1:D5") every time, we can declare the variable as range and set the range reference as Range("A1:D5").

Step 1: Declare the variable as a Range object.

Code:

Sub Set_Example()

Dim MyRange As Range

End Sub

Step 2: When we assign the data type as a range, use the word "Set."

Code:

Sub Set_Example()

Dim MyRange As Range

Set MyRange =

End Sub

Step 3: Now, mention the range.

Code:

Sub Set_Example()

  Dim MyRange As Range

  Set MyRange = Range("A1:D5")

End Sub

Step 4: The variable "MyRange" equals the range A1 to D5. Using this variable, we can access this range's properties and methods.

We can copy, add a comment in excel, and do many other things.

For example, for purpose, we have created some numbers here.

Now using the variable, we will change the font size to 12.

Code:

Sub Set_Example()

  Dim MyRange As Range

  Set MyRange = Range("A1:D5")

  MyRange.Font.Size = 12

End Sub

It will change the font size of the assigned range.

Like this, we can do many things with a particular range by using the word "Set."

#2 - Set Statement with Worksheet Object Variables

We have seen how "set" works with a range object in VBA. It works the same as the worksheet object as well..

Let's say you have 5 worksheets in your workbook. However, you want to keep going back to the one particular worksheet. So, you can set that worksheet name to the defined object variable.

For example, look at the below code.

Code:

Sub Set_Worksheet_Example()

    Dim Ws As Worksheet

    Set Ws = Worksheets("Summary Sheet")

End Sub

The above code defines the variable "Ws" as an object variable. In the next line, by using the word "Set," we set the variable to the worksheet named "Summary Sheet."

By using this variable, we can do all the associated things. Take a look at the below two sets of code.

#1 - Without “Set” Word

Code:

Sub Set_Worksheet_Example1()

    'To select the sheet
    Worksheets("Summary Sheet").Select

    'To Activate the sheet
    Worksheets("Summary Sheet").Activate

    'To hide the sheet
    Worksheets("Summary Sheet").Visible = xlVeryHidden

    'To unhide the sheet
    Worksheets("Summary Sheet").Visible = xlVisible

End Sub

Every time we have used the worksheets object to refer to the sheet "Summary Sheet," This makes the code lengthy and requires a lot of time to type.

As part of the huge code, it is frustrating to type the worksheet name like this every time you need to reference it.

Now, look at the advantage of using "Set" in code.

#2 - With “Set” Word

Code:

Sub Set_Worksheet_Example()

    Dim Ws As Worksheet

    Set Ws = Worksheets("Summary Sheet")

   'To select the sheet
    Ws.Select

   'To Activate the sheet
    Ws.Activate

   'To hide the sheet
    Ws.Visible = xlVeryHidden

   'To unhide the sheet
    Ws.Visible = xlVisible

End Sub

When we set the worksheet name, we can see the variable name while entering the code as part of the list.

#3 - Set Statement with Workbook Object Variables

The real advantage of the word "Set" in VBA arises when we need to reference different workbooks.

When we work with different workbooks, it is hard to type in the full name of the workbook, along with its file extension.

Assume you have two different workbooks named "Sales Summary File 2018.xlsx" and "Sales Summary File 2019.xlsx" we can set the two workbooks like the below code.

Code:

Sub Set_Workbook_Example1()

Dim Wb1 As Workbook
Dim Wb2 As Workbook

Set Wb1 = Workbooks("Sales Summary File 2018.xlsx")
Set Wb2 = Workbooks("Sales Summary File 2019.xlsx")

End Sub

Now, variable Wb1 is equal to the workbook named "Sales Summary File 2018.xlsx," and variable Wb2 is equal to the workbook named "Sales Summary File 2019.xlsx".

We can access all the properties and methods associated with the workbook using this variable.

We can shorten the code like the one below.

Without Using Set Keyword to activate the workbook:

Workbooks("Sales Summary File 2018.xlsx").Activate

Using the Set Keyword to activate the workbook:

Wb1.Activate

It makes the writing of the code much simpler. However, once we set the workbook name, there is a worry of typo errors in the workbook names.