Calc Guide

The third argument, a cell address, acts as the base address for cells
referenced in a relative way. If the cell range is not specified as an
absolute address, the referenced range will be different based on
where in the spreadsheet the range is used. The relative behavior is
illustrated in Listing 11, which also illustrates another usage of a
named range—defining an equation. The macro in Listing 11 creates
the named range AddLeft, which refers to the equation A3+B3 with C3
as the reference cell. The cells A3 and B3 are the two cells directly to
the left of C3, so, the equation =AddLeft() calculates the sum of the
two cells directly to the left of the cell that contains the equation.
Changing the reference cell to C4, which is below A3 and B3, causes
the AddLeft equation to calculate the sum of the two cells that are to
the left on the previous row.
Listing 11. Create the AddLeft named range.
Sub AddNamedFunction()
Dim oSheet 'Sheet that contains the named range.
Dim oCellAddress 'Address for relative references.
Dim oRanges 'The NamedRanges property.
Dim oRange 'Single cell range.
Dim sName As String 'Name of the equation to create.
sName = "AddLeft"
oRanges = ThisComponent.NamedRanges
If NOT oRanges.hasByName(sName) Then
oSheet = ThisComponent.getSheets().getByIndex(0)
oRange = oSheet.getCellRangeByName("C3")
oCellAddress = oRange.getCellAddress()
oRanges.addNewByName(sName, "A3+B3", oCellAddress, 0)
End If
End Sub
Tip
Listing 11 illustrates two capabilities that are not widely known.
A named range can define a function. Also, the third argument
acts as the base address for cells referenced in a relative way.
Select the range containing the headers and the data and then use
Insert > Names > Create to open the Create Names dialog (see
Figure 305), which allows you to simultaneously create multiple named
ranges based on the top row, bottom row, right column or left column.
If you choose to create ranges based on the top row, one named range
is created for each column header—the header is not included in the
named range. Although the header is not included in the range, the
text in the header is used to name the range.
362 OpenOffice.org 3.x Calc Guide