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.

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.

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.


  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.