Macros/Calc/ba023

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:

Summary

This page provides a LibreOffice Basic macro procedure that sorts a table based on the values in one column. It is an example drawn from Chapter 13 ("Calc as a Database") of the 7.0 Calc Guide.

Description

In a Calc macro, data within a range is sorted by calling the sort() method on the range object. When a sort operation is called on a range, an array of properties known as a sort descriptor is passed to the sort method. Contained within a descriptor’s properties are sort fields, which are structures that inform Calc how to sort a range according to the data contained within one of its rows or columns.

As an example, the macro below sorts a class grade sheet in cells $Sheet1.A1:H11 according to average grade in descending order. The linked spreadsheet contains such a grade sheet.

Code

 1Sub SortAverageGrade
 2  Dim oSheet
 3  Dim oRange
 4  Dim oSortFields(0) as new com.sun.star.util.SortField
 5  Dim oSortDesc(0) as new com.sun.star.beans.PropertyValue
 6
 7  oSheet = ThisComponent.Sheets(0)
 8  REM Set the range on which to sort
 9  oRange = oSheet.getCellRangeByName("A1:H11")
10
11  REM Sort by the Average grade field in the range in descending order
12  oSortFields(0).Field = 7
13  oSortFields(0).SortAscending = FALSE
14
15  REM Set the sort fields to use
16  oSortDesc(0).Name = "SortFields"
17  oSortDesc(0).Value = oSortFields()
18  
19  REM Now sort the range!
20  oRange.Sort(oSortDesc())
21End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.