[Solved] Custom OOBasic functions - bug or feature?

Discuss the spreadsheet application

[Solved] Custom OOBasic functions - bug or feature?

Postby zzarko » Sat Dec 22, 2007 5:50 pm

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   Expand viewCollapse view
Function testfunc (x As Integer) As Integer
   testfunc = 2 * x
End Function

2. In any cell write, for example:
Code: Select all   Expand viewCollapse view
=testfunc(4)

The result value for that cell should be 8.
3. In any empty cell write text
Code: Select all   Expand viewCollapse view
testfunc

4. Now, if you try to write in any cell something like step 2, for example:
Code: Select all   Expand viewCollapse view
=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   Expand viewCollapse view
='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.
Last edited by Hagar Delest on Tue Jun 10, 2008 2:23 pm, edited 2 times in total.
Reason: tagged the thread as solved.
zzarko
 
Posts: 6
Joined: Sat Dec 22, 2007 5:11 pm

Re: Custom OOBasic functions - bug or feature?

Postby TerryE » Sat Dec 22, 2007 8:00 pm

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.
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Custom OOBasic functions - bug or feature?

Postby B Marcelly » Sat Dec 22, 2007 8:02 pm

Hi,
I have a different opinion than TerryE ;)

Yes it's a feature :evil:

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
B Marcelly
Volunteer
 
Posts: 1160
Joined: Mon Oct 08, 2007 1:26 am
Location: France, Paris area

Re: Custom OOBasic functions - bug or feature?

Postby TerryE » Sun Dec 23, 2007 3:35 am

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. :-)
Ubuntu 11.04-x64 + LibreOffice 3 and MS free except the boss's Notebook which runs XP + OOo 3.3.
TerryE
Volunteer
 
Posts: 1402
Joined: Sat Oct 06, 2007 10:13 pm
Location: UK

Re: Custom OOBasic functions - bug or feature?

Postby zzarko » Sun Dec 23, 2007 1:29 pm

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.
zzarko
 
Posts: 6
Joined: Sat Dec 22, 2007 5:11 pm


Return to Calc

Who is online

Users browsing this forum: jaragon and 31 guests