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.
1Sub AddManyNamedRanges() 2 Dim oSheet 'Sheet that contains the named range. 3 Dim oAddress 'Range address. 4 Dim oRanges 'The NamedRanges property. 5 Dim oRange 'Single cell range. 6 7 oRanges = ThisComponent.NamedRanges 8 oSheet = ThisComponent.getSheets().getByIndex(0) 9 10 oRange = oSheet.getCellRangeByName("A1:C20") 11 oAddress = oRange.getRangeAddress() 12 oRanges.addNewFromTitles(oAddress, com.sun.star.sheet.Border.TOP) 13End Sub
This Calc spreadsheet contains the above LibreOffice Basic code.