Jump to content

benannte Bereiche aus Vorgaben erstellen

From The Document Foundation Wiki
This page is a translated version of the page Macros/Calc/ba021 and the translation is 52% complete.


Zusammenfassung

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.

Beschreibung

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(Quelle, Grenze)
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 Werteaufzählung
Zähler Beschreibung
TOP Selects the top border row.
BOTTOM Selects the bottom border row.
RIGHT Selects the right border column.
LEFT Selects the left border column.


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


Tipp:
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    'Tabellenblatt, das den benannten Bereich enthält
  Dim oAddress  'Bereichsadresse
  Dim oRanges   'Die Eigenschaft NamedRanges
  Dim oRange    'Zellberich mit einer Zelle

  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

Dieses Calc Tabellenblatt enthält den obigen LibreOffice Basic Code.