Speed Up Calc Macros by Reading and Writing Data in Batches

The most reliable first speedup for a slow Calc macro is to replace repeated cell-by-cell reads and writes with one range read, an in-memory loop, and one range write. This cuts calls between Basic and Calc; it does not guarantee a fixed speed gain, because formulas, formatting, file size, and macro logic can also dominate runtime. The example below changes a status column and shows how to measure both speed and correctness. API details were checked against LibreOffice 26.8 documentation on October 9, 2026.

What should improve, and how will you measure it?

Run the current macro on a copy of the workbook and record its elapsed time. Use a fresh copy with the same starting data for the old and revised macros, or restore the input between runs; otherwise the second run may do less work. Keep row count, calculation settings, and output conditions the same. Repeat each run and compare the typical time rather than relying on one unusually fast run. Check that the result matches your expected rule. A macro that finishes sooner but changes the wrong rows is not an improvement.

LibreOffice Basic’s Timer function reports seconds since midnight; assign it to Long variables to get numeric seconds. For a short run, record a start and end value and subtract them. If the end value is lower, the run crossed midnight, so add 86,400 seconds to the difference. Timer reports whole seconds; use the ScriptForge Timer service for millisecond precision. See the LibreOffice Basic Timer documentation and ScriptForge Timer service documentation. Keep workbook opening, saving, and manual inspection outside the measured interval unless those are part of the real task.

Why do large Calc macros slow down?

A loop that calls getCellByPosition() or reads and writes individual cell properties repeatedly asks Calc to cross the Basic-to-Calc interface for each operation. With many rows and columns, those calls can add up even when the transformation itself is simple. A loop over an in-memory array still visits each row, but it avoids a separate cell API call for every value.

First remove operations that do not affect the result: avoid selecting or activating cells inside the loop, do not recalculate or format a cell repeatedly when one final range operation will do, and do not process entire columns if only a bounded data block is needed. These changes help isolate the actual bottleneck before you add more controls.

How do you process a range as an array?

Use a rectangular range that contains only the values your macro intends to change. The sample assumes a sheet named Data, a header row in row 1, and status values in column C. Change the sheet name and range to match your workbook. The API’s getDataArray() returns the range as rows containing values; setDataArray() writes a same-sized array back to that range.

Sub UpdateStatuses
    Dim oSheet As Object
    Dim oRange As Object
    Dim aData As Variant
    Dim i As Long

    oSheet = ThisComponent.Sheets.getByName("Data")
    oRange = oSheet.getCellRangeByName("A2:C10001")
    aData = oRange.getDataArray()

    For i = LBound(aData) To UBound(aData)
        If aData(i)(2) = "pending" Then
            aData(i)(2) = "review"
        End If
    Next i

    oRange.setDataArray(aData)
End Sub

The row array is zero-based: aData(i)(2) addresses the third column in each row. Here that is column C because the selected range starts at A. The array’s outer sequence represents rows and each inner sequence represents columns. The range passed to setDataArray() must have the same dimensions as the array; a mismatch raises an error. See the LibreOffice XCellRangeData API reference and LibreOffice ScriptForge array documentation.

Keep formula cells out of a value-only update

getDataArray() returns cell values as numbers or strings, not the original formula text. If you read a range containing formulas and write that array back with setDataArray(), those formulas can be replaced by their current results. Limit the value-array method to input/output cells that should contain values. When a macro must preserve or modify formulas, use the formula-array methods getFormulaArray() and setFormulaArray(), or work only on the non-formula range. The XCellRangeFormula API reference documents that separate interface.

When should you change calculation or display settings?

Only test these options after measuring the array version. If formula recalculation accounts for much of the run, you can query isAutomaticCalculationEnabled(), temporarily disable automatic calculation with enableAutomaticCalculation(False), then restore the original setting and recalculate dirty cells when needed. Put restoration in a cleanup path so an error does not leave the workbook in manual calculation mode. Do not disable calculation if the macro needs fresh formula results between steps. LibreOffice documents calculate() as recalculating dirty cells; calculateAll() forces a full recalculation and may itself be expensive. See XCalculatable API reference.

lockControllers() can suspend some notifications used for display updates while a macro changes many cells. Pair every lock with unlockControllers(), including when the macro exits through an error handler. This reduces display-update work; it does not turn off formula calculation or make per-cell logic into a bulk operation. Use it for a measured screen-refresh bottleneck, not as a substitute for range arrays. See XModel API reference.

How do you verify the change and know when to switch methods?

Run the revised macro on a workbook copy and record its time under the same conditions as the baseline. Compare the affected rows, unaffected rows, formulas, and key totals. Save, close, and reopen the copy if the production workflow includes saving; this can reveal issues that a quick visual check misses. A stable improvement should appear across repeated runs while the output checks continue to pass.

If the macro still spends most of its time on complex formulas, formatting, file I/O, or sorting, profile that stage separately; range arrays only reduce repeated cell access. For very large sheets, a full in-memory array can also use substantial memory. Process a few columns at a time or divide the rows into measured blocks if memory pressure rises, while checking that each block begins and ends on the intended rows. If the data already lives in a database and the task is filtering, grouping, or updating records, moving that work into a database query may be a better fit than loading every record into Calc.