From Apache OpenOffice Wiki
Jump to: navigation, search

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
  Dim oSortFields(1) As New
  REM The sort descriptor is an array of properties.
  REM The primary property contains the sort fields.
  Dim oSortDesc(0) As New
  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.
  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.
End Sub 


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!

Content on this page is licensed under the Creative Common Attribution 3.0 license (CC-BY).
Personal tools