Using Relative References with Named Expressions

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

Other languages:

Summary

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.

Description

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).

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

Code

Sub AddNamedFunction()
  Dim oSheet          'Sheet that contains the range oRange.
  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

This Calc spreadsheet contains the above LibreOffice Basic code.