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 some agonising moments Googling these problems.
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.
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 , embedding them in your document is a process that involves manually unzipping the .ods document , 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.
