Error when inserting a new row in a sheet with a macro

[rammy]

Hi,

I am working on a new sheet, but when I, the macro, wants to insert a new row in the sheet it stops executing the macro and reports the error:

BASIC runtime error.
An exception occured.
Type: com.sun.star.uno.RuntimeException.
Message: at ./sc/source/ui/unoobj/docuno.ccx:4429’

I have searched the net but can’t find a solutions for the problem.
I also tryed it with the dispatcher, this method produce no error, but a new line is not inserted.
I have made an example document to show the problem.

Example_LineInsert.ods (204.3 KB)

Who can help solving the mistery.

[sokol92]

Hello!
You use the Watch function in sheet formulas.
Such user define functions (UDFs) have a number of limitations.
One of them is the prohibition of making changes to the document.
For Excel this is described here , for Calc the situation is similar

[JohnSUN]

…in other words, trying to use a function to change something on the sheet where the function is located was doomed to fail from the very beginning. While the function code is being executed, the contents of the sheet are blocked and there is no way to bypass this restriction.
Rewrite the Watch function as a procedure and assign it as a sheet event handler.

[schiavinatto]

Hello @rammy , follow macron recipe.

Example_LineInsert_GS.ods (197,9,KB)

[fpy]

@JohnSUN

doomed to fail from the very beginning

mostly likely AI again.

[JohnSUN]

@fpy

AI again

I tried to read this code - even if it’s “artificial”, it’s not “intelligence”

[fpy]

@JohnSUN

it’s not “intelligence”

sure.

but since it’s obviously growing, it should be handled as FAQ rather than going down the rabbit hole again and again

Unable to create HTTP client. Make sure LibreOffice is configured to allow HTTP requests

You write that as if it is better to generate a nonsensical answer to one’s question, waste the questioner’s time trying to make the answer work, waste time of others who try to help the questioner when they turn to the Ask site seeking for clarification… and now that non-existent service name has appeared here on the Ask LibreOffice site, adding to the noise of the Internet. No idea what could that mean. Possibly you hint on something, but not everyone could guess.

[rammy]

I do no want to use the dispatcher because the sheet is completely build by a macro.

[rammy]

Good advise,
I rewrote my Watch macro and used the sheet event handler to call the Watch macro to handle the cell changes.
That works now.

What code could I use to implement the action:

Sheet.SheetEvents.ContentChanged.Macro.General.Watch

that I take to tell the sheet event handler to use the Watch macro for that sheet.
I tryed it with the dispatcher but that does not work, so I need to implement it in BASIC.,

[rammy]

I found the solution at https://ask.libreoffice.org/t/how-to-set-a-macro-to-an-sheet-event-by-a-macro/14765/2

[rammy]

I wrote the code with the knowledge I have. If you have a better solution, please let me know so I can learn from it.