How to Use a Calc Macro to Monitor Cell Contents

Processing User Inputs to OpenOffice.org Calc Using Macros

Nov 23, 2008
Mark Alexander Bain

A Macro for Monitoring Calc Cell Contents - Mark Alexander Bain

This simple tutorial shows how to monitor the contents of an OpenOffice.org Calc document by assigning listeners to cells in the spreadsheet.

OpenOffice.org Calc is a very powerful spreadsheet application, and it's made even more powerful by the fact that a user can write macros that will process the data that they, or other users, enter. However, that processing can only take place when the user:

presses a button that has a macro associated with it
carries out a predefined key stroke or causes an event such as closing the document - again each of these will need a macro associated with it
manually runs the macro themselves

On the other hand the macro developer may, if they wish, add a listener that can monitor the contents of one or more cells and then run additional code if there is a change in the contents in any of the cells.

Adding an OpenOffice Listener from a Macro

A listener is simply a process attached to an OpenOffice object (such as a cell) which runs in the background, waiting for a defined event to occur (such as a change in the contents of the cell).

The first step is to create two global variables:

an array for storing any cells to be monitored
a variable for the listener itself

Option Explicit 'Enforce variable definition

Global oCell(0)

Global oListener

Next a subroutine is needed - this subroutine will add cells to the array as necessary, and then add a listener to the cell:

Sub add_cell_listener (cell_id As String)

Dim oSheet

Dim i As Integer

i = ubound(oCell) + 1

Redim Preserve oCell(i)

oSheet = thisComponent.Sheets("Sheet1")

oCell(i) = oSheet.getCellRangeByName(cell_id)

oListener = CreateUnoListener( "CELL_", _

"com.sun.star.chart.XChartDataChangeEventListener" )

oCell(i).addChartDataChangeEventListener(oListener)

End Sub

It's worth noting that there is not a listener specifically for cells - what's actually being used here is the OpenOffice functionality for updating charts.

The Data Changed Subroutine

When the listener is defined the prefix for the 'data changed' subroutine is also defined (in this case the prefix is CELL_), and the suffix must be chartDataChanged:

Sub CELL_chartDataChanged

The contents of each of the monitored cells can now be processed:

Dim i As Integer

for i = 1 to ubound(oCell)

msgbox "Cell value = " & oCell(i).Value & chr(10) _

& "Cell String = " & oCell(i).String & chr(10) _

& "Cell Formula = " & oCell(i).Formula

next i

End Sub

Starting the Listeners

The listeners are not assigned to the cells automatically - for that a final subroutine is required:

Sub Main

add_cell_listener "A1"

add_cell_listener "A2"

End Sub

Of course, this subroutine can then be run whenever the document is opened by assigning it to the 'Open Document' event.

Conclusion

In this tutorial listeners have only been assigned to two cells (A1 and A2) but as many cells as necessary can be used, and the final cell processing is very simple - again that can be extended as required to produce more complicated results on the spreadsheet.

Mark Alexander Bain - Mark Alexander Bain is a writer, Mo Bro and consultant for all aspects of software development at dsquared. He has also written regularly ...