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:


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


A range can be sorted using up to three columns or rows in a macro. Sorting with extra columns or rows is as easy as adding extra sort fields to a sort descriptor. The macro below uses the class grade sheet in cells $Sheet1.A1:H11 of the linked spreadsheet to illustrate how to sort by two columns. The records are sorted in ascending order, first by Quiz #1 scores and then by Quiz #2 scores.


 1Sub SortByQuizScores
 2  Dim oSheet
 3  Dim oRange
 4  Dim oSortFields(1) as new
 5  Dim oSortDesc(0) as new
 7  oSheet = ThisComponent.Sheets(0)
 9  REM Set the range on which to sort
10  oRange = oSheet.getCellRangeByName("A1:H11")
12  REM Sort by the Quiz #1 field in the range
13  oSortFields(0).Field = 4
14  oSortFields(0).SortAscending = True
15  oSortFields(0).FieldType =
17  REM Sort by the Quiz #2 field in the range
18  oSortFields(1).Field = 5
19  oSortFields(1).SortAscending = True
20  oSortFields(1).FieldType =
22  REM Set the sort fields to use
23  oSortDesc(0).Name = "SortFields"
24  oSortDesc(0).Value = oSortFields()
26  REM Now sort the range!
27  oRange.Sort(oSortDesc())
28End Sub

This Calc spreadsheet contains the above LibreOffice Basic code.