Major OpenOffice Bug: Rapid Execution of Macros

[Chasing Dreams]

Hello,
I believe I have found a major bug in OpenOffice. It is applicable to any file that enables the user to execute macros. Specifically, when you try to rapidly execute macros, they are not carried out correctly. If you run one macro first and then attempt to run a second macro before the first one has finished, you can corrupt the data you are working with. I have a spreadsheet that uses macros and buttons and this bug just renders the data inaccurate. I have done searches for my problem but have not found a similar situation.

I have created a demonstration Calc file with two macros to illustrate my point. You can retrieve the file from http://cid-c3d66c73859f43b5.skydrive.li ... f%20Macros

The demonstration provides a button for you to press that increments 101 numbers each time you press the button. If you press the button slowly, the macro executes until it is finished and correctly increments the 101 numbers. If you press the button rapidly, say 20 times as fast as you can click, the the sequence of numbers gets corrupted. You will get numbers that are out of order and of the wrong value in the sequence.

Below is the two macros the program uses. The second macro is just to set the numbers from 1 to 101.

Code: Select all

Sub Increase_Numbers
REM *** Increments each of the 101 numbers in the first column of the spreadsheet by 1 ***

oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet)

i = 100
m = 0

For j = 0 to i
  k = oSheet.getCellByPosition(0, j).getValue()
  oSheet.getCellByPosition(0, j).setValue(k+1)

  REM *** Delay loop to simulate real world calculations ***
  For l = 0 to 1000
    m = m + l
  Next l
Next j

End Sub

Code: Select all

Sub Reset_Numbers
REM *** Fills the first column of the spreadsheet with ascending numbers from 1 to 101 ***

oDocument = ThisComponent
oSelectedCells = oDocument.CurrentSelection
oActiveCells = oSelectedCells.RangeAddress
oSheets = oDocument.Sheets
oSheet = oSheets.getByIndex(oActiveCells.Sheet)

i = 100

For j = 0 to i
  oSheet.getCellByPosition(0, j).setValue(j+1)
Next j

End Sub

Does anyone know how to make it so that the macros don't get interrupted in the middle of execution? Is there a fix for this?

[TerryE]

Sorry but I don't think this is a bug but a feature. Unlike the VBA engine within Excel, the Calc Basic engine is multi-threaded. You can quite validly have multiple macros running at the same time. Whether this is wise or not that is how the system is defined. Hence the two subroutines will beat against each other so in essence you have two statements in the first routine and one in the second which are accessing or setting the same memory:

Code: Select all

' in Routine 1
'=============
  k = oSheet.getCellByPosition(0, j).getValue()
  oSheet.getCellByPosition(0, j).setValue(k+1)
' in Routine 2
'=============
  oSheet.getCellByPosition(0, j).setValue(j+1)

This is just the same as if you were writing in separate threads in C++. Also not that this yielding is done in between Pcode statements and not at the Basic statement level so even if you were to rewrite the first two in the more readable format

Code: Select all

oSheet.getCellByPosition(0, j).Value = oSheet.getCellByPosition(0, j).Value + 1

there is no reason that this should be atomic

[Chasing Dreams]

Is there anyway to make it so that only one macro is allowed to run at a time?

Multi-threading macros can be good for highly complex spreadsheets but I wager that the majority of beginning/intermediate users are only programming with a single thread in mind. If you convert from Excel to Calc, then not only do you have to change the keywords and syntax of your code, you have to recreate algorithms and create data locks so that things don't get corrupted by concurrently running macros.

[Chasing Dreams]

Oh, because you mentioned multi-threaded macros. Can you link to an example of where someone has actually created something like this? I have never seen or need multi-threaded macros before.

[TerryE]

Oh, because you mentioned multi-threaded macros. Can you link to an example of where someone has actually created something like this? I have never seen or need multi-threaded macros before.

No I can't. I didn't that this makes sense; just that that's the way it was designed. Because you can be working on more than more than one document at one time, for example a Calc based cruncher running some horrible piece of macro code, and a writer document. Given that probably have a dual core PC, then you could literally have both running at the same time, and you would want this to occur.

One way of doing an interlock these two then you could use some form of mutex. The difficult thing here is guarantee atomicity. If you two macro sorts, say a background "cruncher", and form triggers, but you don't want the triggers to interfere with the background process, then you could use a public variable, say bgRtn1Running which Rtn1 can set on entry and clear on exit. Any triggers or other macros can then test for this and either about or wait.

[B Marcelly]

One way of doing an interlock these two then you could use some form of mutex. The difficult thing here is guarantee atomicity. If you two macro sorts, say a background "cruncher", and form triggers, but you don't want the triggers to interfere with the background process, then you could use a public variable, say bgRtn1Running which Rtn1 can set on entry and clear on exit. Any triggers or other macros can then test for this and either about or wait.

This solution can greatly reduce the risk of muti-execution and can be acceptable, but it is not really a mutex. Because a Basic execution can be interrupted by another Basic execution, as in the case of successive button clicks.

I propose to use the write exclusion on a local file. Run this code from a button, and click several times during execution:

Code: Select all

Sub ProtectedByFile()
Dim f1 As Long
f1 = FreeFile
On Error GoTo BusyFile
Open "C:\Docs OpenOffice\lock1234" For Output As #f1

Increase_Numbers
Close #f1
Exit Sub

BusyFile:
  Resume Aborting
Aborting:
  On Error Goto 0
End Sub

[TerryE]

This solution can greatly reduce the risk of muti-execution and can be acceptable, but it is not really a mutex.

Bernard is quite correct: it isn't, which is why I didn't say it was. The trick does work if you want an asymmetric system that is you my have a long running b/g process and you want to ensure that you don't accidentally fire a foreground trigger whilst its running.

Bernard's solution will work since Linux, Windows and Mac all open output files exclusively. However, file open is quite a slow operation and therefore if you are not careful and put such locks too low in your processing then you could slow your execution to a crawl. The calls to UNO are automic so it would in principle be able to add an extension which impliments a proper mutex.