LibreOffice addons in C++... OK, OK, Basic

There are times when the built-in functions you have in your spreadsheet program just won't do. You feel you need to write your own special function for convenience or to stroke your ego. This entails adding functionality to an existing program.... how would you go about doing that?

This was really inspired by Ken's post about developing a DLL for Excel with C++. I thought I'd cover the open-source side somewhat less competently by sharing how to put together the same thing for OpenOffice/LibreOffice Calc. Just so you could compare the different approaches and I get to learn something about C++ and LibreOffice suites.

Step 1: Download Excel...

OK, here goes.

Step 1: Look up the C++ examples in the LibreOffice API. Pick a simple example, like Counter.

Step 2: Recoil in horror at the length of a "very simple" example. Even if you take into account the padding that the BSD licence gives, there are still 120-odd lines in counter.cxx and 50-ish in countermain.cxx. Then there's the .idl file ...

Step 3: Realise that I am doing a silly thing, trying to use a Windows convention outside Windows. In LibreOffice, it does not seem possible to compile shared objects (what Windows knows as DLLs) and bundle them for use with VBA. What I should have been doing is to instead define a function in LibreOffice Basic.

So here's the real step 1: Open the Basic-IDE using the menu sequence below.

Step 2Drill down to Standard.Module1 in My Macros and click New:

Step 3: Write the following function into the editor:

Function addition(a, b)
    addition = a + b
End Function

Be sure to remove the two existing procedures, Sub Main and Sub Macro1; writing functions inside procedures is not acceptable in StarBasic. Then Save the code and close the editor.

Step 4: Use the function as you would an inbuilt function: For instance, write =addition(A1, B1) in cell C1. You can check by changing the values of A1 and B1 that you have a new function you can use with any new spreadsheet on your computer.

Step 5: Optionally, if you want to share the file with others, it should be located in ~/.config/libreoffice/3/user/basic/Standard/Module1.xba. Copy that XML file as, say, Addition.xba and update script.xlb with the following line:

<library:element library:name="Addition"/>

LibreOffice should detect it upon loading.

As with Excel, changes to the code outside of LibreOffice (even deleting the script) will not be detected until you restart.

Yes, yes, it's a bit of a cop-out. I will look more into the C++ analogue or talk about writing a macro in Python soon.