From The Document Foundation Wiki
< Macros‎ | Calc
Jump to navigation Jump to search

Template:TopMenu/en Template:Menu/en Template:Menu.Macros/en

Other languages:
English • ‎español • ‎italiano


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.


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.


Sub SimpleRangeFilter()
  Dim oSheet          ' Sheet to filter.
  Dim oRange          ' Range to be filtered.
  Dim oFilterDesc     ' Filter descriptor.
  Dim oFields(1) As New
  oSheet = ThisComponent.getSheets().getByIndex(0)
  oRange = oSheet.getCellRangeByName("A1:H11")
  REM If argument is True, creates an 
  REM empty filter descriptor.
  oFilterDesc = oRange.createFilterDescriptor(True)

  REM Setup a field to view cells with content that 
  REM start with the letter B.
  With oFields(0)   
    .Field = 0              ' Filter column A (Student names).
    .IsNumeric = False      ' Use a string, not a number.
    .StringValue = "b.*"    ' Every name starting with a B.
    .Operator =
  End With
  REM Set up a field that requires at least one of the conditions.
  REM This new condition requires a value less than or 
  REM equal to 90.
  With oFields(1)
    .Connection =
    .Field = 6              ' Filter column G (Test #1 grades).
    .IsNumeric = True       ' Use a number
    .NumericValue = 90      ' Scores less than 90
    .Operator =
  End With

  oFilterDesc.ContainsHeader = True
  oFilterDesc.UseRegularExpressions = True
End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.