CALC: How to write a macro that can be used as a formula?

Want to enter a macro in one cell of a spreadsheet and have it calculate something. Need it to automatically run when the spreadsheet is opened.

For example, the following returns the number of sheets:

Function Number_of_Sheets()

Dim oSheets
Dim oSheet
oSheets = ThisComponent.getSheets()
oSheet = oSheets.getCount()
Number_of_Sheets = oSheet
End Function

In the spreadsheet, I enter:

=number_of_sheets()

in a particular cell and right away the cell contains the number of sheets open in that particular spreadsheet. The macro is in the standard library under “My Macros & Dialogs”.

But if I close the spreadsheet and then re-open it, I get a message box saying " Due to an unexpected error, LibreOffice has crashed. All the files…" and I have to go through the procedure to recover the file.

What must I do to fix this?

Doesn’t crash here. Which LibreOffice version and platform are you using?

Hello @,

Are you sure that the crash was caused by that function ?

Running Ubuntu 16.04, Using Libreoffice version:

Version: 5.1.6.2
Build ID: 1:5.1.6~rc2-0ubuntu1~xenial2
CPU Threads: 6; OS Version: Linux 4.4; UI Render: default;
Locale: en-US (en_US.UTF-8); Calc: group

Basic runtime error. Property or method not found: getsheets

So is getsheets() no longer valid? I’ve seen it in more than one calc macro example in tutorials.

When Calc open a document, the formulas are recalculated twice, at loading and when displayed. At moment of loading not all the objects and document properties are available yet - for example, ThisComponent is not yet defined and this causes an error when trying to access its properties and methods.You can disable recalculation of formulas on document loading for the entire document (Tools - Options’ - LibreOffice Calc - Formula’) or for individual functions (see my answer below)

formulas are recalculated twice

That’s not true.

Yes, you’re right - the idea expressed inaccurately: “to after opening the books cell displays the result of calculation of the custom function, you need to make the formula recalculated twice”. That’s better?

To handle errors in BASIC macros, use the On Error statement

Like this:

Function Number_of_Sheets() As Variant 
	Number_of_Sheets = "Press Shift+Ctrl+F9 to recalculate formulas"
On Error Resume Next
	Number_of_Sheets = ThisComponent.getSheets().getCount()
End Function

Without user code: =SHEETS() in any cell.

In addition: If you place a cuboid as a parameter to SHEETS() like in =SHEETS(Sheet3.B7:Shet5.G8) only the sheets spanned by the cuboid are counted.

When I put your function in MyMacros I get “Basic runtime error. Property or method not found: getsheets” but LO does not crash. If I put the function in a module in the spreadsheet it works fine. As @JohnSUN said ThisComponent has not been fully defined. It appears to be a timing sequence error and when the function is in the sheet module ThisComponent has been defined. There have been postings in the past referring to this problem.

I doubt that. The moment formula expressions are recalculated the document and component are fully set.

Might be other problems with some older version (OP uses 5.1.6), but tried in 5.3.7 and later it works.

Thanks, that makes sense. I’ll have to find a work-around.