LibreOffice calc: automatically sort data upon data entry

A common task in LibreOffice (or Apache OpenOffice) calc is to sort a range of cells via Data -> Sort... where the sort criteria (columns, sort order) are defined.
If the spreadsheet is a living document where entries are deleted and new entries entered on a regular basis one would wish for this sorting to be done automatically rather than the user having to do it manually each time. In this article I show how to achieve this with macros in LibreOffice.

The very basic idea of automation is to assign a macro to an event. In case that event happens the macro is being run.

But before I get to that I'd like to outline the steps and how they are connected.

First define a range for the cells to be sorted

Then create the macro which will be triggered (it will make use of the range)

Assign the macro to the 'change content' event Now whenever the content of a cell is changed the macro will be triggered and run.

Define a range

Assume the data to be sorted are in two columns A and B. Currently I have only 4 entries but there could be more in the future. The columns also have a header in the first row.
The range is set by

marking columns A and B

going to Data -> Define Range... and entering a name for the selection and finally clicking Add and OK. I chose the name 'MyData'.

Create a macro

It would suffice to create one macro but I opted to split the macro into two, one being of a more general reusable nature and the other more specific to the event.

The first macro 'SortRange' sorts a range by its second column in descending order using the first row as header i.e. not part of the actual sort. The macro gets a range object as parameter and thus could be used for any range.

Sub SortRange( oRange As Variant )     
rem Sorts range 'sRange' by column 2 in descending order
rem (assumes that columns have labels in first row)

rem ----------------------------------------------------------------------
rem define variables
dim document   as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document   = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------
rem Select the range
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = oRange.AbsoluteName

dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())

rem ----------------------------------------------------------------------
dim args2(7) as new com.sun.star.beans.PropertyValue
args2(0).Name = "ByRows"
args2(0).Value = true
args2(1).Name = "HasHeader"
args2(1).Value = true
args2(2).Name = "CaseSensitive"
args2(2).Value = false
args2(3).Name = "NaturalSort"
args2(3).Value = false
args2(4).Name = "IncludeAttribs"
args2(4).Value = true
args2(5).Name = "UserDefIndex"
args2(5).Value = 0

Rem Sort by the second column of the range !!!
args2(6).Name = "Col1"
args2(6).Value = oRange.RangeAddress.StartColumn + 2

args2(7).Name = "Ascending1"
args2(7).Value = false

dispatcher.executeDispatch(document, ".uno:DataSort", "", 0, args2())

End Sub

Note how the Data -> Sort... Options are mapped to these properties and could be adjusted if one wanted different sorting criteria.

The second macro 'SortRangeFilter' checks whether the change actually happened inside the range and then calls the SortRange macro. The name of the range is hardcoded in this macro and thus makes it inflexible but I couldn't find a better way (yet).
Macros which are triggered by an event listener get passed a parameter which describes the source object. These can be different things which I need to differentiate in my code:

a single cell e.g. when entering a value

a range of cells e.g. when deleting a row

a set of ranges e.g. when marking and deleting two disjunct cells
In the last cases my selection might contain cells which are not members of my defined range. In order to make my code the most flexible I apply the following logic:

a single cell is just a special case of range of cells thus I only need to differentiate two cases

for the remaining cases I determine the intersection of my named range with the selection (= source of the event). If it is empty there is nothing to do, otherwise I need to sort the range.

Sub SortRangeFilter( oEvent As Variant )

Dim sRange As String
sRange = "MyData"
Rem       ^^^^^^
Rem       Hardcoded range name

Rem Get the range object in order to access its boundaries
Dim oSheet As Variant
oSheet = ThisComponent.CurrentController.getActiveSheet()

Rem Define an error handler in case the range name does not exist
On Error Goto ErrorHandler
Dim oCellRange As Variant
oCellRange = oSheet.getCellRangeByName( sRange )

Rem Check the type of 'oEvent'
Rem Both support the 'queryIntersection' method
If ( oEvent.supportsService( "com.sun.star.sheet.SheetCellRanges" ) Or _
     oEvent.supportsService( "com.sun.star.sheet.SheetCellRange" )) Then
  If oEvent.queryIntersection( oCellRange.RangeAddress ).count > 0 Then
    SortRange( oCellRange )
  Endif
Endif
Exit Sub

ErrorHandler:
  Msgbox "SortRangeFilter: Range does not exist '" + sRange + "'"
  Exit Sub
End Sub

Note: rather than hardcoding the range name one could check all defined ranges for the current sheet and try to find the right one but there are a number of drawbacks:

a cell could be a member of several ranges (since ranges can overlap). Which range should be picked for sorting?

if multiple cells are selected (e.g. a row) there is an even higher chance of confusion. Some of the selected cells might be in range 1, others in range 2 etc. so which range is to be picked? And it is unlikely that all ranges need to be sorted.

Both macros need to be entered in a macro library. I chose My Macros -> Standard -> Module1.

Set event listener

Now that the macro is in place I can set up the event trigger.

Right click on the sheet name and choose Sheet Events...

In the Assign Action window select Content changed and click Macro...

In the Macro Selector window navigate to (in my case) My Macros -> Standard -> Module1 and select SortRange in the list of macros and click OK

Back again in the Assign Action window click OK too

Now you have it: any change in the range i.e. columns A or B will trigger a re-sort. Any other change on the sheet won't.
If you create the range in a different place the sort will still be triggered.

---

---

Below I'll show a couple of scenarios and how the macros behave. Assume there is a defined range 'MyData' somewhere on the spreadsheet, say C5:D10. The column headers have been entered in B5 and C5.

Then the sheet event trigger needs to be assigned as described above and I can start entering the data.

I begin with B6=A and C6=20. Nothing happens other than the named range being marked due to the macro. Then I enter B7=B and C7=34 and now the sort trigger already fires and sorts my columns and reverses the order.
I enter another data set B8=C and C8=51
And finally the last data B9=D and C9=40 which is moved up two rows.

Always starting with the last setup I show various scenarios to delete cells and how the sort automatically kicks in.

Mark a single cell and delete it
Mark a row and delete it
Mark some disjunct cells and delete them
If you accidentally delete the range 'MyData' you get an error message

Since my macro selects the data range it stays this way after the sort has finished. Of course this could be changed but I view it as an indicator that something happened, the viewer is visually attracted to the marked data range.