Sorting

OpenOffice.org 3 Calc Guide

Chapter 12 Calc Macros

< Previous Section

Next Section >

• Using the macro recorder
• Write your own functions
• Using a macro as a function
• Passing arguments to a macro
• Accessing cells directly
• Sorting

< Previous Page

Next Page >

Consider sorting the data in the figure below. First, sort on column B descending and then column A ascending.

Sort column B descending and column A ascending.

The example in Listing 9, however, demonstrates how to sort on two columns.

Listing 9. Sort cells A1:C5 on Sheet 1.

Sub SortRange
  Dim oSheet        ' Calc sheet containing data to sort.
  Dim oCellRange    ' Data range to sort.
 
  REM An array of sort fields determines the columns that are
  REM sorted. This is an array with two elements, 0 and 1.
  REM To sort on only one column, use:
  REM Dim oSortFields(0) As New com.sun.star.util.SortField
  Dim oSortFields(1) As New com.sun.star.util.SortField
 
  REM The sort descriptor is an array of properties.
  REM The primary property contains the sort fields.
  Dim oSortDesc(0) As New com.sun.star.beans.PropertyValue
 
  REM Get the sheet named "Sheet1"
  oSheet = ThisComponent.Sheets.getByName("Sheet1")
 
  REM Get the cell range to sort
  oCellRange = oSheet.getCellRangeByName("A1:C5")
 
  REM Select the range to sort.
  REM The only purpose would be to emphasize the sorted data.
  'ThisComponent.getCurrentController.select(oCellRange)
 
  REM The columns are numbered starting with 0, so
  REM column A is 0, column B is 1, etc.
  REM Sort column B (column 1) descending.
  oSortFields(0).Field = 1
  oSortFields(0).SortAscending = FALSE
 
  REM If column B has two cells with the same value,
  REM then use column A ascending to decide the order.
  oSortFields(1).Field = 0
  oSortFields(1).SortAscending = True
 
  REM Setup the sort descriptor.
  oSortDesc(0).Name = "SortFields"
  oSortDesc(0).Value = oSortFields()
 
  REM Sort the range.
  oCellRange.Sort(oSortDesc())
End Sub 

Conclusion

This chapter provides a brief overview on how to create libraries and modules, using the macro recorder, using macros as Calc functions, and writing your own macros without the macro recorder. Each topic deserves at least one chapter, and writing your own macros for Calc could easily fill an entire book. In other words, this is just the beginning of what you can learn!