Using Relative References with Named Expressions

    From The Document Foundation Wiki
    < Macros‎ | Calc
    This page contains changes which are not marked for translation.


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

    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.