Macros/Calc/ba021

From The Document Foundation Wiki
< Macros‎ | Calc
Jump to navigation Jump to search

Template:TopMenu/en Template:Menu/en Template:Menu.Macros/en

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

 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.