Calc Guide

For iRow = LBound(oRows()) To UBound(oRows())
oRow() = oRows(iRow)
For iCol = LBound(oRow()) To UBound(oRow())
TheSum = TheSum + oRow(iCol)
Next
Next
Next
SumCellsAllSheets = TheSum
End Function
Tip
When a macro is called as a Calc function, the macro cannot modify any
value in the sheet from which the macro was called.
Sorting
Consider sorting the data in Figure 320. First, sort on column B descending and then
column A ascending.
Figure 320: 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")
Chapter 12 Calc Macros 311