Listeners/Events etc

[mikejp]

Having trouble converting to LO from VBA. I have a calc sheet in which I wish to monitor 5 columns for changes. I have written the event code which works fine, except when I close the sheet I get the warning:
“Message: The following Basic script could not be found:
library: ‘Standard’
module: ‘Module1’
method: ‘RmvListener’
location: ‘document’”

Which is puzzling since the code is there

function Modify_RmvListener(oEv)
CellRng.removeModifyListener(oListener(a))

end function

oListener is acting on 5 columns (a=0 to 4)

Any pointers where to look to fix appreciated.

Edit 2019-04-09 (by Ratslinger) - Transfer data in not-an-answer answer to here:

Thanks very much, Kat. The progress to OO/LO coding from VBA has been painful largely due to the lack of code examples, unlike the VBA help side which often does contain these.

The code I am using was shamelessly lifted from a (rare) post on the OO forum in which the ever helpful FJCC posted it. Why he used Fn and non sub I know not.

‘a’ comes from

function add_listener
 Dim Doc as Object
 Dim Cellpos(5)
 Cellpos(0)="A8:A260"
 Cellpos(1)="F8:F260"
 Cellpos(2)="G8:G260"
 Cellpos(3)="K8:K260"
 Cellpos(4)="N8:N260"
Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0)  'get leftmost sheet
dim a
for a=0 to 4
CellRng = Sheet.getCellrangeByName(Cellpos(a))
oListener(a) = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
Cellrng.addModifyListener(oListener(a))  'register the listener
next a
end function

Re the fn call for RmvListener- yes, my original code was, as per FJCC,

function RmvListener
CellRng.removeModifyListener(oListener(a))
end function

but this was giving me a “BASIC runtime error. Variable not defined” warning when I closed the sheet and in the resulting blind thrashing around I tried the code I posted - which gave the new warning, but not the ‘variable’ warning. Commenting out the line ‘CellRng.removeModifyListener(oListener(a))’ stops the variable not defined warning.

If you can point to any source of LO/OO code sample I would appreciate it. How do I track down the Basic error on closing the sheet?

[Ratslinger]

@mikejp

Thanks very much, Kat.

Who is KAT?

When referring to code from another location (no problem in using code from forums) please provide a link. There seems to be a loss of some information here. Have found other posts but not the one where you got this code. Please provide the link.

[Ratslinger]

@mikejp Please stop posting comments as answers.

Posted by @mikejp:

Much appreciate the work there, Kat. I will go away and work with your code, thanks.
Yes, I already had Andrew’s PDF, but I find the lack of code samples on the internet for OO/LO a handicap - while I was getting to grips with VBA a simple query on ‘how do I…x/y/z’ would bring up several search hits which I could absorb. A simple, ignorant question in OO/LO like ‘why do I need a ‘remove’ sub for a listener if I am going to close the document it works on anyway’ is not found anywhere.
cont…

[Ratslinger]

FYI, the project is a large booking/invoicing system for a hall. It ran well in VBA but I wished to ‘move with the times’ into a better programming environment, but am finding it harder than I think it should be. A shame, as the OO/LO concept is brilliant. The whole task was made much more difficult by a few on the OO forums who seemed to go into hysterics at the mention of VBA and who appeared to think that newbies should have a hard time and whip themselves daily with rolled-up barded wire

I’ll get there. Thanks very much for your help.

[Ratslinger]

@mikejp

AGAIN ---- Who is Kat???

Where is the info I asked for.

[Ratslinger]

Hello,

There are more puzzling items than what you mention. Don’t understand why you chose to use a Function vs a Sub since there is nothing to return here. Also not certain why oEv is present since it is not even used. And where is the value for a coming from?

As for the error, somewhere (not much detailed in the question) there is an event pointing to RmvListener in Standard.Module1 but your routine is named Modify_RmvListener so no match there and thus the error.

Edit 2019-04-09:

Had a bit of time so put together a sample. Loosely based on your request. Still would like link to original code. Don’t understand what you have and how you have anything working with it. Must be missing something.

Information on listeners can be found in Andrew Pitonyak’s book - Open Office Macros Explained. PDF located here → OOME. See section 10.12. UNO listeners for further information. There are other samples around. Don’t understand your having a difficult time locating them.

Attached is a sample. There is a new toolbar on the sample which allows you to start and stop the listeners. Changes in the appropriate cells (you ranges used) will display a Message Box.

Sample ------- ListenerForColumns.ods

[Ratslinger]

Post a sample of your Base file. If you are getting the error message there is definitely an event somewhere which is causing the error. Have asked for that link and never got response - my very first comment.

Also have provided OOME link in answer which discusses listeners and has examples - closest you will find to tutorial.

[mikejp]

Option Explicit

Dim oListener(5) as Object
Public CellRng as Object
Dim Sheet as Object
Dim Range
Dim Cell
Global oMRI As Object

sub Main
add_listener
End sub

sub add_listener
Dim a as Integer
Dim Doc as Object
Dim Cellpos(5)
Cellpos(0)=“A8:A260”
Cellpos(1)=“F8:F260”
Cellpos(2)=“G8:G260”
Cellpos(3)=“K8:K260”
Cellpos(4)=“N8:N260”

Doc = ThisComponent
Sheet = Doc.Sheets.getByIndex(0)  'get leftmost sheet
for a=0 to 4
CellRng = Sheet.getCellrangeByName(Cellpos(a))
oListener(a) = createUnoListener("Modify_","com.sun.star.util.XModifyListener")  'create a listener
Cellrng.addModifyListener(oListener(a))  'register the listener
next a

end sub

