How to Use a Calc Macro to Monitor Cell Contents

Processing User Inputs to OpenOffice.org Calc Using Macros

Nov 23, 2008

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:

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).

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

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.

The copyright of the article How to Use a Calc Macro to Monitor Cell Contents in Computer Programming Tutorials is owned by Mark Alexander Bain. Permission to republish How to Use a Calc Macro to Monitor Cell Contents in print or online must be granted by the author in writing.