Bug in LO Calc and VBA?

[LKeithJordan]

I think I have found a bug in certain cell formulas produced by VBA Script. So far, I have been met with Err:508 and #Name? errors. Please let me elaborate:

OS: Win 7 (x64) & Linux Mint (x64)

Ver: LO 5.3.6.x (x64) or LO 5.3.7.x (x64) (I have updated to 5.3.7.2 and the errors still occur)

I am building a CPE course where I plan to show my students how LibreOffice Calc can use code from multiple languages. I already have a working application in Excel 2007, and the same application in Google Sheets. I plan to create a counterpart in LO Basic, but I also want to show that LO Calc can use code from VBA and JavaScript.

I found an error when working with the VBA code. After opening the Excel worksheet containing the VBA macro and saving to ods format, I tried to run the script. The macro executed once I rem’d out the “Sheet1.Activate” line of code.

The macro works fine in Excel 2007, but I was met with “Err:508” in numerous cells when attempting to run it in LO Calc. There is nothing wrong with the offending cells. In fact, you can edit any of the offending cells and make ANY editing change and the cell will display properly. For instance, edit an offending cell, delete the last parenthesis, and hit “Enter.” The final parenthesis will be added back and the formula will now work – along with any other cells dependent upon that particular cell.

After further research, I created a separate worksheet that further isolates the problem. In this worksheet, I try several tests. Cells with just numbers or numbers and operators work fine. The SUM function also seems to work fine.

HOWEVER, the ROUND function now displays “#NAME?” (as opposed to “Err:508” in the original worksheet). In either the original worksheet or the new abbreviated test worksheet, any manual modification to the offending cell (such as described above) will correct the problem.

I have attached a file. Open the worksheet, click the “TEST” button, and a description of the formula will appear in column A, along with the actual formula and results in column C.

BUG_TEST.ods

I have not yet tried to convert the Google Sheet to ods format, so I do not know if JavaScript will produce the same error.

Has anyone else noticed this problem? Is this a bug, or am I missing something?

All help is appreciated.

[jimk]

Upvoted because it is a well-written question, but yes, you’re certainly missing something.

[jimk]

Well, I’m surprised any of that code works, because these are formulas, not values. Here is the proper way.

Cells(4,3).Formula = "=ROUND(6000*2;0)"

Note: Unexpectedly, setFormula() does not work here. Using Option VBASupport 1 does strange things, and I do not like it. My recommendation to students: VBA is for MS Office, and LO Basic is for LibreOffice.

Further, IMHO, Python is the clear winner for general LO macro programming, although there is still a case to be made for Basic. On my wish list: Python user-defined Calc functions and an organizer like APSO.

For complex tasks, it may be worth considering Java, as it works closely with the UNO API, and the language has better compile-time checking for errors. However, there are drawbacks such as queryInterface().

Currently, LibreOffice built-in support for JavaScript is poor, and that does not seem likely to change anytime soon. I would prefer that they drop the idea altogether, because it would take a lot of resources to develop it into a worthwhile option, and even then, the language simply is not as good as Python for macros. I imagine that the motivation is that many people are familiar with JavaScript, but it’s not that hard to learn a new language. What’s harder is to learn the UNO API. Anyway, JavaScript would still be an option similar to how C# or Delphi works, just not built-in.

The other language mentioned at Writing Macros - Apache OpenOffice Wiki is BeanShell, but I do not use it, nor do I hear of people who do. Again, I wish they would just drop support for it and stick with the three main languages.

[librebel]

It also works here with the semicolon instead of the comma as argument separator, which is already standard to my locale.

[LKeithJordan]

Thank you, Jim, for a most complete answer – so complete, in fact, that I gave your answer the check without trying it first. (I have no doubt it works, and will try it when I begin work – it’s 3am here.)

I’m still new to LO, and I have much I want to learn and do in time. Not that much different from M$O in respect to learning. I’ve been a power user in Office for years and there is still a lot to learn there.

As a CPE course author and presenter, I have been…

[LKeithJordan]

…working to expand the horizons of my fellow CPAs. This includes working with both databases and code scripting in addition to spreadsheets, per se. I like LO, both as a product and as part of the FLOSS philosophy, and I am touting it to those who might be interested. This year I’m upping my game by adding courses to demonstrate how to use LO to accomplish more advanced tasks…

Thanks again for the time and effort you took with your answer. I really appreciate it.

[LKeithJordan]

And thanks to you, librebel, as well. Thanks to your answer, I now understand the reason and need for the semicolon argument separator. Comma seems standard for US locale.