Macros/Calc/ba021

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

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:
To generate names from multiple borders, you must call addNewFromTitles for each header row or column that you wish to use.

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.