LO Calc crashes when calling a macro

English
, , ,
#1

Solving one problem on another forum, I must admit, I lost my temper. The number of errors in LO Calc is simply off scale, the system is extremely unstable compared to Excel. Even the End statement doesn’t help. All bugs are somewhere deep inside. Or am I doing something wrong?
LO Calc API is not used in macros. Differences from Excel can only be in the indices of the boundaries of the arrays passed to the function. Not tested.
The app crashes from time to time, but not always when I try to edit data (player points).

Whoever has a desire, dig into this example:

players-and-points (2) (1).ods (32.9 KB)

Description:
I find the next winner of the week (GetNextWinner) who has the best points scored (SortDataArrayDesc) and he - the player - wins for the first time (Not WinnerExists, skips the winners even if they have more points, it doesn’t matter).

Function GetNextWinner(Players, Points, Winners)
Function SortDataArrayDesc(DataArray()) 'simple bubble sort is used
Function WinnerExists(Winners, Player) As Boolean

In my example, there are 15 players (“Team1” sheet), and in the first 15 games the players are not repeated. Then the player with the maximum result is simply taken. However, according to the author’s condition, the number of players (40 or 15-20) is equal to or greater than the number of games (15-20).
So in the example, look at the first 15 games where the winners are unique. If the number of points is the same for several players, then the first unique player is displayed in the order of their sequence.
Of course, bubble sort can be replaced with more efficient sort. It is not important here.
There are several teams. In the example, there are 2 of them (Team 1 & Team 2).
All formulas on all sheets are the same and can be freely transferred from sheet to sheet. However, they all belong to their ranges, which must be set correctly. For example, I copied the Player 12 formula from the first sheet and pasted it in place of Player 21 on the second sheet. Everything works. Convenient.

Note: @Lupp & @Villeroy took an active part in this task. But everything is not easy.

Cross reference

Version: 7.2.0.4 / LibreOffice Community Build ID: ce769e3009755dcf0082844e386f5dca4c8ecb2f CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk3 Locale: ru-RU (ru_RU.UTF-8); UI: en-US Calc: threaded

UPDATED:
See the “players-and-points (3).ods” file attached below. Works fine, but w/o conditional formatting.
UPDATED:
The initial file “players-and-points (2) (1).ods” was updated. The Iterative References option was unchecked. Fixed one reference to the range.

Highlighting a named range does not work correctly
#2

I didn’t yet study your sugessted solutions posted in and attached to some of your posts to the mentioned thread [Solved] Index/match/max (View topic) • Apache OpenOffice Community Forum.
Before I start to use time for an investigation, I would like to know if the attachment to you question here is supposed to be a “final-so-far” version or if you are still working on the task in steps.

#3

No. I ended my research at a dead end.
Ctrl+Shift+F9 updates all formulas on all sheets quickly enough and without errors. But editing player points does work from time to time, but more often it doesn’t.
UPDATED:
Maybe @erAck or @mikekaganski or other specialists can help the author here (I’m not the problem author)?

Function GetNextWinner(Players, Points, Winners)
	On Error GoTo Failed
	<...>
	Exit Function

Failed:
	GetNextWinner = "Error": End
End Function

And I can’t understand why the error handler doen’t work. Calc crashes somewhere before… Internal error.
I have been working with LO Calc for about 2 years. It is a matter of trust in the product. I didn’t have any particular problems before, but this case is fundamental.

#4

Hello @eeigor! Problems may be related to the “Winners” name, which refers (in reverse cell order) to a range that has merged cells.

#5

@sokol92, however, there are no errors when opening the file. Even when editing cells above merged cells Calc crashes. It looks like the problem is not with Calc, but with StarBasic. Merged cells are considered empty (Variant/Empty).

UPDATED:
Worked better with one sheet. Okay, I removed the second sheet. The “Name Box” still contains the range names of the deleted sheet, but the “Manage Names” dialog box has been updated. Range names mismatch.
It started to work a little better, but not much. Moreover, you can make the same correction, and Calc crashes, then no.

#6

You can check.

  • Remove formulas containing UDF functions from cells
  • Delete rows containing merged cells
  • Write down the formulas (one at a time).

Are there crashes?

#7

@sokol92, yes, it crashes. I removed the second sheet, removed the rows with holidays and merged cells, respectively. There is no stability. I removed all range names. Did not help.
Judge for yourself.
players-and-points (castrated).ods (22.5 KB)
But when you open the file or recalculate all the formulas, everything is fine.
If someone rewrote this in Python, then my guess about the failure of StarBasic would become more solid.
Of course, I have no complaints, and the task is not mine. But I’m disappointed…

#8

Let’s keep the example down to a minimum.

  • Delete rows 5-17 and 2 in the last document.
  • Remove conditional formatting.

What steps do you need to take to get a crash? I am unable to get it.
Maybe you should reboot your system?

#9

Removed. The initial file works correctly.
Conditional formatting was suggested by @Lupp and it came in handy. It’s a pity to lose this, but perhaps the author will be satisfied. I wrote to him.

Row 2 cannot be deleted because cell $B$2 is fixed.
Is there something wrong with the conditional formatting? But I don’t know how to describe this bug, because the example looks difficult to demonstrate. Help.

#10

I can’t yet, I have no crashes. :slightly_smiling_face:

#11

players-and-points (3).ods (33.9 KB)
The initial file w/o conditional formatting works fine. I use Linux.
And then this error is not StarBasic one, but Calc.

#12

Try to change condition in conditional rule to formula D3=$A3.

#13

Did not help. I left one coherent range with the conditional formatting applied and it crashes Calc and the other cells are editable well.

#18

I only did very limited testing with (2) and didn’t look yet into (3) yet.
There were no crashes, but the solution is not correct (disambiguation, exclusion of previos winners).

On the other hand I had lots of crashes using Basic / IDE with versions of the 7.x series (under Win 10).

#19

@Lupp, previous winners are eliminated if the number of players is greater than or equal to the number of games played. I made a reservation.
The author was satisfied with this decision. The problem was with the crash of the application.
Perhaps this bug manifested itself in Linux. @sokol92 also had no errors (Windows?).

Corrected one reference to a range in the original file.

#20

That makes the question moot though because the crash doesn’t occur any more with the now current sample file, apparently not even if iterations are enabled again. Anyway, bug fix under way with https://gerrit.libreoffice.org/c/core/+/122949

#21

@erAck, this file from another forum causes the error in question. You can test it in a new build. Enter 99 in cell D3. Crash!

ex-player-values (2).ods (29.2 KB)

#14

The document unnecessarily has iterations enabled, there are no circular references. But somehow in this document it may lead to a crash deep in the iteration resolver, might be that view update kicks in while formula cells are recalculated and it tries to access a currently being recalculated cell and then triggers an iteration recursion.

However, disabling iterations under Tools → Options → Calc → Calculate, Iterative References, uncheck Iterations, a crash wasn’t reproducible anymore.

2 Likes
#15

@erAck, well, I knew who to turn to. Works. Great.
However, I do not know whether it is necessary to refine something in Calc in light of this. You know better.

#16

Yes of course it’s a bug.

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.