Create a Document-Owned Basic Function in OpenOffice Calc

A worksheet function returns a result to the cell containing its formula. That is different from a recorded macro, which replays operations against the current selection. Here, a small Basic function totals positive numeric inputs without editing its source cells.

Scope and safety: This is a documentary exercise based on the Apache OpenOffice 4.1 desktop Calc Guide, with English menu labels. No Basic code, GUI workflow, macro security setting, save/reopen behavior, or installed operating-system build was tested for this article. The numerical expectations were checked independently outside Calc. Use a disposable ODS file and only code you have reviewed. If your existing macro policy blocks execution, stop; do not lower security or enable unfamiliar document macros to make an example work. No extension or add-in is needed.

Put the function in the document, not your personal library

Create and save a practice workbook named basic-function-practice.ods. Keep an untouched copy before experimenting.

1. Open Tools > Macros > Organize Macros > OpenOffice Basic.
2. Select the current document’s Standard library, not My Macros or OpenOffice Macros. If necessary, expand the document entry to see its libraries.
3. Use New to create a module named CalcFunctions, then open it for editing. A generated empty Sub Main ... End Sub can remain; it is not the worksheet function.
4. Add the code below in the module, outside any existing procedure. Put Option Explicit at the beginning of the module, once only. Save the module and the ODS document.

The official creation guide explains the document/library/module hierarchy. This exercise deliberately uses Standard from the start: the function-loading guide documents its automatic loading when a document opens. A newly created library with another name is not automatically loaded.

Define a narrow input contract

STPositiveTotal accepts either no argument, one numeric value, or one rectangular range containing only ordinary numeric values. It returns the sum of values strictly greater than zero. Zero and negative numbers contribute nothing; omission returns zero.

This intentionally small teaching function does not define behavior for text, blank cells, logical values, error values, or numeric-looking strings. Do not interpret it as a drop-in replacement for a built-in function’s coercion and error rules. Use a fully populated numeric fixture first. For routine conditional totals, prefer the built-in SUMIF rather than introducing a macro dependency.

Option Explicit

Function STPositiveTotal(Optional values)
    Dim total As Double
    Dim rowIndex As Long
    Dim columnIndex As Long

    total = 0
    If Not IsMissing(values) Then
        If IsArray(values) Then
            For rowIndex = LBound(values, 1) To UBound(values, 1)
                For columnIndex = LBound(values, 2) To UBound(values, 2)
                    If values(rowIndex, columnIndex) > 0 Then
                        total = total + values(rowIndex, columnIndex)
                    End If
                Next columnIndex
            Next rowIndex
        Else
            If values > 0 Then total = values
        End If
    End If
    STPositiveTotal = total
End Function

The code adapts the PositiveSum teaching pattern in the Apache OpenOffice guide’s Passing Arguments to a Macro, licensed CC BY 3.0. Changes include naming, branch order, formatting, and Long loop counters.

A single-cell reference supplies a scalar. A multi-cell range supplies a two-dimensional array, even when it is only one row or column. The two loops are therefore necessary for this implementation. LBound and UBound avoid assuming array bounds. Optional together with IsMissing handles omission before the value is used.

Check scalar, column, row, and block calls

Enter this complete numeric fixture in A1:B3, with no header row inside the range:

Enter each test formula in an empty cell outside that block:

The block result is 7 + 5 + 3 = 15. As a changed-input check, replace B2’s 5 with -5: the expected block result becomes 10, and the B-column result becomes 3. Restore B2 to 5 afterward. These are calculated expectations, not reported results from an OpenOffice session. If the sheet does not update, consult recalculation troubleshooting before assuming a code change is needed.

Values are not cell identities

The argument-values guide makes an important distinction: =STPositiveTotal(A1) passes A1’s value, not a cell object identifying A1. Supplying a range likewise does not hand this function the source addresses or formatting.

Do not extend this example by assuming values has a cell-address property. Passing an address as text and then resolving cells through the document API is a different design, outside this guide. Keep calculation inputs explicit rather than silently reading whichever cell happens to be selected.

Save, reopen, and diagnose availability separately

Save and reopen the practice ODS file only under your existing approved macro policy. Check that the document still contains Standard > CalcFunctions > STPositiveTotal, then compare the fixture results again. This is a verification step for the reader, not a persistence test completed for this article.

If the function is unavailable, separate these causes:

• Wrong owner: Code under My Macros is not the document-owned module you intended. Inspect the document’s own library tree before sharing the workbook; successful calculation on your machine alone does not establish portability.
• Different library: The guide documents loading a non-Standard library by expanding it in the Basic dialog. Loading it alone may leave formula cells in error until you edit or change them. For this small example, keep the function in Standard instead of depending on that manual step.
• Blocked macros: A loaded library does not override macro security. Stop at a policy block and use your normal review process.
• Name mismatch or duplicate: Compare the formula with the Function declaration and its return assignment. Calc function names are case-insensitive; uppercase display alone is not an error. Use a distinctive name to avoid ambiguity with functions in other visible libraries.
• Mixed input types: Return to the numeric fixture. Adding text/error-handling rules requires additional code and tests; the example makes no promise about those inputs.

For a larger project, the official loading guide describes a small wrapper function in Standard that checks BasicLibraries.isLibraryLoaded, calls BasicLibraries.LoadLibrary when necessary, then delegates to an implementation in another library. That is a separate architecture, not a reason to add automatic event handlers or change security settings here.

Why it is absent from the Function Wizard

A Basic function callable from a cell is not automatically a built-in catalog entry. The official add-in distinction notes that these macros do not appear in Calc’s function list. Type the formula directly; absence from the wizard does not by itself prove loading failed. Add-in development and installation are outside this exercise.

The five linked first-party guide pages were preserved in a 22 October 2026 documentation audit. Three were also retrievable during the 23 October review; the loading and argument-values pages were read from those earlier saved copies when live retrieval was unavailable. No cross-version equivalence or native execution is claimed.