Macros/Calc/ba029/es

TDF LibreOffice en español Document Liberation Project Blogs comunitarios Weblate Nextcloud Redmine Preguntas y respuestas Donar

• Inicio
• Desarrollo
• Diseño
• Control de calidad
• Eventos
• Documentación
• Sitio web
• Regionalización
• Accesibilidad
• Mercadotecnia
• Diversity
• Wiki Help

• Macros
• Macros Generales
• Macros para Writer
• Macros para Calc
• Macros para Impress
• Macros para Draw
• Macros para Base
• Macros para Math
• Basic/Python

Summary

This page provides a LibreOffice Basic macro procedure that creates an advanced filter. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

Applying an advanced filter with a macro works similarly to setting up a standard filter. The key difference is that the cell range containing the filter criteria is used to create the filter descriptor. The macro procedure below demonstrates how this is done using the createFilterDescriptorByObject method. It uses a class grade sheet in cells $Sheet1.A1:H11 as the data range and cells $Sheet2.A1:H3 as the criteria range. The linked spreadsheet contains such a data range and criteria range.

Code

Sub AdvancedRangeFilter()
  Dim oSheet     '<span lang="en" dir="ltr" class="mw-content-ltr">A sheet from the Calc document.</span>
  Dim oRanges    '<span lang="en" dir="ltr" class="mw-content-ltr">The NamedRanges property.</span>
  Dim oCritRange '<span lang="en" dir="ltr" class="mw-content-ltr">Range that contains the filter criteria.</span>
  Dim oDataRange '<span lang="en" dir="ltr" class="mw-content-ltr">Range that contains the data to filter.</span>
  Dim oFiltDesc  '<span lang="en" dir="ltr" class="mw-content-ltr">Filter descriptor.</span>

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Range that contains the filter criteria</span>
  oSheet = ThisComponent.getSheets().getByIndex(1)
  oCritRange = oSheet.getCellRangeByName("A1:H3")

  REM <span lang="en" dir="ltr" class="mw-content-ltr">You can also obtain the range containing the</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">filter criteria from a named range.</span>
  REM oRanges = ThisComponent.NamedRanges
  REM oRange = oRanges.getByName("AverageLess80")
  REM oCritRange = oRange.getReferredCells()

  REM <span lang="en" dir="ltr" class="mw-content-ltr">The data that you want to filter</span>
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oDataRange = oSheet.getCellRangeByName("A1:H11")

  oFiltDesc = oCritRange.createFilterDescriptorByObject(oDataRange)
  oDataRange.filter(oFiltDesc)
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.