Using Relative References with Named Expressions
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).
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. 7 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.