Using Relative References with Named Expressions

From The Document Foundation Wiki
< Macros‎ | Calc
Jump to navigation Jump to search
Other languages:
English • ‎italiano


This page provides a LibreOffice Basic macro procedure that illustrates the use of relative references with named expressions. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.


If a named range uses any cell addresses that are not absolute, then these addresses will be referenced relative to the range’s base address, which is defined by the third argument of the addNewByName method, Position. This behavior is illustrated in the code below, where the macro AddNamedFunction creates the named expression AddLeft. This expression references the equation A3+B3 with C3 as its base address. Because relative references are being used AddLeft sums the values of the two cells directly to the left of any cell containing the formula "=AddLeft". For example, if AddLeft is referenced in cell E5, then it will sum the values in C5 and D5 (see linked spreadsheet).


This code illustrates another little-known attribute of Calc: named ranges are a subset of named expressions, which can include formulas as well.


 1Sub AddNamedFunction()
 2  Dim oSheet          'Sheet that contains the range oRange.
 3  Dim oCellAddress    'Address for relative references.
 4  Dim oRanges         'The NamedRanges property.
 5  Dim oRange          'Single cell range.
 6  Dim sName As String 'Name of the equation to create.
 8  sName = "AddLeft"
 9  oRanges = ThisComponent.NamedRanges
10  If NOT oRanges.hasByName(sName) Then
11    oSheet = ThisComponent.getSheets().getByIndex(0)
12    oRange = oSheet.getCellRangeByName("C3")
13    oCellAddress = oRange.getCellAddress()
14    oRanges.addNewByName(sName, "A3+B3", oCellAddress, 0)
15  End If
16End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.