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

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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


=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?

edit retag close merge delete

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

( 2017-12-07 20:29:53 +0200 )edit

Hello @,

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

( 2017-12-07 20:53:56 +0200 )edit

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

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

( 2017-12-08 04:25:30 +0200 )edit

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)

( 2017-12-08 06:30:49 +0200 )edit

formulas are recalculated twice

That's not true.

( 2017-12-08 14:57:37 +0200 )edit

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?

( 2017-12-08 15:54:45 +0200 )edit

Sort by » oldest newest most voted

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

more

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.

more

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.

more

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.

( 2017-12-08 15:09:30 +0200 )edit

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

( 2017-12-09 03:32:08 +0200 )edit