Bug 79588 - Macros called from within formulas doesn't survive save, close and reopen of the calc-document.
Summary: Macros called from within formulas doesn't survive save, close and reopen of ...
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
4.2.3.3 release
Hardware: x86-64 (AMD64) Windows (All)
: high major
Assignee: Not Assigned
URL: https://bz.apache.org/ooo/show_bug.cg...
Whiteboard:
Keywords:
Depends on:
Blocks: Macro
  Show dependency treegraph
 
Reported: 2014-06-03 13:20 UTC by Bernhard
Modified: 2020-01-20 17:47 UTC (History)
7 users (show)

See Also:
Crash report or crash signature:


Attachments
Used to calculate money of accounts for different month (173.39 KB, application/vnd.oasis.opendocument.spreadsheet-template)
2014-06-03 13:20 UTC, Bernhard
Details
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods (11.89 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-02-21 01:52 UTC, Richard Bruce Baxter
Details
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad-withoutEmbeddedMacro.ods (11.58 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-02-21 01:53 UTC, Richard Bruce Baxter
Details
Example of my ods file (20.91 KB, application/vnd.oasis.opendocument.spreadsheet)
2017-10-16 14:52 UTC, fcyrilf
Details
function_not_evaluated_on_change_in_reference (9.43 KB, application/vnd.oasis.opendocument.spreadsheet)
2020-01-19 10:34 UTC, b.
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Bernhard 2014-06-03 13:20:58 UTC
Created attachment 100358 [details]
Used to calculate money of accounts for different month

Hi all,

reading the existing bugs doesn't confirms me, that my error behaviour belongs to one of them.

I use Ubuntu 14.04 release with a LXDE-Desktop on a i386 notebook with 8GB ram.
Don't know whether this helps.

I created own macros and stored them within the .ots file within a new library within a new module. 
I assigned some to buttons of a symbol bar, some to graphical objects and some I needed to call from within formulas stored within a cell.
Every macro ran and did its job.

I saved and closed the document.

Opening the document to continue my work the following behaviour was present.
Macros assigned to buttons of symbol bars and graphical objects are called and worked well.
Macros used within formulas seem to be unknown there.
1) Was the macro built with some capital letters, its name within the formula is now completely written in lower case letters.
2) Depending on the formula of the cell, sometimes it returned sometimes #NAME!.
3) None of the macros used within formulas seem to be called.
The name and code of the macros itself within the code module look unchanged and Ok like before.

Result - My sheet doesn't work anymore after saving, closing and opening again.

Workaround1 (but not practical for many formulas):
1) I have to edit something within the macro module and save it again.
2) Then I need to correct the call of the macro within the formula.
Then the stuff works again for this session.

Workaround2 (not always possible):
I try to use no macro if possible.

Hope you can help me, 
                      Bernhard
Comment 1 Eike Rathke 2014-06-03 19:22:40 UTC
It could be easier if you would actually give a hint what exactly fails where and when.

For example, on sheet Hilfsfelder in H6 there's a formula that uses handelmonat and handeljahr (probably the macros you mentioned), but the entire document does not contain any definition for those so there's already a #NAME? error. Similar for the name jahreingabe in F12. If a name can not be found it's occurrence is changed to lowercase in the formula, but that's just a symptom, not the cause. Might be that these were defined in a profile wide macro library that either isn't present anymore in your installation or isn't accessed by the saved document but only from the template file.

Sorry, but as given without these macros and a description where exactly they are located there isn't much we could do. Please provide the necessary information if possible.


Furthermore, some named expressions use the DATEVALUE() function (German DATUMWERT) with a locale dependent string argument. This will work correctly only in a de-DE (German-Germany) locale but may give Err:502 in other locales. Either make sure you didn't change locale on your system or better use the locale independent DATE() function instead (which takes numeric arguments instead of strings so may need some rewrite of your expressions). There are also other expressions that rely on the German date format.