'Sub StopModifyListeners
’ for a = 0 to 4
’ CellRng(a).removeModifyListener(vChangeListener(a))
’ Next a
'End Sub

Sub Modify_modified()
CalledRoutine

[mikejp]

That code sits on a master calc sheet configured to enable a selected month’s days and dates to be produced as a new workbook of two sheets for bookings in two separate areas of a hall. That is the ‘Listener’ bit. The rest of the code on the shet is the ‘called routine’. The code contains SOME of your code. When I ran it with ONLY your code it produced different warnings - cannot find ‘addlistener’ and cannot find something else to do with removing the listener. I have tried putting a token ‘RmvListener’ sub in

sub RmvListener()
end sub

but it still complains about not being able to find that!

end sub

The warning is received when I close the ‘master sheet’ having created the new month sheet.

[Ratslinger]

This code is absolutely of no help. This is basically the same as what you placed in your not an answer answer. Need copy of Base file. Somewhere in there is an event left behind from your initial attempt. Have now asked multiple times for the link to where this original code came from - still nothing. Again asking for a sample base file with the error.

My apologies for referring to a Base file. Been too long since original post and I did not view the Tags again.

Regardless, getting a copy of the Calc file would resolve this. Again the provided sample in my answer works. Must be a problem in what you have.

[Ratslinger]

Just tested the code you posted in last comment and it works without a problem, although not good to remove code to remove listeners. Can cause problems if add listener is run multiple times.

[mikejp]

Well I had ignored your ‘Base’ comment but we obviously have a language issue here What EXACTLY do you want? I just cannot understand how anything can be ‘left behind’ as you put it. I have searched the whole of the suite for ‘Listener’ and ‘Rmv’ and there is NO existence. What is the ‘Calc’ file that you need? I am not posting the whole suite since it is !VERY LARGE! It invoices, creates, credit./debits, changes, distributes to the web and makes coffee too.

Perhaps easier just to tell me what to look for to find this ‘residual’?

[Ratslinger]

Only asked for file since you can’t seem to find problem or get code to work or present link to original code. The pointer to a non-existing macro (your error message) can be in many places. Main place to look is in (from menu) Tools->Customize... under Events tab. There are other places but that is a starting point. Could also be under Sheet Events (sheet tab right mouse click).

[Ratslinger]

"FYI there is no mention of RMVLISTENER anywhere in my code within 100km of my house!

Hopefully with any future questions you will have a bit more trust in what someone is trying to help you with. You removed it but did not replace with the new one - StopModifyListeners

Seemingly logical - Open document add listeners; close document remove listeners.

[mikejp]

Rat - sorry to have been a while - other things. I put your code into the programme as you gave in given in Listenerforcolumns.ods

Sub StartModifyListeners
Dim oSheet as Object
Dim Cellpos(5)
Cellpos(0)=“A8:A260”
Cellpos(1)=“F8:F260”
Cellpos(2)=“G8:G260”
Cellpos(3)=“K8:K260”
Cellpos(4)=“N8:N260”
oSheet = ThisComponent.Sheets.getByIndex(0) 'get leftmost sheet
Dim a as Integer
for a = 0 to 4
CellRng(a) = oSheet.getCellrangeByName(Cellpos(a))
vChangeListener(a) = createUnoListener(“Modify_”,“com.sun.star.util.XModifyListener”) 'create a listener
CellRng(a).addModifyListener(vChangeListener(a)) 'register the listener
next a
End Sub

Sub StopModifyListeners
Dim a as Integer
for a = 0 to 4
CellRng(a).removeModifyListener(vChangeListener(a))
Next a
End Sub

Sub Modify_modified(oEvent)
oColumns = oEvent.Source.getColumns()
oElementNames = oColumns.getElementNames()
sColumn = oElementNames(0)
msgbox "Something Changed - Column " & sColumn
End Sub

Sub Modify_disposing(oEvent)
End Sub

Running your code I still get

A Scripting Framework error occurred while running the Basic script vnd.sun.star.script:Standard.Module1.RmvListener?
language=Basic&location=document.

Message: The following Basic script could not be found:
library: ‘Standard’
module: ‘Module1’
method: ‘RmvListener’
location: ‘document’

??

[Ratslinger]

It is not a surprise. Have asked previously in this post for you not to post as an answer unless it answers the original question. Moved that one myself to original question as an edit. Now this one again.

Now as for the error, it is the same as just stated - not looking at what is posted. In the text you just posted there is no RmvListener in the code you copied! This is some remnant from original code you had problems with - see your own original post. Since the sample works, then it is likely something else. Clean up the old before using the new.

[mikejp]

Sorry Rat, but I just do not understand
“It is not a surprise. Have asked previously in this post for you not to post as an answer unless it answers the original question. Moved that one myself to original question as an edit. Now this one again.”

FYI there is no mention of RMVLISTENER anywhere in my code within 100km of my house! Is there a tutorial on adding a listener?

[Ratslinger]

Only reason for not understanding is you have not been reading my messages. Have sent others (since deleted) & some re-posted in you question & comments.

Again - Answers are for responding to original question. Other information should either be added to original question or a comment.

Also there is no Kat or Rat. No further response to those!

[mikejp]

I take it English is not your first language?
You also are not reading my comments
"FYI there is no mention of RMVLISTENER anywhere in my code within 100km of my house! Is there a tutorial on adding a listener?

It would be a ‘HELP’ if queries were answered if this is to be a help forum!

[Ratslinger]

Have asked you not to post under answer. And to my point, answered all that already hours before your last comment. Please look at the comment under my answer. And you would get notifications on all of these - check your message box. RMVLISTENER doesn’t need to be in your code. It is being called from an event.