[Solved] Custom OOBasic functions - bug or feature?

[zzarko]

I had a problem using custom functions written in OOBasic in Calc documents (OOo 2.3.0). The problem can be reproduced like this (assuming that all operations are on the same sheet, for example Sheeet1):
1. Create new spreadsheet document, and write new OObasic function (any function will do, this is just an example):

Code: Select all

Function testfunc (x As Integer) As Integer
	testfunc = 2 * x
End Function

2. In any cell write, for example:

Code: Select all

=testfunc(4)

The result value for that cell should be 8.
3. In any empty cell write text

Code: Select all

testfunc

4. Now, if you try to write in any cell something like step 2, for example:

Code: Select all

=testfunc(3)

the written function becomes inaccesible and the result value in that cell becomes "#REF!". Calc would not let you to write text "=testfunc(3)", as it's automatically replaced with

Code: Select all

='testfunc'(3)

Previous cell from step 2 is still working (calculating) as before step 3, and any copy/paste of that cell still works, although if you try to edit it, it will stop to work and it't value will become "#REF!".

Is this bug or feature (expected behaviour)? It took me a few hours to figure out why several of my functions were constantly "ignored" by Calc. If this is a feature, can it be turned off? If this is a bug, I guess I should report it to OOo team.

[TerryE]

Thanks this is a well explained post and I can understand your frustration. I will be updating the Survival Guide for the forum with this general help but in cases like this the best thing to do is to search the OpenOffice.org qa database through the Issue tracking query.

This is what I did and entered "spreadsheet function" into the Summary: field with the "all words/strings", and then submitted. This came back with 10 hits: 6 Enhancement Requests, 3 Defects and 1 Feature. The first of these defects that has already been logged is the very symptom that you have found yourself: Issue 62081— Userdefined function doesn't work anymore after it appears as pure text in the spreadsheet. "JA: I can confirm that this is a valid problem. The automagic range detection doesn't take into account that there might be a BASIC function having the same identifier. In this case the function name is converted into a string."

So the good news is that this has already been identified. The bad news is that they're planning to fix in sometime in 3.x.
A sensible workaround for avoiding this issue is to adopt a naming convention for such functions, e.g. prefix all such functions with xBasicFnc so that you won't unknowingly duplicate the names.

[B Marcelly]

Hi,
I have a different opinion than TerryE

Yes it's a feature

Menu Tools > Options > OpenOffice.org Calc > Calculate
un-check : Automatically find column and row labels

Don't ask me why they have included this strange feature.

This option is set for each document
If you want it unchecked for every newly created document you have to edit your default template for Calc, uncheck the option and save.
If you don't know how to create a template, start another thread

______
Bernard

[TerryE]

I am not sure that this is really a disagrement. As the Issue says, this is a bug in the "automagic range detection" feature within calc. What Bernard points out is that this bug can therefore be disabled by disabling this optional feature through the Tools->Options->Calc->Calculate dialogue. According to the Help on this function its purpose is defined as follows: Automatically find column and row labels specifies that you can use the text in any cell as a label for the column below the text or the row to the right of the text. The text must consist of at least one word and must not contain any operators. This would seem to imply that we are talking about labels in Column A or Row 1. However if you have a Name predefined through Insert->Names->Define then this takes precedence, so you would expect other predefined names (such as Function names) to take precedence.

This feature seems to have been cloned from the Excel "Accept labels in formulas" (in which Function names do take precedence, by the way.)

Though to be fair the example given makes clear that this labelling algorithm searches the whole sheet. Now what I find unacceptable with this feature is that its functionality seems pretty random. If you have a name with another name below then it will look to the right, otherwise below. It also only uses the consecutive sequence of none-blank fields.

So Bernard if right. Turn it off, and define your own names explicitly if you need them. This will avoid the bug manifesting itself.
And I am right. It is a bug.

[zzarko]

Thank you all for your replies. I'm sorry I didn't search OOo issues, but I didn't know what keywords to use. All your answers are a great help for me, as my problem is now solved. I already included prefixes for my functions as a solution, but it's nice to know that this behaviour can be turned off.