Create Named Ranges from Labels
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.
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:
|TOP||Selects the top border row.|
|BOTTOM||Selects the bottom border row.|
|RIGHT||Selects the right border column.|
|LEFT||Selects the left border column.|
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.
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.