benannte Bereiche aus Vorgaben erstellen

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

• Start
• Entwicklung
• Gestaltung
• QA
• Events
• Dokumentation
• Webseite
• Lokalisierung
• Barrierefreiheit
• Marketing
• Diversität
• Wiki Hilfe

• Makros
• Allgemeine Makros
• Makros für Writer
• Makros für Calc
• Makros für Impress
• Macros für Draw
• Makros für Base
• Makros für Math
• Basic/Phyton

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.