VBA Set Statement

What is Excel VBA Set Statement?

VBA Set statement assigns an object reference to a variable. Referencing objects like Range, Cells, Worksheet, Workbooks, Charts etc in the VBA code might be difficult due to the long lines of code we may need to write. Therefore, using VBA Set statements solves such complexities.

For example, look at the following code. Once we declare a variable, “Set” assigns an object reference to the defined variable.

Here, the variable “Rng” refers to the range of cells from A1 to A10. So, now we can deploy the variable “Rng” to refer to this range.

Explanation

Let’s look at the same code above to understand how things work with the Set keyword in VBA.

Part #1: Here, we define a variable “Rng” using the DIM statement and assign it the data type “Range.” So, the defined variable can contain only a range of cells.

Part #2: Range is an Object data type. Whenever we assign the Object data type to a variable, we should assign the range of cells to the defined variable. Hence, we use the “SET” keyword to set the range for the defined variable.

Excel VBA – All in One Courses Bundle (35+ Hours of Video Tutorials)

If you want to learn Excel and VBA professionally, then ​Excel VBA All in One Courses Bundle​ (35+ hours) is the perfect solution. Whether you’re a beginner or an experienced user, this bundle covers it all – from Basic Excel to Advanced Excel, Macros, Power Query, and VBA.

Without Using a Variable

Assume we must insert a value “Excel VBA” to cells A1 to A10; Here, we use the following code.

We have used the Range object to reference the cells A1 to A10, then used the value property of the Range object to insert the text “Excel VBA” in the given range of cells.

Thus, “Excel VBA” will be inserted in cells A1 to A10.

Using a Variable

Now, assign the range of cells A1 to A10 to the variable “Rng.” Now, use the variable name “Rng” instead of referencing the cells again using the RANGE object.

Type the variable name “Rng” and enter a dot to see the properties and methods associated with the RANGE object.

Because we have already assigned the cell references using the RANGE object to the defined variable, we can use the variable name “Rng.”

Choose the “Value” property from the IntelliSense list and set the value to “Excel VBA”.

This code is much better than the previous one, where we referenced cells using the RANGE object.

How to Use Excel VBA Set Statement?

The VBA Set statement is one of the advanced concepts in VBA, and mastering this will take a long way in automating tasks in Excel using VBA. This example section will show actual examples of using the SET statement.

Example #1 – VBA Set Statement with Worksheet Object Variables

Not many of us realize that worksheets are objects in Excel. We call it a worksheet because we can move its position from one place to another.

We can reference the worksheet by its name using the Worksheets object. But, first, we must provide the worksheet name we want to reference in double-quotes.

For example, look at the following worksheets we have in an Excel file.

We have three worksheets named “Intro,” “Basic,” and “Example.”

#1 – Without the Set Keyword

If we want to select the worksheet “Basic,” we first must open the Worksheets object.

The Worksheets object’s argument shows the index, i.e., we can provide the worksheet’s name in double quotes or its index number.

Step 1: We refer to the worksheet “Basic” and should provide its name within double quotes.

Step 2: After the worksheet name, enter a dot followed by the Select method to select the given worksheet.

It will select the worksheet “Basic” once we execute the code.

Worksheet “Basic” has been selected.

However, when we need to reference the same worksheet repeatedly, we need to write lengthy code and work with many other properties and methods of the Worksheets object. Also, we may make typing mistakes when we try to enter the worksheet name every time manually.

To eliminate this, we can use a variable of Worksheet data type and set the worksheet’s reference to the defined variable. Thus, we can access the worksheet easily with the help of the defined variable.

#2 – With Set Word

Define a Variable and Set the Worksheet Reference: We can use variables to overcome writing lengthy code and avoid mistakes while typing it.

Step 1: Define a variable by using the DIM statement.

Step 2: Once the variable is defined, we need to assign a particular data type to it. In this case, we will assign the Worksheet object to the defined variable by making its data type Worksheet.

Step 3: As we type Worksheet, the IntelliSense list shows all matching results. Choose the option “Worksheet” (do not choose Worksheets).

Now, the variable “Ws” can hold only a Worksheet object as its value.

We must assign the worksheet reference that this variable will hold. To assign a worksheet reference to the variable, we use the “Set” keyword.

Step 4: Enter the SET keyword and the variable “Ws.”

Step 5: To assign the worksheet, enter an equal sign and provide the desired worksheet name using the Worksheets object.

Step 6: Now, the variable “Ws” holds the reference of the worksheet “Basic.” Instead of referencing the worksheet by its name, Worksheets(“Basic”), we can use the variable name “Ws.”

Enter the variable name Ws and enter a dot to see the IntelliSense list shows all the properties and methods associated with the Worksheet object.

• Choose the desired property or method, and it just works fine.
• If you want to use the same worksheet reference in the same macro or sub procedure, we can use the variable name “Ws” instead of the full worksheet name.

Example #2 – Set Statement with Range Object Variables

