How to Speed Up LibreOffice Calc Macros with Large Datasets

LibreOffice Calc macros often become slow on large worksheets for a simple reason: the macro spends more time crossing the UNO object boundary than doing the actual calculation. Reading one cell, changing it, and writing it back thousands of times can be much more expensive than moving the same data in larger blocks.

The goal is not to make every macro “fast” in the abstract. A useful optimization should produce the same output, finish in less time on the same dataset, keep Calc responsive enough for the workflow, and avoid creating new correctness or memory problems. The techniques below focus on those measurable outcomes.

As of October 2026, the current LibreOffice SDK documentation identifies the spreadsheet interfaces used here in the LibreOffice 26.8 API reference. The core methods are long-standing UNO interfaces rather than a newly introduced performance feature.

1. Measure the macro before changing it

Start with a repeatable baseline. LibreOffice Basic provides GetSystemTicks(), which the official Help describes as a way to measure system ticks and optimize processes. Wrap the part of the macro you actually want to improve instead of timing workbook opening, user prompts, or unrelated setup.

Dim t0 As Long, elapsed As Long

t0 = GetSystemTicks()

' Run the work you want to measure here.

elapsed = GetSystemTicks() - t0
MsgBox "Elapsed ticks: " & elapsed

Run the same operation several times on the same copy of the file. The first run can differ because of file-system caches, formula state, or other one-time work. Record the dataset size, the range processed, and whether automatic calculation was enabled so that your comparisons remain meaningful.

A good result is not merely a lower timing number. Confirm that row counts, totals, formulas, text values, dates, and any output ranges still match the baseline.

Official reference: LibreOffice Help: GetSystemTicks Function.

2. Remove cell-by-cell reads and writes first

If a macro loops through a large range and repeatedly calls methods such as getCellByPosition(), that pattern is usually the first place to look. The UNO API exposes XCellRangeData.getDataArray() and setDataArray() specifically for moving a complete cell range as an array.

The official API states that getDataArray() returns a sequence of rows, with each row containing columns. Values are returned as strings or doubles. setDataArray() writes an array back to a range, and the array dimensions must match that range.

For example, suppose columns A through E contain the input that your macro transforms. Instead of reading each cell individually, select the exact range once:

Dim doc As Object
Dim sheet As Object
Dim sourceRange As Object
Dim data As Variant

doc = ThisComponent
sheet = doc.Sheets.getByName("Data")

' Example only: A2:E50001.
sourceRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)
data = sourceRange.getDataArray()

Process data in Basic memory, then write a result array to a matching destination range in one operation. This reduces the number of UNO calls dramatically compared with thousands of individual reads and writes.

Official references: LibreOffice SDK: XCellRangeData and LibreOffice SDK: XCellRange.

3. Process only the range you actually need

Bulk array access is helpful, but turning an unnecessarily huge sheet into an array can simply move the bottleneck into memory. Avoid hard-coding entire columns or the full Calc grid when the task only touches a defined table.

Determine the last meaningful row using information your workbook already knows: an import row count, a database result size, a designated key column, or a range that the application creates. Then request only the rows and columns required by the macro.

This is especially important when the worksheet contains formatting far below the real data or when old content has expanded the apparent used area. Large in-memory arrays consume RAM, and one oversized allocation may be worse than processing moderate blocks.

When to switch to chunking

If a single array makes memory usage jump sharply, causes long pauses before processing begins, or approaches the practical limits of the machine, process the data in blocks. For example, handle several thousand rows, write the results, then continue with the next block. There is no universal ideal block size; test with the actual workbook and hardware.

4. Suspend screen-update notifications during heavy work

A Calc document implements LibreOffice's XModel interface. Its lockControllers() method suspends some controller notifications used for display updates, while unlockControllers() resumes them. This can reduce unnecessary visual work while a macro performs many changes.

Dim doc As Object
doc = ThisComponent

doc.lockControllers()

' Perform bulk changes here.

doc.unlockControllers()

The official API is explicit that controller locks and unlocks must be paired. That means production macros should include error handling that guarantees unlockControllers() is reached even if processing fails. Leaving the document locked is not a performance optimization; it is a broken cleanup path.

Official reference: LibreOffice SDK: XModel.

5. Control automatic recalculation when formulas dominate runtime

If the macro repeatedly changes cells that feed many formulas, Calc may spend substantial time recalculating while the macro is still updating intermediate values. The spreadsheet document exposes the XCalculatable interface, including isAutomaticCalculationEnabled(), enableAutomaticCalculation(), calculate(), and calculateAll().

