Create a document-owned Basic custom function

A document-owned Basic function can be called from a Calc formula when it is stored in the document’s loaded macro library and execution is already permitted. Use explicit numeric arguments and a return value rather than making the function alter other cells.

Start in an authorized local environment

This procedure follows LibreOffice Calc 25.2 Community desktop with English standard menus on Windows or Linux. It is documentary; the function has not been executed in a native Calc session. Continue only where local macros are already authorized. If execution is blocked, stop rather than lower macro security or enable all macros.

Save a new disposable spreadsheet as VolumePractice.ods. Enter 2, 3 and 4 in A1:C1. In A2:C2 enter the labels Height, Width and Depth, and note elsewhere that all dimensions use the same unit. The expected volume is 24 cubic units.

Use the current document’s Standard library. This differs from storing code in My Macros, which belongs to your user environment, or in a custom library that may not load automatically when the document reopens.

Create a module in the document

1. Choose Tools > Macros > Organize Macros > Basic.
2. Locate VolumePractice.ods in the available macro containers.
3. Use the organizer/module controls to create a new module named VolumeFunctions in that document’s Standard library.
4. Open the module for editing in the Basic IDE. Confirm the document and library in the Object Catalog before changing any code.
5. Enter this complete function in the module, outside any Sub procedure:

Option Explicit

Function Volume(Height As Double, Width As Double, Depth As Double) As Double
    Volume = Height * Width * Depth
End Function

If the module already has Option Explicit at the top, keep one copy. A default empty Main procedure can remain, but do not paste Function Volume inside it. Save the module and the ODS.

The assignment to Volume supplies the return value. The typed arguments make the intended numeric interface explicit. This example assumes valid nonnegative numeric dimensions; it does not implement text parsing, unit conversion or validation of physical measurements.

Call and reconcile the function

In D1 enter =VOLUME(A1;B1;C1), using the argument separator configured in your Calc installation. If your configuration uses commas, use commas between the three references. See formula separators and syntax rather than changing global settings merely to match an example.

D1 should be 24. In E1 enter the independent control =A1*B1*C1, also 24. Change C1 to 5: both cells should become 30. Then set A1 to 0: both should become 0. Restore A1 to 2 and verify 30 again.

This control separates function lookup and library problems from the arithmetic. If E1 is correct but D1 errors, inspect the function name, module syntax, library ownership and macro policy. Do not assume a numerically correct ordinary formula proves the Basic function loaded.

Verify library loading after reopening

Save, close and reopen the ODS under the existing authorization policy. The guide explains that the document’s Standard library is loaded automatically, while arbitrary custom libraries are not. Confirm the function remains under VolumePractice.ods rather than My Macros.

Change B1 from 3 to 4. Both formulas should now return 40. Seeing only the previously displayed 30 is not enough to demonstrate successful function execution after reopening.

If macros are disabled, a custom function can be unavailable or produce an error. Do not suppress the warning or weaken security. Keep the ordinary multiplication control as the transparent alternative and stop the macro test at that boundary.

Retain ODS for this practice. Moving to another file format or computer requires a fresh compatibility and policy check; do not assume that a document-owned LibreOffice Basic function becomes an Excel function or travels independently of the document containing its code.

Official references

• LibreOffice documentation 1