Macros/Calc/ba028/es

This page is a translated version of the page Macros/Calc/ba028 and the translation is 4% complete.

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

Other languages:

Summary

This page provides a LibreOffice Basic macro procedure that filters two columns and uses regular expressions. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

The macro procedure below demonstrates a filter that filters two columns and uses regular expressions. Note that the filter method is called on a range rather than its sheet in this example.

As an example, the macro below filters a class grade sheet in cells $Sheet1.A1:H11 by Test #1 scores <= 90% and student names that begin with "B". The linked spreadsheet contains such a grade sheet.

Code

Sub SimpleRangeFilter()
  Dim oSheet          ' <span lang="en" dir="ltr" class="mw-content-ltr">Sheet to filter.</span>
  Dim oRange          ' <span lang="en" dir="ltr" class="mw-content-ltr">Range to be filtered.</span>
  Dim oFilterDesc     ' <span lang="en" dir="ltr" class="mw-content-ltr">Filter descriptor.</span>
  Dim oFields(1) As New com.sun.star.sheet.TableFilterField
  
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("A1:H11")
  
  REM <span lang="en" dir="ltr" class="mw-content-ltr">If argument is True, creates an</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">empty filter descriptor.</span>
  oFilterDesc = oRange.createFilterDescriptor(True)

  REM <span lang="en" dir="ltr" class="mw-content-ltr">Setup a field to view cells with content that</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">start with the letter B.</span>
  With oFields(0)   
    .Field = 0              ' <span lang="en" dir="ltr" class="mw-content-ltr">Filter column A (Student names).</span>
    .IsNumeric = False      ' <span lang="en" dir="ltr" class="mw-content-ltr">Use a string, not a number.</span>
    .StringValue = "b.*"    ' <span lang="en" dir="ltr" class="mw-content-ltr">Every name starting with a B.</span>
    .Operator = com.sun.star.sheet.FilterOperator.EQUAL
  End With
  REM <span lang="en" dir="ltr" class="mw-content-ltr">Set up a field that requires at least one of the conditions.</span>
  REM <span lang="en" dir="ltr" class="mw-content-ltr">This new condition requires a value less than or</span> 
  REM <span lang="en" dir="ltr" class="mw-content-ltr">equal to 90.</span>
  With oFields(1)
    .Connection = com.sun.star.sheet.FilterConnection.OR
    .Field = 6              ' <span lang="en" dir="ltr" class="mw-content-ltr">Filter column G (Test #1 grades).</span>
    .IsNumeric = True       ' <span lang="en" dir="ltr" class="mw-content-ltr">Use a number</span>
    .NumericValue = 90      ' <span lang="en" dir="ltr" class="mw-content-ltr">Scores less than 90</span>
    .Operator = com.sun.star.sheet.FilterOperator.LESS_EQUAL
  End With

  oFilterDesc.setFilterFields(oFields())
  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
  oRange.filter(oFilterDesc)
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.