LibreOffice macros: Basic, the recorder and first scripts

LibreOffice is widely used in public bodies, schools and small businesses that prefer a free and open-source suite. People coming from Excel often look for macros and discover that they exist but work differently: the language is called LibreOffice Basic and, although it resembles VBA, it talks to the program through its own interface called UNO. Understanding this difference saves a lot of frustration.

Where macros live

Everything goes through the Tools > Macros menu. The Organize Macros > Basic entry opens the list of libraries, which are kept in three places:

• My Macros: stored in the user profile, available in any document opened on your computer;
• LibreOffice Macros: those shipped with the program, read-only;
• a single document: the macro travels inside the .ods or .odt file and works on other computers too.

For a personal tool, such as formatting a recurring report, My Macros is the most convenient choice. For a form to hand out to colleagues it is better to embed them in the document.

The recorder and its limits

The recorder exists, but it must be switched on in Tools > Options > LibreOffice > Advanced by ticking the option to enable macro recording. It works only in Calc and Writer. Once enabled, Tools > Macros > Record Macro appears.

The code it produces, however, is hard to read. Every action becomes a call to the "dispatcher" with a list of properties:

dispatcher.executeDispatch(document, ".uno:Bold", "", 0, Array())

It is the equivalent of pressing a button in the interface. That is fine for short automations, but it becomes difficult to modify. For any macro meant to last, it is better to write code that uses the document's objects directly.

Accessing sheets and cells in Calc

The starting point is ThisComponent, which represents the active document. From there you reach sheets and cells:

Sub FillHeader
    Dim sheet As Object
    sheet = ThisComponent.Sheets.getByIndex(0)

    sheet.getCellRangeByName("A1").setString("Customer")
    sheet.getCellRangeByName("B1").setString("Amount")
    sheet.getCellByPosition(2, 0).setString("Due date")
End Sub

Two details to remember. getCellByPosition takes the column first and then the row, both counted from zero: (2, 0) is cell C1. And cells distinguish between content types: setString writes text, setValue writes numbers, setFormula writes formulas in English notation, for example =SUM(B2:B20) even in a localised installation.

A complete example: highlighting overdue items

Suppose you have a list of invoices with the due date in column C and want to colour the overdue ones red.

Sub HighlightOverdue
    Dim sheet As Object, cell As Object
    Dim row As Long
    sheet = ThisComponent.Sheets.getByName("Invoices")
    row = 1
    Do While sheet.getCellByPosition(0, row).getString() <> ""
        cell = sheet.getCellByPosition(2, row)
        If cell.getValue() < CDbl(Date) Then
            sheet.getCellRangeByPosition(0, row, 2, row).CellBackColor = RGB(255, 200, 200)
        End If
        row = row + 1
    Loop
End Sub

The loop runs through the rows as long as it finds a customer name, so it adapts to the length of the list on its own. Dates in Calc are numbers, which is why comparing with today's date converted to a number works directly. Conditional formatting can achieve a result like this without any code: the macro becomes preferable when the rule is more complex or when you want to perform several actions at once, such as colouring, sorting and exporting to PDF.

Working with Writer

In Writer the object model is different: the document has a text, the text has paragraphs, and a cursor lets you move around and insert content. A typical use is replacing placeholders in a template:

Sub ReplacePlaceholder
    Dim search As Object
    search = ThisComponent.createReplaceDescriptor()
    search.SearchString = "{CUSTOMER}"
    search.ReplaceString = "Rossi Ltd"
    ThisComponent.replaceAll(search)
End Sub

Macro security

As in any office suite, macros can perform powerful operations. In Tools > Options > LibreOffice > Security > Macro Security you choose the level. The recommended setting runs without asking only macros stored in trusted locations and asks for confirmation for the others. My Macros, kept in the profile, are not subject to these warnings. A document received from strangers that contains macros should be opened with macros disabled.

Basic, Python or something else?

LibreOffice also supports macros in Python, which use the same UNO interface. For those who already know Python they are a good alternative, although the built-in editor exists only for Basic and Python files have to be placed by hand in the profile folder. In my work I use Basic for small automations inside the document and external Python when many files need processing together.

In short

• LibreOffice macros are managed from Tools > Macros and can live in the profile or in the document.
• The recorder must be enabled in the advanced options and produces dispatcher-based code.
• ThisComponent, getCellByPosition and getCellRangeByName are the basics for working in Calc.
• Rows and columns are counted from zero, and the column comes before the row.
• Set security so that only macros from trusted locations run.