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.

(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.

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.

(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.

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 ...

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.

Highest is reserved for other purposes so just bumping this down to high. Thanks Bernhard

(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.

> 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.

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."

Created attachment 131374 [details]
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad.ods

Created attachment 131375 [details]
libreOfficeBug-macroFunctionsNotEvaluatedUponLoad-withoutEmbeddedMacro.ods

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.

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.

Hi,

The bug's always here : Calc LibreOffice 5.3.3 and 5.3.4

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?

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!

Bug still present in Version: 5.4.3.2
Build-ID: 92a7159f7e4af62137622921e809f8546db437e5

Bug still present in 5.4.5.1

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"!?

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....

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.

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.

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.

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.

(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.

FTR: only libraries named "Standard" and "VBAProject" are loaded automatically in BasicManager::SetLibraryContainerInfo (basic/source/basmgr/basmgr.cxx). This method is called both for application BASIC, and for document BASIC, so only functions available in these libraries are available for execution until other libraries are loaded. Since function resolution is performed before the security prompt, it's impossible to use any Basic code to load other libraries here (the resolution of all formulas happens in ScCompiler::ParseMacro during the load, then macro execution is performed in ScInterpreter::ScMacro, at which point the prompt is shown, and only then programmatic loading of other libraries could happen, but the compiler has already rejected the names from not-yet-loaded libraries as unknown at this point).

Possibly this should be a documentation issue, or *possibly* ScCompiler::ParseMacro could try loading other libraries after it failed to find the name ... but I don't know how much impact could that be.