Create Named Ranges from Labels

    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 creates named ranges from labels. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

    Description

    In addition to using the Create Names dialog (accessed by selecting Sheet ▸ Named Ranges and Expressions ▸ Create on the Menu bar), named ranges can be generated from labels using the macro method addNewFromTitles:

    addNewFromTitles(Source, Border)
    Creates named ranges from column or row headers. This method has two arguments:
    • Source – The cell range address of the named range to be created.
    • Border – Is an enumeration value that specifies the location of the header labels. This enumeration has one of four possible values, listed in the following table:
    com.sun.star.sheet.Border enumeration values
    Enumerator Description
    TOP Selects the top border row.
    BOTTOM Selects the bottom border row.
    RIGHT Selects the right border column.
    LEFT Selects the left border column.


    Tip.svg

    Tip:
    To generate names from multiple borders, you must call addNewFromTitles for each header row or column that you wish to use.


    Tip.svg

    Tip:
    Avoid giving multiple rows or columns the same label, as the ranges generated from them will likewise share the same name, and can end up being overwritten by Calc.


    The macro below creates three named ranges using headers from the top row of the cell range A1:C20. The resulting ranges will be listed in the Manage Names dialog, which can be accessed by selecting Sheet ▸ Named Ranges and Expressions ▸ Manage on the Menu bar or by pressing Ctrl+F3.

    Code

    Sub AddManyNamedRanges()
      Dim oSheet    'Sheet that contains the named range.
      Dim oAddress  'Range address.
      Dim oRanges   'The NamedRanges property.
      Dim oRange    'Single cell range.
    
      oRanges = ThisComponent.NamedRanges
      oSheet = ThisComponent.getSheets().getByIndex(0)
    
      oRange = oSheet.getCellRangeByName("A1:C20")
      oAddress = oRange.getRangeAddress()
      oRanges.addNewFromTitles(oAddress, com.sun.star.sheet.Border.TOP)
    End Sub

    This Calc spreadsheet contains the above LibreOffice Basic code.