Sorting

Apache OpenOffice 4.1 Calc Guide

Chapter 12 Calc Macros

< Previous Section

Next Section >

• Introduction
  • The OpenOffice Basic Language
• Using the Macro Recorder
• Write Your Own Functions
  • Using a Macro as a Function
  • Passing Arguments to a Macro
  • Arguments are Passed as Values
  • Writing Macros that Act Like Built-in Functions
• Accessing Cells Directly
• Sorting
• Conclusion

< Previous Page

Next Page >

Sorting

Consider sorting the data in Figure 324. First, sort on column B descending, then column A ascending.

Figure 324: Sort column B descending and column A ascending

The example in Listing 9 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 oSortFields(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.
  SortFields(0).Field = 1
  SortFields(0).SortAscending = FALSE

  REM If column B has two cells with the same value,
  REM then use column A ascending to decide the order.
  SortFields(1).Field = 0
  SortFields(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

As this macro is a subroutine, execute it with Tools → Macros → Run Macro and then open CalcTestMacros > AuthorsCalcMacros > Module1. Click on the macro SortRange and then  Run .