Macros/Calc/ba022

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

• Wiki Home
• Development
• Design
• QA
• Events
• Documentation
• Website
• Localization
• Accessibility
• Marketing
• Diversity
• Wiki Help

• Macros
• General Macros
• Macros for Writer
• Macros for Calc
• Macros for Impress
• Macros for Draw
• Macros for Base
• Macros for Math
• Basic/Python

Summary

This page provides a LibreOffice Basic macro procedure that creates a new database range referencing $Sheet1.$A$1:$F$10. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

In a macro, a database range is created, accessed, and deleted using the DatabaseRanges service. This service has many of the same methods as the NamedRanges service, but lacks the addNewFromTitles method. DatabaseRanges also uses a reduced version of the addNewByName method that lacks arguments for a relative base address and range type:

addNewByName(Name, Range)

Adds a new database range to the current document.

As an example of creating a range using this method, the macro below creates a database range named MyName and automatically applies auto filters to each of the range’s columns:

Code

Sub AddNewDatabaseRange()
  Dim oRange 'DatabaseRange object.
  Dim oAddr  'Cell address range for the database range.
  Dim oSheet 'First sheet, which will contain the range.
  Dim oDoc   'Reference ThisComponent with a shorter name.

  oDoc = ThisComponent
  If NOT oDoc.DatabaseRanges.hasByName("MyName") Then
    oSheet = ThisComponent.getSheets().getByIndex(0)
    oRange = oSheet.getCellRangeByName("A1:F10")
    oAddr = oRange.getRangeAddress()
    oDoc.DatabaseRanges.addNewByName("MyName", oAddr)
  End If
  oRange = oDoc.DatabaseRanges.getByName("MyName")
  oRange.AutoFilter = True
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.