The Range is also one of the objects in Excel. Working efficiently with the Range object makes automating tasks using the VBA code easier.

To work seamlessly, we use the Range object data type with variables.

In this VBA Set statement example, look at the following data.

We have data from cells A1 to B7.

Step 1: Assume we need to change the font size of this range to 10. Then we can do this by using the Range object as follows.

It will change the font size to 10.

Step 2: Similarly, assume we need to change the font name to “Segoe UI,” we must reference the same range of cells using the Range object.

Thus, whenever we must make any change in the range, we must specify the cell range using the Range object.

We can use the variable name of the data type Range object to eliminate this lengthy code.

Step 3: Define a variable and assign it the data type Range.

Step 4: The variable “Rng” can reference a range of cells. To assign it a specific range of cells, use the SET keyword.

Step 5: Here, we assign the reference A1 to B7 to the variable. Use the variable name and set its properties as before.

Compared to the earlier method of referencing the cells each time, using the variable name optimizes the code.

Step 6: Execute the code, and it will change the font size and name of the given range cell values.

Set the Range Dynamically

One issue when we manually set the variable to the range of cells is that it won’t be dynamic. For example, consider the following data.

Compared to the previous table, this table has expanded (colored area). In the newly added area, we must make the formatting changes.

However, the existing code needs to be fixed. So let’s look at the code again.

Sub Range_Object()
Dim Rng As Range
Set Rng = Range(“A1:B7”)
Rng.Font.Size = 10
Rng.Font.Name = “Segoe UI”
End Sub

In this code, while we set the range reference, we have used the cell range as A1 to B7, which is static.

This code will only apply formatting changes to the given cell range, i.e., A1 to B7. To work efficiently, we must set the Range object cell reference dynamically.

The following code dynamically sets the range.

Sub Range_Object()
Dim Rng As Range
Dim LC As Long
Dim LR As Long
LC = Cells(1, Columns.Count).End(xlToLeft).Column
LR = Cells(Rows.Count, 1).End(xlUp).Row
Set Rng = Cells(1, 1).Resize(LR, LC)
Rng.Font.Size = 10
Rng.Font.Name = “Segoe UI”
End Sub

Instead of manually assigning the range of cells, we have defined two variables to find the last used row and last used column.

• LC = Cells(1, Columns.Count).End(xlToLeft).Column

This code finds the last used column.

• LR = Cells(Rows.Count, 1).End(xlUp).Row

This code finds the last used row.

Next, we set the range using the CELLS property.

Set Rng = Cells(1, 1).Resize(LR, LC)

It will set the range using the last used row and last used column. So, this dynamically considers the newly added rows and columns.

Once we run the code, it will format all the newly added rows and columns.

These colored ranges of cells are also formatted without altering the code, thanks to the dynamic range setting.

Example #3 – VBA Set Statement with Workbook Object Variable

A workbook is an object in Excel. When we automate tasks, we may have to get the data from different workbooks; hence, it is essential to set the workbook reference to a variable and assign it to a particular workbook to improve efficiency.

For example, look at the following workbooks.

We have two workbooks opened in our system:

1. Sales Report Feb 2023.xlsx
2. VBA Set Statement.xlsm

Currently, the active workbook is VBA Set Statement.xlsm.

Assume we need to move to the other workbook, i.e., Sales Report Feb 2023.xlsx

Step 1: First, reference the workbook using the “Workbooks” object.

Step 2: In the “Workbook” object, enter the workbook name in double quotes.

We have provided the workbook name, followed by the extension of the workbook. Providing the extension is very important because the same workbook may also be saved with a different extension.

Step 3: Enter the dot and choose the activate method after providing the workbook name.

Once we run the code, it will activate the workbook Sales Report Feb 2023.xlsx.

However, writing this code when we need to switch between workbooks or reference a workbook multiple times is a tedious task.

To eliminate this, we define a variable of data type Workbook.

Step 4: Define a variable and assign the data type “Workbook”.

Step 5: Now, use the “Set” keyword and assign the desired workbook name to the variable along with its extension.

Step 6: The variable “Wb” refers to the workbook Sales Report Feb 2023.xlsx. We can use the variable name instead of referencing the workbook with long text.

Thus, we can activate the workbook using the variable.

Important Things to Note

• When we assign the data type Worksheet, we must be careful to assign the Worksheet object without the letter “s,” i.e., “Worksheet,” not “Worksheets.”
• Without trying to access VBA Set statement, we must reference the object each time with its name.
• Once the range is set manually, it will refer to the same range even when it is increased or decreased. Hence, we must set the range dynamically.
• When we set a reference for a workbook, we must enter the workbook name accurately along with its extension.
• When we define a variable, we should define the kind of object it can hold, and it can only be assigned that object type.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand the VBA Set Statement, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA Set Statement. Here we explain how to use the VBA Set Statement in Excel with examples and downloadable excel template. You may learn more from the following articles –

• VBA Select Cell
• VBA Format Number
• VBA UBOUND