Bug 70902

I can't insert a user defined function using LibreOffice 3.5.4.2 
Build ID: 350m1(Build:2).  The insert => Function window shows err:520 even after a reboot and starting a new Libre Office Calc spreadsheet.  

I have purged libreoffice and libreoffice calc, but the problem persists.  The error code is: "Internal syntax error, Compiler creates an unknown compiler code". 	

This problem started after initializing a variable in libreoffice Basic.  The line of code was: "pie = 3.14159".  I corrected the program line by changing it to" "let pie = 3.14159".  Everything was working fine and my user defined function could be inserted and worked until I saved the program and started the spreadsheet the next day.

I am using an AMD FX-6100 processor, Debian 7.0.0 with latest updates, and the XFCE user interface.  I have found a lot of Err: 502 error reports on the Internet but no mention or solution to my particular problem.  Any help would be greatly appreciated because I am a heavy user of functions and user defined function and this problem crippling my use of libreoffice.

Created attachment 88165 [details]
Can't insert used defined function into libreoffice Calc

I can't insert a user defined function using LibreOffice 3.5.4.2 
Build ID: 350m1(Build:2).  The insert => Function window shows err:520 even after a reboot and starting a new Libre Office Calc spreadsheet.  

I have purged libreoffice and libreoffice calc, but the problem persists.  The error code is: "Internal syntax error, Compiler creates an unknown compiler code". 	

This problem started after initializing a variable in libreoffice Basic.  The line of code was: "pie = 3.14159".  I corrected the program line by changing it to" "let pie = 3.14159".  Everything was working fine and my user defined function could be inserted and worked until I saved the program and started the spreadsheet the next day.

I am using an AMD FX-6100 processor, Debian 7.0.0 with latest updates, and the XFCE user interface.  I have found a lot of Err: 502 error reports on the Internet but no mention or solution to my particular problem.  Any help would be greatly appreciated because I am a heavy user of functions and user defined function and this problem crippling my use of libreoffice.

