Macros/Calc/ba028/es

    From The Document Foundation Wiki
    < Macros‎ | Calc
    This page is a translated version of the page Macros/Calc/ba028 and the translation is 4% complete.


    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.