References make formulas more convenient because they allow you to use data from multiple cells, tables, and workbooks. References can identify both individual cells and groups of cells.
Earlier we examined two cell reference styles: A1 and R1C1. When using references in formulas, you can enter their names from the keyboard or select the required cells with the mouse.
The Range and Selection Objects
Regarding the Range and Selection objects, note that in the MS Excel hierarchy the Range object (range) comes immediately after the Worksheet object. The Range object is one of the key objects in VBA.
The Selection object in VBA appears in two ways: either as the result of the Select method, or when calling the Selection property. The type of object returned depends on the type of object selected. Most often, the Selection object belongs to the Range class, and when working with it you can use the properties and methods of the Range object.
The Range object can be returned as an element of the Range or Cells collections, through the properties Range, Cells, and Offset, or by the methods ActiveCell, Intersect, and Union.
Relative, Absolute, and Mixed Referencing
When addressing cells, relative, absolute, and mixed references can be used.
- Relative referencing is based on the position of the cell containing the formula. When the formula is copied to another cell, the references in each copy change to preserve the same relationships as in the original formula.
Example: A1 or C2 — if the A1 style is used; or R1C1 or R2C3 — if the R1C1 style is used. - Absolute referencing keeps the reference unchanged when formulas are copied (the reference always points to the same cell).
A reference in absolute style contains the row number and the column letter preceded by a dollar sign.
Example: $A10, A$10, and $A$10 fix the column, the row, and both, respectively.
In the R1C1 style, absolute referencing works differently: if the active cell is R2C3, then R[1]C[-1] refers to cell R3C2.
- Mixed referencing is used when you want only the row or only the column to remain unchanged when copying. In this case, the reference contains both absolute and relative parts.
The key while editing formulas cycles through all combinations of relative and absolute references.
References to Other Worksheets or Workbooks
A reference to another sheet in the same workbook is created by including the sheet name in the formula:
Sheet5!A1
Here, the exclamation mark ! is mandatory. If the sheet name contains spaces, the reference must be enclosed in quotation marks.
External references are references to cells located in other workbooks and must include the workbook name enclosed in square brackets:
[Book1]Sheet3!$B$4
Three-dimensional (3D) references consist of a range of sheets (with the first and last specified) and a range of cells:
=SUM(Sheet1:Sheet6!$E$1:$E$6)
In this formula, the values in the range $E$1:$E$6 are summed across all sheets from Sheet1 to Sheet6.
Built-in Functions Supporting 3D References
Three-dimensional references can be used in the following built-in MS Excel functions:
- VAR
- VARP
- MAX
- MIN
- PRODUCT
- AVERAGE
- STDEV
- STDEVP
- SUM
- COUNT
- COUNTA
It is convenient to use names as addresses in formulas (both for individual cells and for ranges of cells).
Assigning Groups of Rows and Columns
If only the names of columns or rows are specified in a range, the Range object defines a range consisting of the specified columns or rows. For example, Range(« A:C ») defines a range consisting of columns A, B, and C, while Range(« 2:2 ») defines the second row.
Another way to work with rows and columns is through the Rows and Columns properties of the worksheet, which return collections of rows and columns. For example, column A is Columns(1), and the second row is Rows(2).
Relationship Between the Range Object and the Cells Property of the Worksheet Object
A cell is a special case of a range, consisting of a single cell. Therefore, it is natural that the Range object can be used both for ranges of cells and for a single cell.
An alternative way to work with a cell is through the Cells property of the Worksheet object. For example, the cell A2 as an object can be described in two equivalent ways: Range(« A2 ») and Cells(1, 2).
In turn, a cell returned by the Cells property, when used as a parameter of the Range object, allows writing a range in an alternative form, which is sometimes more convenient for work. As an example of this form of recording a range, consider the following two instructions, both of which return the same range:
Range("A2:C3")
Range(Cells(1,2), Cells(3,3))
NOTE
A range, like a worksheet, has the Cells property which, when used without parameters, returns the collection of all cells included in the range. If used with parameters, it returns a specific cell from the range. In the following example, the value 2 is entered into cell C3:
Range("B2:D4").Select
Selection.Cells(2, 2).Value = 2
Properties of the Range Object
The Range object combines the flexibility of VBA with the power of the worksheet. The large number of built-in worksheet functions significantly simplifies and makes programming in VBA more intuitive.
The properties of the Range object allow you to control it—from appearance to calculation automation. The main properties of the Range object are the following:
- Address
- AllowEdit
- Areas
- Borders
- Cells
- Characters
- Column
- Columns
- ColumnWidth
- Comment
- Count
- CurrentRegion
- End
- EntireColumn
- EntireRow
- Font
- Formula
- FormulaArray
- FormulaHidden
- FormulaLocal
- FormulaR1C1
- FormulaR1C1Local
- HasFormula
- Height
- Hidden
- HorizontalAlignment
- Hyperlinks
- Interior
- Left
- Locked
- Name
- NumberFormat
- Offset
- Orientation
- Resize
- Row
- RowHeight
- Rows
- ShrinkToFit
- Top
- UseStandardHeight
- UseStandardWidth
- Value
- VerticalAlignment
- Width
- Worksheet
- WrapText
Entering or Reading a Value from a Range
The Value property of the Range object returns or sets the value in the cells of a range. In the first instruction of this example, the variable x is assigned the value from cell C1; in the second, the string « Report » is entered into cell C3; and in the third, the number 1 is entered into each cell of the range A1:B2:
x = Range("C1").Value
Range("C3").Value = "Report"
Range("A1:B2").Value = 1
Entering an Array of Values into a Range
A range can be filled not only cell by cell, but also in one operation, by assigning either a variable of type Variant or directly an array of values (as shown in the second example, Listing 3.16).
Entering an Array of Values into a Range. First Example
Sub DemoInput1()
Dim s As Variant
s = Array("1", "2")
Range("Sheet1!A1:B1").Value = s
End Sub
Entering an Array of Values into a Range. Second Example
Sub DemoInput2() Dim t(8, 8) As Integer Dim i As Integer Dim j As Integer For i = 1 To 9 For j = 1 To 9 t(i - 1, j - 1) = i * j Next Next Range(Cells(1, 1), Cells(9, 9)).Value = t End Sub
Searching for Pattern-Matching Values in a Range
Sequential iteration through the cells of a range and comparing the returned Value property with a pattern using the Like operator allows you to implement a search for similar values in a range.
For example, in the following code, all cells of the range A1:A100 are checked in sequence. For those cells containing the substring “MS,” the contents of the cell are replaced with the word “Microsoft,” the cell itself is filled with yellow, while all other cells are filled with white.
Searching for Similar Values in a Range
Dim c As Range For Each c In [A1:A100] If c.Value Like "*MS*" Then c.Value = "Microsoft" c.Interior.Color = RGB(255, 255, 0) Else c.Interior.Color = RGB(255, 255, 255) End If Next
Entering or Reading a Formula into a Cell in A1 Format
The Formula property of the Range object returns or sets the formula in a range in A1 format. For example:
Range("C1").Formula = "=$A$1+$B$1"
Range("C2").Formula = "=SIN(A2)^2"
Entering or Reading a Formula into a Cell in R1C1 Format
The FormulaR1C1 property of the Range object returns the formula in R1C1 format. For example:
Range("B1").FormulaR1C1 = "=2*R3C2"
This is equivalent to the formula =2*$B$3 in A1 format.
Entering or Reading a Local Version Formula in A1 Format
The FormulaLocal property of the Range object returns the localized version of the formula in A1 format. For example:
Range("B2").FormulaLocal = "=SUM(C1:C4)"
Entering or Reading a Local Version Formula in R1C1 Format
The FormulaR1C1Local property of the Range object returns the localized version of the formula in R1C1 format. For example:
Range("B2").FormulaR1C1Local = "=SUM(R1C3:R4C3)"
Entering an Array Formula into a Range
The FormulaArray property of the Range object returns the array formula in A1 format. Unlike a regular worksheet formula, an array formula is entered on the worksheet not by pressing <Enter> but by pressing <Ctrl>+<Shift>+<Enter>.
For example:
Range("E1:E3").FormulaArray = "=A1:A3*3"
Entering a Local Version Array Formula into a Range
When entering an array formula with localized worksheet functions, the formula must be presented in R1C1 format, and instead of the localized version, the base version of the function must be used.
For example:
Range("D1").FormulaArray = "=SUM(R1C1:R1C2*3)"
Entering an Array Formula with Relative Cell References
To enter an array formula with relative cell references, you must use relative addressing in R1C1 format.
For example:
Range("D1").FormulaArray = "=SUM(RC[-3]:RC[-1]*3)"
How to Find Out if a Formula is Hidden on a Protected Worksheet
The FormulaHidden property of the Range object returns True if the formula is hidden on a protected worksheet.
How to Find Out if a Cell Contains a Formula
The HasFormula property of the Range object returns:
- True if all cells in the range contain formulas,
- False if none of the cells contain a formula,
- Null in all other cases.
For example, the following code checks whether cell C1 contains a formula, and if not, it inserts the formula =1 into that cell:
If Not Range("C1").HasFormula Then Range("C1").Formula = "=1"
Determining the Address of a Cell
The Address property of the Range object returns the address of a range.
Address(RowAbsolute, ColumnAbsolute, ReferenceStyle, External, RelativeTo)
- RowAbsolute — optional Boolean parameter. If its value is True or omitted, the reference to the row is returned as absolute.
- ColumnAbsolute — optional Boolean parameter. If its value is True or omitted, the reference to the column is returned as absolute.
- ReferenceStyle — optional parameter. Accepts two values: xlA1 and xlR1C1. If omitted, the reference is returned in A1 format.
- External — optional Boolean parameter. Determines whether the reference is external.
- RelativeTo — optional parameter. If both RowAbsolute and ColumnAbsolute are set to False and ReferenceStyle is xlR1C1, this parameter specifies the starting cell relative to which the addressing is performed.
The following code, which handles the SelectionChange event of the Worksheet object, demonstrates the values returned by the Address property with various parameter settings.
For example, if cell A1 is selected on the worksheet, the following message box will appear:
$A$1 $A1 R1C1 R[-1]C[-1]
The Address Property. Worksheet Module
Private Sub Worksheet_SelectionChange(ByVal Target As Range) MsgBox Target.Address() & vbCr & _ Target.Address(RowAbsolute:=False) & vbCr & _ Target.Address(ReferenceStyle:=xlR1C1) & vbCr & _ Target.Address(ReferenceStyle:=xlR1C1, RowAbsolute:=False, _ ColumnAbsolute:=False, _ RelativeTo:=Worksheets(1).Cells(2, 2)) End Sub
Can a Cell Be Edited on a Worksheet?
The read-only AllowEdit property of the Range object returns True if editing of values in the specified range is allowed, even when the worksheet is protected.
Determining the Number of Areas that Make Up a Range
The Areas property of the Range object returns a collection of Areas that make up the range. The elements of this collection are themselves Range objects.
The main property of this collection is Count, which returns the number of elements in the collection.
For example, the following code , which handles the SelectionChange event of the Worksheet object, displays in the status bar the number of selected areas .
Displaying the Number of Selected Areas in the Status Bar. Worksheet Module
Private Sub Worksheet_SelectionChange(ByVal Target As Range) Application.StatusBar = "Number of areas: " & Target.Areas.Count End Sub