What Are VBA Cells?
VBA cells are similar to cells in Excel and help reference all the cells or a specific cell in a worksheet. To automate tasks in Excel, we need to be aware of how to work with the cell property and range object in VBA.
By using the CELLS reference property, we can do a lot of activities like select, activate, copy, cut, format, etc., So, to copy a cell, say cell A1, we can use the CELLS property and provide the desired cell address to copy the cell.
Table of Contents
- VBA Cells are used to refer to a single cell or multiple cells in a worksheet.
- We can perform several activities like select, activate, assign values, etc., with the cells.
- It is used to loop through cells based on the position of the loop.
- We can insert a value and retrieve the value from any cell of the given worksheet using VBA cells.
- If the worksheet’s name is not mentioned, then CELLS will always retrieve the value from the active sheet.
- We can assign the value of the cell to a variable and show the same value in the message box as well.
What Is VBA Range And VBA Cell?
In simple terms, VBA Range is an object, and VBA Cells is a property. Both Range and Cells are used to reference the cell, but the way of doing it is different.
For example, if we want to reference cell A5, then, we can use the range as well as cells.
Range Method: Range(“A5”)
Cells Method: Cells(5,1)
The range method looks straightforward because the cell address is passed in double-quotes. However, with the CELLS property, this isn’t the case.
In the CELLS property, we must use the row index number and column index number to reference any cell. For example, look at the following image.
Similarly, if we want to insert a value Hello to cell A5, then we can do this in both range and cells.
Range(“A5”).Value = “Hello”
Cells(5,1).Value = “Hello”
The major difference between these two methods of referencing a cell is when we select multiple cells.
For example, if we want to insert a value Hello in cells A1 to A10, then, we can do this by using the Range object as follows.
Range(“A1:A10”).Value = “Hello”
However, the CELLS property doesn’t have the feature of referencing multiple cells at a time; only one cell can be referenced using the CELLS property.
The Formula Of Cells Property In VBA
It is important to understand the syntax of the CELLS property to reference any cells. The syntax is –
RowIndex – The row number of the cell that we need to reference.
ColumnIndex – The column number of the cell that we need to reference.
In Excel, we can reference the cells by using the R1C1 reference style. CELLS property in VBA uses the same logic.
For example, if we want to reference cell D5, then, we must provide the row number of this cell i.e., the 5th row.
After the row number, we must provide the column number of the cell. Since we are referencing cell D5, the column number of D is 4.
Similarly, if we want to reference cell A3, then, we can provide the cell address like this CELLS(3,1), for cell B4 it will be CELLS(4,2), and for cell E10 it will be CELLS(10,5).
How To Use Cells Property In VBA?
Let’s discuss some of the examples of using the CELLS property in VBA coding before we move into some of the advanced examples.
Example #1 – Select A Cell
For example, assume we want to select cell A2 using the CELLS property, then follow the steps listed below.
Step 1 – Click on the Visual Basic Editor window under the Developer tab.
Note: We can also use the Excel shortcut key ALT + F11.
Step 2 – In the Visual Basic Editor window, insert a new module under the Insert tab.
Step 3 – In the new module, write the word and give a name to the macro as Cells_Example and hit the Enter key.
Step 4 – Enter the CELLS property name inside the subprocedure and open the parenthesis.
Step 5 – For the CELLS property, we need to give the row number of the desired cell. Since we are referring to cell A2, the row number is 2.
Step 6 – After the row number, we need to give the column number of cell A2. Column A is the first column, so the index number is 1.
Step 7 – After the cell address is given, we can access the properties and methods of this cell by entering the dot.
The problem with the CELLS property is when we enter the dot after the cell reference is given, we will not get the IntelliSense list to start choosing the desired property or method. Hence, one should be aware of the properties and methods available with the CELLS property.
Step 8 – Since we are selecting the cell enter the word Select.
Step 9 – The above code will select cell A2. Let’s run this code and see what happens.
Example #2 – Insert Value to a Cell
Now lets us look at the way of inserting the value into the cell. For example, if we want to insert the value Excel VBA to cell B3, then first we need to open the CELLS property.
Step 1: Enter the row index number as 3 and the column index number as 2.
Step 2: Since we are inserting the value into this cell, enter the dot (.) and type the VALUE property.
Step 3: After accessing the VALUE property, we need to enter the equal sign and enter the desired value in double quotes.
Step 4: When we run this code, it will insert the value Excel VBA into cell B3.
Example #3 – Get The Value Of A Cell
We have seen how to insert a value into the cell, similarly now we will see how to get the value from the cell. For example, look at the following image.
Assume, we need to get the value of cell B4 to cell C1 i.e., we need to insert the value to cell C1, and the value will be from cell B4.
Step 1: Reference cell C1 by using the CELLS property as follows and enter the VALUE property.
Step 2: Enter the equal sign to assign the value.
The value will be from cell B4, so enter another CELLS property and provide the row and column number of cell B4 as 4 and 2 respectively.
Step 3: Let’s run this code, and we should get the value of cell B4 to cell C1.
Similarly, we can get the value of any cell to the variable. Look at the following code.
Dim CellValue As String
CellValue = Cells(2, 2).Value
The above code will retrieve the value from cell B2 and show it in the message box like the following image.
Example #4 – Get the value of a specified worksheet and cell
Assume we need to retrieve the value of cell B2 from the worksheet “Sales”; then, we can write the code as follows.
Dim k As String k = Worksheets("Sales").Cells(2, 2).Value MsgBox k
By using the WORKSHEETS object, we are referencing the “Sales” worksheet, and in that worksheet, we are getting the value of cell B2.
How To Use CELLS Property With Range Object?
As we have discussed so far, a range object can select multiple cells and the CELLS property can select one cell at a time.
The beauty is we can use the CELLS property inside the RANGE object. For example, look at the following data in Excel.
Step 1: Assume we need to select the range of cells B5 to D7, then, we can use the RANGE object and write the code like the following.
This code will select the cells to range B5:D7.
Step 2: Now, the CELLS property can be used to select the first cell of the given range i.e., B5:D7. The following code will select the first cell of the range B5:B7.
This code will select cell B5.
Step 3: Similarly, if we want to select the 2nd row of the 2nd column, we can write the code like the following.
This will select cell C6.
CELLS Property With Loops
CELLS property is widely used with VBA loops. For example, assume we need to insert a serial number from cell A1 to A10, then we need not have to write 10 different lines of code, rather we can do this in one line of code.
The following code will make use of the CELLS property and insert serial numbers from 1 to 10 in cells A1 to A10.
Dim K As Integer For K = 1 To 10 Cells(K, 1).Value = K Next K
If we observe the above code, for the CELLS property, we have not provided the row number; rather, we have given the loop variable “k”. The row number will be automatically fetched from the variable “k” based on the number of times the loop is running.
When the loop runs for the first time, the value of “k” is 1. So, the CELLS reference will be CELLS(1,1) i.e., Cell A1, when the loop runs for the 2nd time, the value of the variable “k” is 2 and the cell reference will be CELLS(2,1) i.e., cell A2 and so on.
Similarly, we can reference the cells dynamically using loops along with the CELLS property.
Important Things To Note
- CELLS is a property and Range is an object. We can use the property to the object but not the object to the property.
- CELLS property uses R1C1 reference. For example, CELLS(1,2) means B1 cell. We can also reference this by using the column header i.e., B. CELLS(1,”B”)
- When the CELLS is a property used with the RANGE, the first cell will be the cell of the given range.
- Only one cell can be referenced with the CELLS property whereas the RANGE object can be used to reference multiple cells.
Frequently Asked Questions
By using the CELLS property, we can select only one cell. To select multiple cells in VBA, we need to use the Range object.
For example, to select a range of cells A2:B5, then, we can write the code like the following.
Yes, VBA works on hidden cells. We can still select, insert, and retrieve values from hidden cells.
In VBA, CELLS is a property and RANGE is an object. By using the cells property, we can reference only one cell whereas by using the range object we can reference multiple cells.
We can use the CELLS property inside the Range object, but we cannot use the Range object inside the Cells property. For the CELLS property, we need to give index numbers, and for the RANGE object, we need to provide the cell address in double quotes.
We can go to a specific cell by using the cell reference. For example, to go to cell A3, then we can write the code as CELLS(3,1).Select.
Using the range object, we can write the code as Range(“A3”).Select
We can also write the cell’s property address like this CELLS(3, “A”).Select.
This means we can provide the column header instead of the column index number in double-quotes.
This article must be helpful to understand the VBA cells, with its formula and examples. You can download the template here to use it instantly.
This has been a guide to VBA Cells. Here we explain how to use cells property with range object in VBA and downloadable excel template. You may learn more from the following articles –
Leave a Reply