Writing LibreOffice macros in BASIC

I had the questionable pleasure of needing to write some LibreOffice macros. This gave me some WTF moments that I will share here, in the hope that it will save a future reader 1 some agonising moments Googling these problems.

BASIC runtime error. Argument is not optional.BASIC runtime error. Argument is not optional.
This happens when you press “Run BASIC” (or F5) while the cursor is in a function or sub that requires an argument. Put the cursor in a sub that doesn’t require an argument (e.g. Main) and try running again.

BASIC syntax error. Label $(ARG1) undefined.BASIC syntax error. Label $(ARG1) undefined.
In other languages results from functions are often returned using the “return” keyword. This error occurs when you try to do the same in BASIC. Change it to FunctionName=ReturnValue.

Returning arrays/lists
If a function is called from a cell formula and an array is returned by that function, it will be converted to a 2D Array. This happens even if the function is immediately used as a parameter to another function, e.g. “=Function1(ListReturningFunction())“.

If you return a 1D Array, it will be converted to a 2D Array with 1 row and n columns. If you want to return n rows, you should define a 2D Array of 1 by n in your function:

Dim returnedArray(1 To n, 1 To 1)

Returning booleans
Similar to the Array-problem above, boolean values are converted when they are returned to a cell formula. They are converted to strings. Those strings cannot be used by formula functions that expect booleans (e.g. AND(), OR(), NOT()) . Have your BASIC functions return 0 for False and -1 for True instead.

Returning Null
Null is converted to an empty string. Some older sources suggest it as a way to fill the cell with an error value, without triggering error dialogs from the BASIC interpreter. That doesn’t work any more.

Recalculating
Use Ctrl-Shift-F9 to force a recalculation after changing a macro. Ordinary Recalculate (F9) does nothing and neither does autocalculate. I’ve had a few times where the forced recalculation (“hard recalculation” as it’s called) doesn’t work either. It might be a bug in the current version, but if you encounter it, closing and re-opening the sheet does seem to work.

Python
LibreOffice supports Python macros, somewhat. Apparently 2, embedding them in your document is a process that involves manually unzipping the .ods document 3, moving the scripts in the resulting folder, editing the manifest file and re-zipping them. That might not be much better than suffering a bit of BASIC.

Notes:

  1. That might very well be me.
  2. I’m aware that this is a link to OpenOffice.org. The LibreOffice people haven’t recreated all documentation after the fork.
  3. Like many modern file formats, Open Document Format files are just zipped folders.

Roundcube and X-notifier revisited

A couple of years ago I updated the X-notifier script for Roundcube to support multiple mailboxes. (Original post.) There was still an annoying bug in it: whenever the session expired the script wouldn’t recover. Lately that’s been bugging me more than usual. So, I figured I’d fix it. Continue reading “Roundcube and X-notifier revisited”

Roundcube and Google Contacts

The Roundcube logoOne of the reasons I use Roundcube is that when I was shopping for a webmail client, there was a plugin that provided Google Contacts integration. Unfortunately, that plugin hasn’t been updated for over a year and it has rate limiting issues. So, when upgrading my Roundcube installation to 0.8.4 I decided it had to be replaced. Updated for Roundcube 0.9. Continue reading “Roundcube and Google Contacts”

Roundcube and X-notifier

A couple of The X-notifier logoweeks ago I decided it was time to upgrade my Roundcube installation again. I don’t really enjoy doing that due to the amount of plugins I have installed. But it was overdue. In what must have been a stroke of pure brilliance, I allowed Webmail Notifier to upgrade too. (Part of that upgrade was a rename to X-notifier.) Continue reading “Roundcube and X-notifier”