Niklas Nebel wrote:
Matthias Benkmann wrote:
It seems that when I use a custom Basic function in a cell formula,
during the executing of this function, I can't write to any cells.
Statements such as
ThisComponent.Sheets.getByIndex(0).getCellByPosition(4,1).setValue(33)
are silently ignored. Why does this happen? Is there a way around it?
A function used in a formula is supposed to return a value, not modify
the sheet that contains it.
I'm looking for an easy way to trigger a function whenever a cell is
changed. The function is supposed to have full access to the sheet.
What's the most simple way to do this (if I can't just use a Basic
function in the cell formula) ?
The cell has a XModifyBroadcaster interface, so you can use
addModifyListener.
Niklas
Matthias,
1. I am afraid I don't understand the first part of your problem.
That may be due to my limited frame of mind (which sometimes has
its advantages...) Do you mean to use a formula in a cell that refers to,
hence calls a Basic function (or sub) when that cell's contents change?
When that cell's contents change your formula will be overwritten,
i.e. it's gone, so nothing can happen.
2. The solution to the second part is more straightforward: you need
a listener that registers the change and triggers action. Andrew Douglas
Pitonyak
(see other e-mail) has a very instructive chapter (11) on Listeners in his
Macro Document (http://www.pitonyak.org/AndrewMacro.sxw), but no example
that exactly covers what you need, I'm afraid.
3. Some OOo versions ago I dug up a subroutine somewhere (in the Calc
libraries?) which I adapted.
A cell supports methods to install different listeners.
I used addChartDataChangeEventListener( ).
Niklas refers to an addModifyListener. I didn't try that.
Don't get confused by the ChartDataChange part. It doesn't need a chart, it
just reacts to a DataChangeEvent.
Sub trCellDataChange
On Error Goto Errorhandler
oDocument = ThisComponent
oSheet = oDocument.Sheets.getByName( "import" )
oTrCell = oSheet.GetCellRangeByName( "imp_action" ) 'A single
cell range
oTrCell.String = "dataChngTest"
'Not needed. Demo only.
' XRay.XRay oTrCell 'Helps in finding properties and methods for a
cell(range).
oServiceMgr = GetProcessServiceManager()
oTrCellData =
oServiceMgr.createInstanceWithArguments("com.sun.star.chart.XChartData",
oTrCell )
oTrCellData = CreateUnoListener( "trCellListener_",
"com.sun.star.chart.XChartDataChangeEventListener" )
oCellLib = BasicLibraries.getByName( "Standard" )
oCellLib.addEventListener( oTrCellData )
oTrCell.addChartDataChangeEventListener( oTrCellData )
Errorhandler:
MsgBox "Errorcode: " + Err + Chr$(13) + Error$ 'Error code 1 =
UNO exception
Resume Next
End Sub
Sub trCellListener_chartDataChanged( oEvent )
Call reactionTrCell
'Trigger action somewhere.
MsgBox "chartDataChanged. What took you so long?" 'May be replaced
by some action.
End Sub
Sub trCellListener_queryInterface( oEvent )
MsgBox "queryInterface" 'May be replaced by some
action.
End Sub
Sub trCellListener_disposing( oEvent )
MsgBox "disposing" 'May be replaced by
some action.
End Sub
Sub reactionTrCell 'Sub called as
result of the data change in the cell
oDocument = ThisComponent 'This may do anything in the document.
oSheet = oDocument.Sheets.getByName( "import" )
oCell = oSheet.GetCellRangeByName( "$M$1" )
oCell.String = "triggered by data change"
End Sub
The first sub installs the listener. Any subsequent change in the cell will
result in a call to reactionTrCell.
4. I don't know how many cells you wish to guard this way. In case of
a significant
number it might be advisable to add the listener to a range rather than
a single cell.
A multicell range has the same listener methods. Any change in the range
will
trigger action. You will not know which cell, of course. That could be
found e.g.
with two arrays, one containing the previous data and the other the
changed data,
and then compare contents. Maybe two enumerations? I don't know about that.
I hope this helps,
Sincerely,
Gerrit Jasper
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
