|
Apache OpenOffice (AOO) Bugzilla – Full Text Issue Listing |
| Summary: | Error 522 only after a save or autosave. Perfect otherwise. | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|
| Product: | Calc | Reporter: | billgradwohl <bill-list> | ||||||||
| Component: | save-export | Assignee: | AOO issues mailing list <issues> | ||||||||
| Status: | CONFIRMED --- | QA Contact: | |||||||||
| Severity: | Major | ||||||||||
| Priority: | P3 | CC: | elish, issues, kyoshida, rainerbielefeld_ooo_qa, rb.henschel | ||||||||
| Version: | 3.3.0 or older (OOo) | Keywords: | needmoreinfo | ||||||||
| Target Milestone: | --- | ||||||||||
| Hardware: | Other | ||||||||||
| OS: | Linux, all | ||||||||||
| Issue Type: | DEFECT | Latest Confirmation in: | --- | ||||||||
| Developer Difficulty: | --- | ||||||||||
| Attachments: |
|
||||||||||
|
Description
billgradwohl
2009-03-17 01:20:06 UTC
dtardon->billgradwohl: Could you attach the problematic file? I can send you the spreadsheet (about 500K, 38 sheets) and all the passwords in it via your personal email, but as it contains financial info I don't want to post that. Also, This has to do with another issue I've noticed in the last several releases. If a cell is not visible on the screen at the moment, it does not get calculated. If you scroll the screen to make the cell appear it does indeed have the correct answer, but while it is not visible it has the answer from the previous time it was visible. This is patently absurd. I found the following which addresses the issue tangentially:"Calc: working with elements outside the visible area" on the ux-discuss site. I know this is the case because my macros read from cells that may or may not be visible depending on how the user currently has his display of the sheet set up. My macros do the same thing every time but depending on if the user has a cell visible or not creates different and sometimes incorrect output. Example - Cells A1 thru O42 are visible on the screen. Cell A50 contains a sum for A1 thru A49. When the user enters data in any visible A* cell, the total is NOT updated in A50. If a macro is executed to list what is in A50, it will not contain the correct information. If the user scrolls the screen down to make A50 visible, it will have the correct total. Executing the same macro now produces the correct answer. My macros read the contents of cells all over the place and not having a cell calc'd just because it doesn't happen to be visible to the user at the time makes no sense at all. The spreadsheet will produce different answers depending solely on how the user has his viewing screen set up at the time. That's not a bug, but a major design flaw that renders the app unreliable in the extreme. My spreadsheet is a Point of Sale system I wrote for my wife's restaurant. We were giving meals away because the totals weren't correct simply due to how my wife happened to have the viewing screen set up. If she scrolled down to where the meal totals were, and then printed an invoice all was well. If she didn't "see" the total and simply hit the invoice button, the invoice would be short. I wanted to add this real world comment to show that not calcing cells that just happen to be invisible at the moment is blatantly wrong, as giving away meals solely because of how the screen is set up is wrong. Forcing a recalc of the entire spreadsheet is NOT the answer as that slows things down too much and OO Calc is already pretty slow. The correct thing to do is to follow the chain of dependant cells and update all of them regardless of their current visibility. Created attachment 61643 [details]
Sample spreadsheet that displays repeatable errors under certain circumstances
I attached a stripped down version of my spreadsheet that still displays the
same faults to the email I received a month ago. I guess I was supposed to add
it here.
A stripped down version of my spreadsheet is attached.
The following will demonstrate 2 separate errors. I feel that they are
linked however, because they appear only when cells are hidden from
view. That's the common element.
Place the sheet where macro execution is enabled as it relied on macros to
function. Any time you want to start over again, just save the sheet and the 522
error will return.
I removed huge portions of this spreadsheet so you have less to consider
when analysing it. I didn't remove lots of the macro code, but because
about 30 sheets are missing, please don't hit any buttons present on the
sheets. Normally, all the sheets are protected during macro execution,
but to demonstrate these errors protection makes no difference, so I
unprotected the Now sheet.
Hit enter on the “Ready†message.
Set your autosave off so it won't interfere.
Go to the Now sheet
Fix your screen so that cell T47 is NOT visible.
Go to B27
Enter 1
You now have a 522 error in U41 and a few other places.
Go to B27
Enter 2
The error is gone.
Make cell T47 visible by scrolling to see it.
Make cell T47 invisible by scrolling to hide it.
Execute macro Standard/tableReceipt
Go to sheet Receipt and look at it. The total is correct.
Go to the Now sheet
Go to B27
Enter 1
No more 522's till the next save.
Execute macro Standard/tableReceipt
Go to sheet Receipt and look at it. The total is wrong.
Go to the Now sheet
Make cell T47 visible by scrolling to see it.
Make cell T47 invisible by scrolling to hide it.
Execute macro Standard/tableReceipt
Go to sheet Receipt and look at it. The total is now correct.
Go to the Now sheet
Highlight columns G and T and right click / show the columns in between.
Save the spreadsheet to start over again, but this time there will be no
522 errors because the hidden columns are now visible.
Fix your screen so that cell T47 is NOT visible.
Go to B27
Enter 1
No more 522's under any circumstances. By un-hiding the formerly hidden
columns, the 522 error is permanently gone. However, the other error
still persists.
Make cell T47 visible by scrolling to see it.
Make cell T47 invisible by scrolling to hide it.
Execute macro Standard/tableReceipt
Go to sheet Receipt and look at it. The total is correct.
Go to the Now sheet
Go to B27
Enter 2
Execute macro Standard/tableReceipt
Go to sheet Receipt and look at it. The total is NOT correct
Because T47 is not visible, it contains a bogus total.
The 522 error can be made to go away via ctrl-shift-F9 as well, but just
an F9 won't do it.
If you read the code for the tableReceipt macro and what it in turn
calls, you'll note that only the following line is worth your concern:
grandTotal=sheet.getCellRangeByName("Table.tabLemps").getValue()
When T47 (aka tabLemps) is not visible it contains what it did the last
time it was visible, so the getValue returns an incorrect value.
Obviously, cells that are not visible are not getting updated. Therefore
macros that interrogate cells are given wrong answers.
Regardless of visibility, all the cells within the spreadsheet MUST get
updated or else the spreadsheet will give different answers depending on
what cells are visible at any particular time and that means the
spreadsheet is absolutely unreliable.
I cannot confirm the Err 522 problem. I use DEV300m49 on WinXP. You are setting values by macro. You should ask a macro expert, in which cases you need to update the spreadsheet yourself. Have you tried to do a hard recalculate at first action before calculating the receipt? Testing under a Linux O/S might be the difference. I have several Linux boxes and they all do it on the same spreadsheet. Can someone with a Linux distro look at this please. Using a Windows box isn't relevant. Also I'm attaching some screen shots that are related to this issue that I believe show that there is something wrong with the internal tables kept by calc. When this sheet is opened, I make an entry in a cell and then (amongst others) cells AN41 and AM47 get 522's. If I re key the cell with a different value the 522 disappears. The first screen shot shows a trace while I'm on cell AN41. Note that the output is staggered down by 1 cell. The second screen shot shows the same thing - I'm on AM47 and as for a trace - output is staggered by 1 cell. On both shots, you can clearly see the formulas involved. AM47 in particular sums the cells immediately above it. There's no possibility of a circular reference. And if I re key the cell that caused this (happens to be C2) the error clears up. The error reappears after a save (auto or otherwise). There is nothing wrong with the sheet, as its the same sheet that was used under a previous release that didn't display these anomalies. Created attachment 63175 [details]
First screen shot
Created attachment 63176 [details]
Second screen shot
This Issue requires more information ('needmoreinfo'), but has not been updated
within the last year. Please provide feedback as requested and re-test with the the latest version of OpenOffice - the problem(s) may already be addressed.
You can download Apache OpenOffice 3.4.1 from http://www.openoffice.org/download
Please report back the outcome of your testing, so this Issue may be closed or
progressed as necessary - otherwise the issue may be Resolved as Invalid in the
future.
Confirmed, following steps in comment 4 that total is wrong when T47 is not visible, no 522 errors. AOO410m1(Build:9750) - Rev. 1539999 2013-11-09_04:08:11 - Rev. 1540252 Debian Correct Version: has been observed first with OOo 1.0.3 |