(In reply to comment #2)
> Created attachment 88165 [details]
> Can't insert user defined function into libreoffice Calc
> 
> I can't insert a user defined function using LibreOffice 3.5.4.2 
> Build ID: 350m1(Build:2).  The insert => Function window shows err:520 even
> after a reboot and starting a new Libre Office Calc spreadsheet.  
> 
> I have purged libreoffice and libreoffice calc, but the problem persists. 
> The error code is: "Internal syntax error, Compiler creates an unknown
> compiler code". 	
> 
> This problem started after initializing a variable in libreoffice Basic. 
> The line of code was: "pie = 3.14159".  I corrected the program line by
> changing it to" "let pie = 3.14159".  Everything was working fine and my
> user defined function could be inserted and worked until I saved the program
> and started the spreadsheet the next day.
> 
> I am using an AMD FX-6100 processor, Debian 7.0.0 with latest updates, and
> the XFCE user interface.  I have found a lot of Err: 502 error reports on
> the Internet but no mention or solution to my particular problem.  Any help
> would be greatly appreciated because I am a heavy user of functions and user
> defined function and this problem crippling my use of libreoffice.

(In reply to comment #2)
To eliminate any chance of corruption, I installed Debian 7.0.0 on a separate partition of a different computer and than ran Libre Office Calc before doing anything else.  After Libre Office opened, I clicked Insert (menu) ==> Function.
The Function window popped up set to the ABS function as is normal.  In the lower right box for Result: there is "Err:520".  In other words, some element of the problem I am having inserting User Defined functions seems to be there right out of the box with a fresh installion of Debian on the included Libre Office Calc program.

From "Let ..." I assume, you have used a Basic macro. Please attach a spreadsheet containing that macro or attach the source code of the macro.

(The Status should not be NEW, when nobody has confirmed your bug report.)

Created attachment 88420 [details]
Spreadsheet with macro Function to provide Err:520 problem reported.

Created attachment 88421 [details]
Screenshot #0 referenced in problem description

Created attachment 88422 [details]
Screenshot #1 referenced in problem description

Created attachment 88423 [details]
Screenshot #2 referenced in problem description

Created attachment 88424 [details]
Screenshot #3 referenced in problem description

Created attachment 88425 [details]
Screenshot #4 referenced in problem description

The Function that I am writing is too convoluted at this point, and causing too many problems and crashes, so I have repeated the problem using a simpler code example that computes the volume of a cylinder, and has the same Err:520 problems.  I wrote the Function first and then tried to use it in a simple spreadsheet named CylinderVolumeSpreadsheet.odt (attached).  Also attached are Screenshots that show some of the problems as follows:

Screenshot-00-CylVol.png - shows the "Err520" problem on a newly opened spreadsheet.  The only action after opening LibreOffice Calc was to click Insert (menu) => Function.

Screenshot-01-CylVol.png - shows a "Read Error", but if I click "OK", then the whole Calc program crashes (closes) unexpectedly.  I want to try changing "pi" to "pie", but can't do it with the crashes.

Screenshot-02-CylVol.png - Shows the Function Wizard window which does not work to insert my function, but in this case, Result field does not show "Err:520" despite the fact that there is a problem.

Screenshot-03-CylVol.png - I'm now deeper into the problem, still can't insert my function, but now the "Err:520" is showing in the Result field of the Function Wizard.  Incidently, trying to enter the function into cell C1 does not work and results in "#NAME" in the cell.  Calc has various responses when entering the function into a cell directly by typing it, but not of these are helpful in determining what or where the problem is. 

Screenshot-04-CylVol.png - See item 10. in the procudure that follows.

Here is a skeleton outline of the procedure I followed to write the macro and use it in a spreadsheet.

1.  Open LibreOffice Calc from the Debian Xfce GUI

2.  Click Tools => Macros => LibreOffice Basic => Edit

3.  Wrote the function:  (also shown by Screenshot-01-CylVol.png)
	Function CylVol(r, h)
	Let pi=3.14159
	CylVol = pi * r * r * h
	End Function

4.  Saved it.

5.  Click on spreadsheet to make it active and create a simple 3-column spreadsheet.  Saved it.

6.  Attempt to insert my function in cell C2 using the Function Wizard (Insert (menu) => Function)

7.  My User Defined function was not found in the Function Wizard list of functions, and had the Err:520

8.  Tried entering function in cell C2 as "=CylVol(A2, B2)", but got "#NAME"

9.  Save as  CylinderVolumeSpreadsheet.odt  This is the attachment.

10. Close Calc, Open Calc, open CylinderVolumeSpreadsheet.odt.  At this point there is beep caused by some error, the macro window editor pops ups, along with an error message.  When I try to close the error window to see the spreadsheet, then more beeps and the main spreadsheet closes.  Now I can't close the remaining macro editor window because it thinks it is  needed by the spreadsheet, but it has closed. I'm too far into various errors and inoperative windows now, and can't do much of anything else.  Nevertheless, the common denominators are the "Err:520" and not being able to insert my function since the errors messages and crashes make it impossible to do anything.

1 Your spreadsheet do not contains any macros.
2 You try to assign a value who is already defined (PI)
3 Basic functions are not showned in the wizard.

Just removes the line "Let pi=3.14159" (or use a "non reserved name" like "MyPi" if you want your own value of Pi) and the function will works.

Closed as Not a bug.

I don't know how the "Err:520" in the Function Wizard can NOT be a bug of some kind when it appears in a fresh start of LibreOffice Calc, and then disappears when something is really wrong like using a reserved variable name "pi" in LibreOffice BASIC.  Besides that, there must be some bug in the exception handling code if Calc is crashing so easily and frequently that a variable name in a function can't be changed, and the error reporting is not indicating BASIC problems properly.  

I can understand that immature code may exist in open source software, but to say false reporting of "Err:520" with all sorts of confusing operational consequences is "Not a bug", and then dismiss it, is like throwing the baby out with the bath water.  I have written a lot of macros using Excel for very sophisticated scientific and engineering spreadsheets programs and encountered a lot of bugs, but nothing like this.  For the sake of improving LibreOffice, I hope you will reconsider what is called a bug to dismiss significant problems that affect compatibility with Windows spreadsheets and documents.   

Incidentally, is there an way to call the pi() function for use in User Defined functions?

Thank you for your time and efforts, Dick

(In reply to comment #13) 
> 
> Incidentally, is there an way to call the pi() function for use in User
> Defined functions?

pi is a predefined const in Basic, no need to use the Calc pi() function. Both have the same double precision.

In general, access to Calc functions is via service "com.sun.star.sheet.FunctionAccess"

Example for function pi():

dim oFunctionAccess as variant
oFunctionAccess = createUnoService( "com.sun.star.sheet.FunctionAccess" )
dim myCalcPI as double
Dim args() As variant
myCalcPI = oFunctionAccess.callFunction( "PI",args())