Cell Reference in Excel VBA (8 Examples)

In this article, I’ll show you how you can access a cell reference in VBA in Excel. You’ll learn to access a single cell, as well as multiple cells together.

How to Refer to Cell Reference in Excel VBA: 8 Ways

Here we’ve got a data set with the Book Name, Book Types, and Price of some books of a bookshop called Martin Bookstore.

The data set lies in the range B4:D13 of the worksheet.

Today our objective is to learn to refer the cell references of this data set with VBA.

Here are the 8 best ways to refer to a cell reference with VBA in Excel.

1. Refer to a Cell Reference by Using the Range Object in VBA in Excel

First of all, you can refer to a cell reference by using the Range object of VBA.

You can refer to both a single cell and a range of cells with the Range object.

For example, to access the single-cell B4, use the line of code:

The following code selects cell B4.

It’ll select cell B4 in the active worksheet.

Similarly, you can access a range of cells in this way.

The following code selects the range B4:D13.

It’ll select the range of cells B4:D13.

Note: You can use the Range object directly without declaring it first, like:

Also if you want to access any cell of a worksheet that’s not active, use the name of the worksheet before the Range object.

For example, to access the cell B4 of Sheet2, use:

2. Refer to a Cell Reference by Using the Index Numbers in VBA in Excel

You can also refer to a cell reference by using the Index numbers. But in this method, you can refer only to a single cell.

For example, to access the cell with row number 4, and column number 2 (B4), use:

The following code again selects cell B4 of the active worksheet.

It’ll select cell B4.

Note: To access any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

[Same as method 1].

Read More: How to Keep a Cell Fixed in Excel Formula

3. Refer to a Cell Reference Relative to Another Cell in VBA in Excel

You can also refer to a cell reference relative to another cell in VBA. You have to use the Offset function of VBA for this.

To access the cell 1 row down and 2 column right of the cell B4 (D5), use:

The following code selects cell D5 of the active worksheet.

It’ll select cell D5.

Note: To refer to any cell of a worksheet that’s inactive, use the name of the worksheet before the cell reference.

For example:

[Same as method 1 and 2].

Read More: How to Hard Code in Excel

4. Refer to a Cell Reference by Using the Shortcut Notation in VBA in Excel

There is a shortcut notation available to access any cell reference in VBA. You can refer to both a single cell and a range of cells in this way.

To access cell B4, use:

Or to access the range B4:D13, use:

The following code selects the range B4:D13.

It’ll select the range B4:D13.

Note: To refer to any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

[Same as methods 1, 2, and 3].

Read More: How to Make Different Types of Cell Reference in Excel

5. Refer to a Named Range in VBA in Excel

You can refer to a Named Range with VBA in Excel.

Let’s name the range B4:D13 of the active worksheet as Book_List.

Now we can refer to this Named Range by the line of code:

The following code selects the range Book_List (B4:D13).

It’ll select the range Book_List.

Note: To access any cell of an inactive worksheet, use the name of the worksheet before the cell reference.

For example:

[Same as methods 1, 2, 3, and 4].

Read More: How to Use Cell References in Excel Formula

6. Refer to Multiple Ranges in VBA in Excel

You can also refer to multiple ranges in VBA in Excel.

To access the range B4:D5, B7:D8, and B10:D11, use:

It’ll select the multiple ranges together.

Also, you can use the Union property of VBA to access multiple ranges together.

Or you can access multiple Named Ranges together.

Also, put the worksheet name in front of inactive worksheets.

For example:

[Same as methods 1, 2, 3, 4, and 5]

Read More: Relative and Absolute Cell Address in the Spreadsheet

7. Refer to Rows and Columns in VBA in Excel

You can also refer to one or more rows or columns in VBA in Excel.

To access the 4th row, use:

It’ll select the entire 4th row.

Similarly, Columns(4) will access the entire 4th column.

And to access multiple rows or columns together, use the Union property of VBA.

To access the rows 4, 6, 8, and 10 together, use:

It’ll select the entire rows 4, 6, 8, and 10.

Note: Add the name of the worksheet in front in case it’s an inactive one.

For example:

[Same as method 1, 2, 3, 4, 5, and 6]

Read More: Difference Between Absolute and Relative Reference in Excel

8. Refer to the Whole Worksheet in VBA in Excel

Finally, I’ll show you to refer to the whole worksheet. To access the whole worksheet in VBA, use:

Or to refer to an inactive worksheet (For example, Sheet2), use:

It’ll select the whole worksheet Sheet2.

Read More: Excel VBA: R1C1 Formula with Variable

Things to Remember

• To access one or more cells of the active worksheet, you may mention the worksheet name in front or not, But to access cells of an inactive worksheet, you must mention the worksheet name in front of the cell reference.
• Even you can access cells of an inactive workbook in VBA, In that case, you have to mention both the workbook name and the worksheet name in front of the cell reference.

Download Practice Workbook

Download this practice book to exercise the task while you are reading this article.

Conclusion

Using these methods, you can refer to any cell reference with VBA in Excel. Do you have any questions? Feel free to ask us.

<< Go Back to Cell Reference in Excel | Excel Formulas | Learn Excel