Ask Your Question
0

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

asked 2017-12-07 19:44:56 +0200

this post is marked as community wiki

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

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?

edit retag flag offensive close merge delete

Comments

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

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

Hello @,

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

librebel gravatar imagelibrebel ( 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

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.

raymondvillain gravatar imageraymondvillain ( 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)

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

formulas are recalculated twice

That's not true.

erAck gravatar imageerAck ( 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?

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

3 Answers

Sort by » oldest newest most voted
1

answered 2017-12-07 20:45:23 +0200

JohnSUN gravatar image

updated 2017-12-07 20:45:51 +0200

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
edit flag offensive delete link more
0

answered 2017-12-07 22:32:19 +0200

Lupp gravatar image

updated 2017-12-07 22:37:36 +0200

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.

edit flag offensive delete link more
0

answered 2017-12-08 12:39:39 +0200

peterwt gravatar image

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.

edit flag offensive delete link more

Comments

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.

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

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

raymondvillain gravatar imageraymondvillain ( 2017-12-09 03:32:08 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2017-12-07 19:44:56 +0200

Seen: 394 times

Last updated: Dec 08 '17