Last but not least, something seriously broke the formulas on sheet Drucken in columns B and K, they contain series of concatenated #REF! errors. This already in the .ots you attached.
Comment 2 Bernhard 2014-06-03 20:39:11 UTC
(In reply to comment #1)
> It could be easier if you would actually give a hint what exactly fails
> where and when.
> 
> For example, on sheet Hilfsfelder in H6 there's a formula that uses
> handelmonat and handeljahr (probably the macros you mentioned), but the
> entire document does not contain any definition for those so there's already
> a #NAME? error. Similar for the name jahreingabe in F12. If a name can not
> be found it's occurrence is changed to lowercase in the formula, but that's
> just a symptom, not the cause. Might be that these were defined in a profile
> wide macro library that either isn't present anymore in your installation or
> isn't accessed by the saved document but only from the template file.
> 
> Sorry, but as given without these macros and a description where exactly
> they are located there isn't much we could do. Please provide the necessary
> information if possible.
> 
> 
> Furthermore, some named expressions use the DATEVALUE() function (German
> DATUMWERT) with a locale dependent string argument. This will work correctly
> only in a de-DE (German-Germany) locale but may give Err:502 in other
> locales. Either make sure you didn't change locale on your system or better
> use the locale independent DATE() function instead (which takes numeric
> arguments instead of strings so may need some rewrite of your expressions).
> There are also other expressions that rely on the German date format.
> 
> Last but not least, something seriously broke the formulas on sheet Drucken
> in columns B and K, they contain series of concatenated #REF! errors. This
> already in the .ots you attached.

Hi Eike,

thank you for the quick response.
You are wright - I gave too less information.
Here is a short description.

Only the sheet Handel is important here to show the problem.
Ignore the other sheets, they are not completely finished.
Also the buttons ExportPDF, Sortieren and DruckenMonat on the symbol bar in Handel is still under construction and may not work, that is OK.
Try the following:

1) On A3 and B3 I put some buttons to in-/de-crement the values for month and year above. I open the document, and they work. The macros assigned do their job when I press a button --> OK

2) On D3 you find a button calling a macro which set Q1 to 0. You can try that: Set Q1 manually to 5 and press the button on D3 and Q1 becomes 0 --> OK

3) The formula in R1 depends on Q1 and will be executed, when Q1 is updated, even with the same value as before. So pressing the button on D3 should execute the formula in R1 because of the update of Q1. Important is, that the macro "getrowofcursor" within the formula is not called! If I set the cursor in C7 and press the button on D3, it should write 7 into R1, but it doesn't.
I see the names of the macro within the formula in R1 is written in lowercase letters.
To correct that: When I now adapt one of the names within the formula in R1 to GETROWOFCURSOR and press enter, everything works. After that all names of that macro-call in that formula are written in capital letters and the button on D3 works as expected!?
After saving, closing and opening the document again, the functionality of the formula in R1 has gone - WHY??? --> ERROR

The point is, macros called by pressing a button work fine,
but macros called from within formulas don't work!

I wrote the formula "=GETROWOFCURSOR()+Q1-Q1" into R3 and press enter - it works. Pressing the button on D3 adapts R3 with the row of the current cursor position. After saving, closing and opening the document again, I see #NAME? in R3 and the button on D3 doesn't work anymore.