A safer pattern is to remember the user's current calculation mode, disable automatic calculation only during the update, and restore the original state afterward:

Dim doc As Object
Dim wasAutoCalc As Boolean

doc = ThisComponent
wasAutoCalc = doc.isAutomaticCalculationEnabled()

doc.enableAutomaticCalculation(False)

' Perform bulk updates.

doc.enableAutomaticCalculation(wasAutoCalc)

If wasAutoCalc Then
    doc.calculate()
End If

calculate() recalculates dirty cells. calculateAll() recalculates every cell, which can itself be expensive, so do not call it automatically just because a macro has finished. Use full recalculation only when the workbook's logic actually requires it.

As with controller locking, restore calculation state in an error-cleanup path. A macro should not silently leave a user's workbook in manual calculation mode.

Official reference: LibreOffice SDK: XCalculatable.

6. Combine the optimizations safely

The most effective structure is usually: determine a precise range, read it once, process values in memory, write results in bulk, and suppress avoidable UI or recalculation work only for the duration of that operation.

A practical cleanup pattern in LibreOffice Basic looks like this:

Sub ProcessLargeData
    Dim doc As Object
    Dim sheet As Object
    Dim dataRange As Object
    Dim data As Variant
    Dim wasAutoCalc As Boolean

    On Error GoTo CleanUp

    doc = ThisComponent
    sheet = doc.Sheets.getByName("Data")
    dataRange = sheet.getCellRangeByPosition(0, 1, 4, 50000)

    wasAutoCalc = doc.isAutomaticCalculationEnabled()

    doc.lockControllers()
    doc.enableAutomaticCalculation(False)

    data = dataRange.getDataArray()

    ' Transform data in memory here.
    ' Build an output array with dimensions matching its target range.

CleanUp:
    doc.enableAutomaticCalculation(wasAutoCalc)

    If doc.hasControllersLocked() Then
        doc.unlockControllers()
    End If

    If wasAutoCalc Then
        doc.calculate()
    End If
End Sub

The example intentionally leaves the transformation logic unspecified because that part depends on your workbook. The important optimization is the shape of the workflow, not a made-up business calculation.

7. Consider Calc's built-in functions before reimplementing them

LibreOffice Basic can call Calc functions through the com.sun.star.sheet.FunctionAccess service. If your macro is manually implementing a lookup, aggregation, or other operation that Calc already provides, using the built-in function may simplify the code and can reduce custom looping.

That is not a guarantee that every built-in function will outperform every custom algorithm. Benchmark the real workload. The official Help shows how to call Calc functions from Basic and notes that function names are supplied in English.

Official reference: LibreOffice Help: Using Calc Functions in Macros.

How to judge whether the optimization is good enough

Common mistakes that erase the benefit

• Reading a whole worksheet into memory when only a few columns are required.
• Using getDataArray() but still writing every result back cell by cell.
• Disabling automatic calculation and forgetting to restore the previous setting.
• Locking controllers without a reliable cleanup path.
• Calling calculateAll() after every small batch even when only dirty cells need recalculation.
• Judging speed from a single run instead of repeated measurements on the same data.
• Optimizing performance without checking that output remains identical.

Where these techniques stop helping

Array transfers mainly reduce spreadsheet-object access overhead. They will not fix an intrinsically expensive algorithm. A nested comparison of every row with every other row, for example, can remain slow even when all values are in memory. In that case, change the algorithm: use dictionaries or indexes, sort once instead of repeatedly, reduce duplicate work, or move the workload to a more suitable data-processing tool when the spreadsheet is no longer the right execution environment.

The same applies to macros dominated by network calls, database queries, file I/O, or external services. Measure first so that you optimize the actual bottleneck rather than the visible spreadsheet loop.

Recommended optimization order

1. Measure the current macro with a repeatable dataset.
2. Replace repeated cell reads and writes with range arrays.
3. Restrict processing to the exact rows and columns needed.
4. Use chunking if the bulk array is too large for comfortable memory use.
5. Lock controllers during large batches, with guaranteed cleanup.
6. Temporarily suspend automatic calculation when repeated formula recalculation is a demonstrated bottleneck.
7. Recalculate only as much as necessary, restore application state, and verify output.
8. Remeasure. If runtime is still dominated by computation rather than UNO access, improve the algorithm instead.

For large Calc datasets, reducing the number of calls between Basic and spreadsheet objects is usually the most important structural improvement. Treat every additional optimization as something to measure, not as a ritual. A faster macro is useful only when it remains correct, predictable, and maintainable.