Apache OpenOffice (AOO) Bugzilla – Issue 19664
Custom functions are recognized as names of cell ranges when opening Excel documents
Last modified: 2013-08-07 15:15:02 UTC
For a long time I used an Excel application where cell values were calculated with some custom functions written in Visual Basic. Unfortunately, all these functions have names consisting from 2 letters, e.g. CY(), DY(), RY() etc. However, in MS Excel these names were never confused with column markers, because they are always followed by their arguments in parentheses. So my cell formulas could look as follows: =5*ROUND(1000*(NY(D237))*C87*1,25*(CY(D222));-1) Now, when I open such a document in OOo, my function names are recognized as column markers, even if the functions itself are available to OOo (there is no problem to convert them, since they contain only arithmetical calculations and no references to application objects). Moreover, the parentheses surrounding their arguments are automatically removed, and a semicolon is inserted instead. So the function above looks in OOo as follows: =5*ROUND(1000*(NY;D237)*C87*1,25*(CY;D222);-1) Even if I restore the parentheses manually, my formulas still don't work, and I always got a 511 error instead of correct calculations. The only way to fix the problem is changing names for all my functions, but I have a lot of spreadsheet documents which should be converted and a lot of cells using these functions in each of them. And, since my naming conventions never caused problems in MS Excel, I think it should be possible to make OOo Calc correctly handle such functions for better compatibility with MS Office.
Hi, I've checked it using OOo1.1rc4 on Linux and Windows and can not reproduce the problem you are facing. Please use the latest build which is currently OOo1.1RC4 to check. For know closed worksforme Frank
closed worksforme
Now I see I've incorrectly described the problem, so nothing strange if you couldn't reproduce it. The problem looks now as follows. The names of my functions are *not* confused with column markers. Instead, they are recognized as names of cell areas. After opening an Excel document, I can go to Insert->Names->Define and see all these names (DY, CY, RY...) in the dialog box. I am sure the initial Excel document didn't include any cell areas with such names. This means OOo export filters simply inserts them instead of function names it can't recognize. So I have to manually remove all such autogenerated entries from the "Insert Name" dialog in order to be able to use my custom functions. However, this doesn't make my imported document more correct, because I still have to insert omitted parentheses around each function's arguments. Of course I understand that all custom functions are very probably "invalid" at the time the Excel document is imported, because our Visual Basic code is not available to OOo. However it is much easier to rewrite a macro code for OOo *once* than to restore parentheses in hundreds of cells. So I think the import filter for Excel files should be corrected at this point.
Hi, could you attach a document showing this behaviour ? It would make it easier to reproduce. If the data is confidential, you can change them to values producing non correct stuff. Thanks for your help. Frank
Created attachment 9451 [details] MS Excel document containing some custom functions
No confidential information, just a testcase document. To reproduce the problem: -- open custfuncs.xls in MS Excel to ensure everything is correct. The cells B1 and C1 should contain the following formulas: =AA(A1) =AAA(A1) The functions itself are defined in a VBA module inluded into the same document. Of course they are very simple and should work both in MS Excel and OOo without any modifications. -- Now open the file in OOo and take a look at the same cells. They contain the following pseudo-formulas: =(AA;A1) =(AAA;A1) -- Go to Insert->Names->Insert. The "Insert name" list box contains two entries: AA and AAA.
Hi, as you said removing rems once and add some dim's is easier The parantheses are set by the excel import filter and this one will be improved. As this is not an easy task, don't expect it before OOo2.0. Also this is more an enhancement then a defect. So I changed the Issue type and re-assign it to the appropriate developer. Thanks for your help. Frank
Hi Daniel, as discussed this one is yours. Frank
accepted
*** Issue 21045 has been marked as a duplicate of this issue. ***
fixed
wrong issue -> reopened
started again
OC: Due to high workload this issue is retargeted to office.later
John will take care about this
mark started
patch for issue 26402 checked in today for cws/dr19 will fix this issue.
reopen to reassign
assign to qa
reset to fixed
reopened because fixed but failed
Hi John, this fix has broken the named ranges in functions. If I import a document with a named range in a function I'll get ERR:508. I've attached a simple bugdoc which show this fault.
Created attachment 16406 [details] Bugdoc with broken named range
adjusting target milestone to OOo2.0
Created attachment 16496 [details] patch to fix this and issue 25883
checked in patch today to cws/dr19.
verified in internal build cws_dr19
reopened
back to QA
reset verification status
closewd because fix available in current master