Calc: ThisComponent.calculateAll() macro not working

[ArminLinder]

I have a spreadsheet (ods format) with a cell containing a value calculated by a macro, the value is derived from the name of the spreadsheet file. Whenever I re-open the spreadsheet the cell contains an error message. This is annoying, but expected/documented behaviour. But how do I get the correct value after the document has finished loading?

If I recalculate the cell by hand (F9), the value displays correctly.

To automate things, I put this macro into “My Macros”:

Sub RecalculateAllCells()
ThisComponent.calculateAll()
End Sub

and assigned it to the “View created” event. Nothing happens. I put a breakpoint into the calculateAll() line of the macro, nothing happens. If I let the spreadsheet load, and start the macro by hand using “Tools” - “Macros” - “Run macro” - “RecalculateAllCells”, nothing happens (and the breakpoint does not trigger). If I select all cells (click on left top corner of spreadsheet header bar) all the spreadsheet cells highlight, then press F9, nothing happens. If I highlight the affected cell only and press F9, it recalculates correctly. No error message appears anywhere.

Any ideas? LibreOffice 7.1.8.1 (x64) on Windows 11.

Thnx, Armin.

[ArminLinder]

AAargh, I resolved it myself.

I had to lower the macro security level from “high” to “medium”.

Now the spreadsheet updates on load like expected.

[erAck]

A safer approach would be to set Macro Security to very high, add a directory to Trusted Sources, Trusted File Locations, and place the document in that directory, or on capable systems add a symbolic link there pointing to the original location if you don’t want to move the document and then open the document from the trusted location.