That is the behaviour on my PC.
Comment 3 Joel Madero 2014-06-04 02:40:52 UTC
Not a blocker - lowering to major for now. Blockers are literally saved for only the most serious bugs (for instance if most users could not install LibreOffice). Majority of users don't use macros - data loss therefore seems like major.
Comment 4 Bernhard 2014-06-13 11:45:17 UTC
(In reply to comment #3)
> Not a blocker - lowering to major for now. Blockers are literally saved for
> only the most serious bugs (for instance if most users could not install
> LibreOffice). Majority of users don't use macros - data loss therefore seems
> like major.

May be this bug is not a blocker for using LibreOffice Calc.
But it may be a blocker for those, who need this functionality and no workaround is available. 

May be that the majority of users don't use macros, but many (more as we believe) want it. But when such a basic functionality (for macros) doesn't work and has no workaround, they never change from MS-Office to Linux because complex text-documents, spreadsheets or databases may be their main usage.

It IS a blocker for all those people who want to use macros, but not writing bug-reports.

Yes, I know, this is only one individual opinion.
Comment 5 Eike Rathke 2014-06-13 20:42:02 UTC
I don't know why, but moving the getrowofcursor() function from BASIC module Wodi/Kassenbericht to module Standard/Kassenberichte helps. Somehow the function is not found in the Wodi module after the document is opened, but is found after having opened the Basic manager once.

Someone who wants to deep dive into the BASIC collection internals would be welcome ...
Comment 6 Bernhard 2014-06-18 13:40:46 UTC
Hi Eike,

wow - that works!!!
It is a workaround I can live with.
Thank you for your help.

Before I stopped programming (6 years ago), I would enjoy learning the object tree with its internal. Now I am a user helping some friends to convert their excel-, word- and access-applications to linux with libre-office. Also I try to get a more ergonomic user interface to their spreadsheets.
There is no time pressure and as long as I find a solution, they continue working with linux. When I reach a blocking point, they go back to MS-Office and Windows.
But they really want to stop working with Windows as far as possible.
From my point of view, the most built spreadsheet applications should be built as database application. But the people are users which are lucky to fill out a table and get some sums and statistics.
Comment 7 Joel Madero 2014-06-18 14:28:12 UTC
Highest is reserved for other purposes so just bumping this down to high. Thanks Bernhard
Comment 8 Alex Thurgood 2015-01-03 17:40:24 UTC Comment hidden (no-value)
Comment 9 Olivier Hallot 2015-12-14 17:12:18 UTC
(In reply to Eike Rathke from comment #5)
> I don't know why, but moving the getrowofcursor() function from BASIC module
> Wodi/Kassenbericht to module Standard/Kassenberichte helps. Somehow the
> function is not found in the Wodi module after the document is opened, but
> is found after having opened the Basic manager once.
> 
> Someone who wants to deep dive into the BASIC collection internals would be
> welcome ...

I think the issue is the same as reported in bug 54854.

But also I have to ask: Should all Basic libraries stored in a file be loaded always when the file is loaded? IOW, have all the file Basic libraries and modules available when opening it? At the moment, only the Standard library is loaded when the file is opened.

This can explain why addin functions (those written in basic and stored in libraries) don't work when opening a file.
Comment 10 Forester 2016-02-13 09:30:08 UTC
> I think the issue is the same as reported in bug 54854.

See bug 54854 comment 22.

If I code +1 where I should code -1, that is a bug.  I'm sure others have made the
same mistake.  When would it be helpful to consider two identical coding errors to
be the same bug ?

The answers is when it means both get fixed all the sooner.

I look forward to progress on bug 54854 and this issue in the near future.

I have to agree with Bernhard that button pushing users may be able to transition
to LibreOffice easily but commercial organisations with a history of macros and
stuff that need porting face a very difficult transition.

I'm sure the Document Foundation regrets the current state of affairs but as
society in general moves from one where people understood shit in order to make
shit happen to one where people just push an intuitive OK button, things are only
going to get worse.
Comment 11 Richard Bruce Baxter 2017-02-21 01:51:04 UTC
This issue is still occurring on LibreOffice 5.3.0.3 (x64). I have attached the following example to demonstrate the bug; libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods.

To Replicate;
1. Ensure that libreOffice Macro security is set to Low/Medium (libreOffice - Tools - options - security - Macro security - Medium)
2. Open document (e.g. libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods) - select enable Macros

Note that libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods is a very simple example. In cell A1 it contains the text "Some Text". In Cell B1 it contains the forumla =GETCELL("Sheet1", "A1"). In LibreOffice - Tools - macros - organise macros - libreoffice basic - libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods - [myCalcFunctions] - Module1 it contains the following code*;
	REM  *****  BASIC  *****
	Sub Main
	End Sub
	Function GETCELL(sheetName As String, cellReference As String)
		oSheet = ThisComponent.Sheets.getByName(sheetName)
		oCell = oSheet.getCellRangeByName(cellReference).String
		'Msgbox(oCell)
		GETCELL = oCell
	End Function

Expected Behaviour;
Libreoffice executes user defined macro functions upon document load (cell B1 should read "Some Text").

Actual Behaviour;
Libreoffice doesn't execute user defined macro functions upon document load (all cell formulae containing references to these functions are evaluated as "#NAME?". Cell B1 therefore displays "#NAME?")

Workaround (to reload macros for cells);
1. Open document (e.g. libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods) - select enable Macros
2. LibreOffice - Tools - macros - organise macros - libreoffice basic - libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods - [myCalcFunctions] - Module1 - Edit (open the editor)
{or LibreOffice - Tools - macros - edit macros - libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods - [myCalcFunctions] - Module1}
3. edit cell - space - backspace - enter (force LibreOffice to re-evaluate the cell formula).

Non-workaround;
A previous workaround suggested in this bug report thread was to move the macro function definition from 'libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods - [myCalcFunctions] - Module1' to 'My Macros & Dialogs - Standard - Module1'. This however is a non-solution, as standard functions may be called before the document's sheets are loaded, depending upon how the document has been saved (NB there appears to be another bug relating to inconsistent saving of a document as to whether the standard macros will be executed before or after its sheets are loaded). I have provided an alternate example to demonstrate this bug. To replicate;
1. Open libreOffice Calc
2. LibreOffice - Tools - macros - organise macros - libreoffice basic - My Macros & Dialogs - Standard - Module1 - Edit (add the code above*)
3. Close LibreOffice
4. Open libreOfficeBug-macroFunctionsNotEvaluatedUponLoad-withoutEmbeddedMacro.ods - select enable Macros
5. Note that the following error is thrown; "Basic Runtime error. Property or method not found: Sheets."
Comment 12 Richard Bruce Baxter 2017-02-21 01:52:42 UTC
Created attachment 131374 [details]
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods
Comment 13 Richard Bruce Baxter 2017-02-21 01:53:43 UTC
Created attachment 131375 [details]
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad-withoutEmbeddedMacro.ods
Comment 14 fcyrilf 2017-05-16 09:51:04 UTC
Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again for the session.

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.
Comment 15 fcyrilf 2017-05-16 09:52:24 UTC
Same bug here.
I've tested it with my file : I've doing a VB macro which I've call in a formula and when I save and reopen the .ods document, the formula isn't recognized anymore. The only work-around I've found is to edit the macro (for example add a space or remove a space) and it work again for the session.

It's very annoying to do that every time i re-open my documents with macro...

LibreOffice tested : 5.2.6, 5.2.7 and 5.3.2.
Comment 16 fcyrilf 2017-07-11 22:43:36 UTC
Hi,

The bug's always here : Calc LibreOffice 5.3.3 and 5.3.4
Comment 17 fcyrilf 2017-10-16 14:29:15 UTC
Hi,

Always the same bug in Version 5.3.6 and 5.4.2

Will someone manage this bug someday? or give us a solution?
Comment 18 fcyrilf 2017-10-16 14:52:52 UTC
Created attachment 137014 [details]
Example of my ods file

try to open this ods file, active macro and select cell "F3" push F9 KEY nothing happen...
Then go to Macros -> edit macro then go to the function CalcColor add a space at the end for example, save it and go back to the workbook...
Now select cell "F3" push F9 KEY and it work!
Comment 19 Dietmar 2018-01-24 08:55:02 UTC
Bug still present in Version: 5.4.3.2
Build-ID: 92a7159f7e4af62137622921e809f8546db437e5
Comment 20 John Russell 2018-02-28 11:22:08 UTC
Bug still present in 5.4.5.1
Comment 21 fcyrilf 2018-02-28 15:17:03 UTC
Hi,
Yes I'm with the 5.4.5.1 version and whereas there is still the bug, I've found another solution :

In the macro editor just write "function" instead of "Function" (without uppercase at start and end of function), refresh the cells with formula (with the F9 key) and just add a space or something else in your calc document to force save it before close it...
Re-open and... magic it work! (for me).

So, to resume, it's just a bug with the uppercase of the word "Function"!?
Comment 22 fcyrilf 2018-03-01 23:51:01 UTC
I'm Wrong in my previous comment sorry.

I just believe that the bug was only the upper case but I had deactivated the automatic update of calculation, and it make me believe that the macro was executed on start because the column were there is my calculation was like it was before I close the file..... but in fact... so forget the thing with the uppercase...Sorry ... I'm so tired of this bug....
Comment 23 QA Administrators 2019-03-02 03:51:39 UTC Comment hidden (obsolete)
Comment 24 b. 2020-01-13 21:17:57 UTC
still a bug with: 

Version: 6.2.8.2 (x64)
Build ID: f82ddfca21ebc1e222a662a32b25c0c9d20169ee
CPU threads: 8; OS: Windows 6.1; UI render: default; VCL: win; 
Locale: de-DE (de_DE); UI-Language: en-US
Calc: 

sample from comment #11 works fine, 

original complaint with the sample 'Work' with only one sheet for oct.: cell using a macro function! (col. F) not autocalculated. reload, sometimes F9 while in that cell, and crtl-shift-F9 work to recalculate, 

there are some complaints about similar fails: 40203, 43003, 54854, 55774, 78929, 79770, 82610, 129896 ... 

6.4.0.1 does react on change of cell, not on change of color, maybe the same with 6.2.8.2, not rechecked,  

reg. 

b.
Comment 25 b. 2020-01-19 10:34:17 UTC
Created attachment 157251 [details]
function_not_evaluated_on_change_in_reference


'macro function not auto-recalculated on changes in referenced cell', 

while testing old bugs i came across that: 

the function from comment #11 

Function GETCELL(sheetName As String, cellReference As String)
   oSheet = ThisComponent.Sheets.getByName(sheetName)
   oCell = oSheet.getCellRangeByName(cellReference).String
   'Msgbox(oCell)
   GETCELL = oCell
End Function

in ver. 6.4.0.1 is calculated when the file is loaded, but later it is excluded from 'autocalculate'. A recalculation works with F9 with focus in the cell, or ctrl-shift-F9, or save-reload, but not regular on changes in the referenced cell. 

steps to reproduce: 

1. load attached sheet, allow macros, 
2. change content of A1, 
3. observe B1 not changed, 
4a. click on B1, then press F9, or 
4b. hard recalc (ctrl-shift-F9), 
5. observe B1 changed, 
6. repeat from 2., still no autocalculation, 

I think this violates the statement for autocalculate in: 

https://help.libreoffice.org/6.3/en-US/text/scalc/01/06070000.html?DbPAR=CALC#bm_id3145673

"Automatically recalculates all formulas in the document.

All cells are recalculated after a sheet cell has been modified. Any charts in the sheet will also be refreshed."

Could someone please check: 

- if i've made any mistakes, 

- whether the behaviour is 'normal' or an error, 

- if i should file a new bug for this or if the comment here is enough, 

In my opinion the error is quite important because it hinders any analysis or corrections for plenty other 'macro-function related' bugs. 

(pls. don't ask, yes! 'autocalculate' was activated at the test)

reg. 

b.
Comment 26 Eike Rathke 2020-01-20 13:11:33 UTC
This

  GETCELL(sheetName As String, cellReference As String)

gets passed some strings, if the call isn't like GETCELL(A1;A2) but literal GETCELL("Sheet1";"A1") or some such then there is no cell reference that would be added to dependencies to trigger a recalculation. But even if it was GETCELL(A1;A2) then the resulting "reference" used internally by the macro would not lead to some recalculation of the macro if only that "referenced" cell changed as again it is not part of the dependency tree, automatic recalculation would happen only if A1 or A2 were changed.


> "Automatically recalculates all formulas in the document.
> 
> All cells are recalculated after a sheet cell has been modified. Any charts in > the sheet will also be refreshed."
That description is misleading. AutoCalculate does not recalculate *all* formulas, but only so-called dirty cells, those that are affected by changes in cells they depend on.

The behaviour described in comment 25 is not a bug and not even related to the original problem.
Comment 27 b. 2020-01-20 17:47:29 UTC
hi, 

@Eike: 

thanks, that explains a lot... 

> "That description is misleading. AutoCalculate does not recalculate *all* formulas, but only so-called dirty cells, those that are affected by changes in cells they depend on."

i'd suggest to add something like: 

'autocalculate doesn't recalculate all cells but only those where it can see / analyse the dependencies. some special constructions block that, thus to get a fully calculated sheet use ctrl-shift-F9 (hard recalc).' 

or similar to the manual / help, it should propose something that would help in all situations not being a bug. 
 
shall i file a separate request for that? 

> "The behaviour described in comment 25 is not a bug and not even related to the original problem."

one might argue about it having the same root as, being the source of, related to, or a side effect of the OP problem, or vice versa, but that's not what i'm going for. i'm seeking for clarity, thus if someone solves the original bug, and if the behaviour of c#25 stays alive but is mentioned in the help / manual, it's solved for me. 

it worries me that the 'non-auto-calculation' hampers the analysis / processing of other bugs where the sheet authors and users cannot explain the 'non-auto-calculation' caused by such constructs, and the follow-up 'non-auto-calculation' of cells dependent on these non-calculated. 

maybe some old bugs can be closed as they are based on a misunderstanding of the power of autocalculate and get a plausible explanation by your comment? 

reg. 

b.