When Microsoft Excel starts, it creates 16,384 columns and 1,048,576 rows. As a result, a Microsoft Excel spreadsheet has 16,384 * 1,048,576 = 17,179,869,184 cells available.
To access a cell, you can click it. This becomes the active cell. In VBA, the active cell is represented by an object named ActiveCell.
To identify a cell, you can use the Range object. In the parentheses of the Range object, pass a string that contains the name of the cell. Here is an example that refers to the cell located as D6: Sub Exercise()
Workbooks.Item(1).Worksheets.Item("Sheet1").Range("D6")
End Sub
To get a reference to a cell, declare a variable of type Range. To initialize the variable, identify the cell and assign it to the variable using the Set operator. Here is an example: Sub Exercise()
Dim Cell As Range
Set Cell = Workbooks.Item(1).Worksheets.Item("Sheet1").Range("D6")
End Sub
Cells are referred to as adjacent when they touch each other. To refer to a group of adjacent cells, in the parentheses of the Range object, pass a string that is made of the address of the cell that will be on one corner, followed by a colon, followed by the address of the cell that will be on the other corner. Here is an example: Sub Exercise()
Range("B2:H6")
End Sub
You can use this same technique to refer to one cell. To do this, use the same cell address on both sides of the colon. Here is an example: Sub Exercise()
Range("D4:D4")
End Sub
Instead of referring to one group of adjacent cells, you can refer to more than one group of non-adjacent cells. To do this, pass a string to the Range object. In the string, create each range as you want but separate them with commas. Here is an example: Sub Exercise()
Range("D2:B5, F8:I14")
End Sub
Before doing anything on a cell or a group of cells, you must first select it. To support cell selection, the Range object is equipped with a method named Select. Therefore, to programmatically select a cell, after referencing it, call the Select method. Here is an example: Sub Exercise()
Range("D6").Select
End Sub
When you have selected a cell, it is stored in an object named Selection. You can use this object to take an action on the cell that is currently selected.
To programmatically select a group of adjacent cells, refer to the group using the techniques we saw earlier, then call the Select method. To programmatically select all cells of a column, access the Columns collection and pass the column name as a string, then call the Select method. Here is an example we saw in Lesson 9: Sub Exercise()
Rem This selects all cells from the fourth column
Columns(4).Select
End Sub
To perform this operation using the name of a column, pass that name as argument. Here is an example that selects all cells from Column ADH: Sub Exercise()
Rem This selects all cells from the column labeled ADH
Columns("ADH").Select
End Sub
You can also perform this operation using the Range object. To do this, use the Range collection. In the parentheses of the colection, enter the name of the column, followed by a colon, followed by the same column name. Here is an example: Sub Exercise()
Rem This selects all cells from Column G
Range("G:G").Select
End Sub
To programmatically select all cells that belong to a group of adjacent columns, in the parentheses of the Columns collection, enter the name of the first column on one end, followed by a colon ":", followed the name of the column that will be at the other end. Here is an example: Sub Exercise()
Rem This selects all cells in the range of columns from Column D to Column G
Columns("D:G").Select
End Sub
To select the cells that belong to a group of non-adjacent columns, use the technique we saw earlier to refer to non-adjacent columns, then call the Select method. Here is an example: Sub Exercise()
Rem This selects the cells from columns B, D, and H
Range("H:H, D:D, B:B").Select
End Sub
To programmatically select all cells that belong to a row, access a row from the Rows collection, then call the Select method. Here is an example that all cells from Row 6: Sub Exercise()
Rows(6).Select
End Sub
You can also use the Range object. After accessing the row, call the Select method. Here is an example that selects all cells from Row 4: Sub Exercise()
Range("4:4").Select
End Sub
To select all cells that belong to a range of rows, refer to the range and call the Select method. Here is an example that selects all cells that belong to the rows from 2 to 6: Sub Exercise()
Rows("2:6").Select
End Sub
To select all cells that belong to non-adjacent rows, refer to the rows and call the Select method. Here is an example that selects all cells belonging to Rows 3, 5, and 8: Sub Exercise()
Range("3:3, 5:5, 8:8").Select
End Sub
To programmatically select cells in the same region, enter their range as a string to the Range object, then call the Select method. Here is an example: Sub Exercise()
Range("B2:H6").Select
End Sub
Remember that you can use the same technique to refer to one cell, thus to select a cell. Here is an example: Sub Exercise()
Range("D4:D4").Select
End Sub
To select more than one group of non-adjacent cells, refer to the combination as we saw earlier and call the Select method. Here is an example: Sub Exercise()
Range("D2:B5, F8:I14").Select
End Sub
To select all cells of a spreadsheet, you can call the Select method on the Rows collection. Here is an example: Sub Exercise()
Rows.Select
End Sub
Instead of the Rows collection, you can use the Columns collection instead and you would get the same result. When you have selected a group of cells, the group is stored in an object named Selection. You can use this object to take a common action on all of the cells that are currently selected.
We already saw that, to refer to a cell using its name, you can pass that name as a string to the Range object. After creating a name for a group of cells, to refer to those cells using the name, call the Range object and pass the name as a string. |
|
|||||||||||||||||||||||
|
|
||||||||||||||||||||||||
Practical Learning: Introducing
Data Entry
