Create Custom Functions in LibreOffice Calc

LibreOffice Calc allows users to extend its built-in calculation capabilities by building custom User-Defined Functions (UDFs). Using either LibreOffice Basic or Python, you can automate complex calculations, manipulate strings, and create reusable formulas that function just like native Calc formulas (such as SUM or VLOOKUP). This guide provides straightforward instructions for creating and implementing custom functions using both languages.

Creating Custom Functions with LibreOffice Basic

LibreOffice Basic is the simplest and most integrated method for writing custom spreadsheet functions. Functions written in Basic are immediately available in the formula bar of your spreadsheet.

Step 1: Open the Macro Editor

  1. Open your Calc spreadsheet.
  2. Navigate to Tools > Macros > Organize Macros > Basic…
  3. Select your current document (or My Macros > Standard to make the function available globally across all spreadsheets).
  4. Click New or select an existing module and click Edit.

Step 2: Write the Basic Function

In the editor window, define your function using the Function and End Function keywords. The function must return a value by assigning the result to the function name.

Function CALCULATETAX(amount As Double, taxRate As Double) As Double
    CALCULATETAX = amount * taxRate
End Function

Step 3: Use the Function in Calc

Return to your Calc sheet and enter the formula into any cell:

=CALCULATETAX(100, 0.05)

The cell will output 5.

Creating Custom Functions with Python

Python provides advanced libraries, modern syntax, and superior performance for complex logic.

Step 1: Locate the Python Scripts Directory

To make Python functions accessible to LibreOffice, place your .py files in the user scripts folder:

(If the Scripts or python directories do not exist, create them manually).

Step 2: Write the Python Script

Create a new file named custom_functions.py in the directory specified above and define your calculation:

def multiply_values(a, b):
    return float(a) * float(b)

Step 3: Bridge Python to Calc via Basic

While Calc handles Basic natively in cell formulas, the cleanest built-in method to execute a Python function directly from a cell without third-party extensions is to use a lightweight Basic bridge wrapper.

  1. Open Tools > Macros > Organize Macros > Basic…
  2. Add the following bridge function to your module:
Function PY_MULTIPLY(a As Double, b As Double) As Double
    Dim scriptProvider As Object
    Dim scriptURI As String
    Dim script As Object

    scriptURI = "vnd.sun.star.script:custom_functions.py$multiply_values?language=Python&location=user"
    scriptProvider = ThisComponent.getScriptProvider()
    script = scriptProvider.getScript(scriptURI)

    PY_MULTIPLY = script.invoke(Array(a, b), Array(), Array())
End Function

Step 4: Call the Python Function

In your spreadsheet cell, call the Basic wrapper that executes the underlying Python code:

=PY_MULTIPLY(12, 8)

The cell will evaluate and display 96.

Key Considerations for Custom Functions