Various barriers to developing Calc function libs LO 7.5

This Ask concerns some barriers I am encountering in developing function libraries for LO Calc.

Version: (X86_64) / LibreOffice Community
Build ID: 9f56dff12ba03b9acd7730a5a481eea045e468f3
CPU threads: 8; OS: Windows 10.0 Build 19045; UI render: Skia/Vulkan; VCL: win
Locale: en-US (en_US); UI: en-US
Calc: CL Jumbo

First, I as developer have had broad experience with app-level, kernel-level and driver-level software development, including, in ancient times, macro scirpting in LO Calc (circa 2016) and in Excel (circa 2008). Given minimal clues I can figure out most anything. You should surmise that my issues are lack of minimal clues…

I am also no stranger to LO Bugzilla, but before I go there regards some of what I am encountering I want to ask the question here “Is this intended behavior?”

I am using LO Basic to re-establish my intuition of simple stuff, thus avoiding the complexities of the other supported languages for now. I have reviewed though not memorized the DevGuide (LibreOffice Developer's Guide - The Document Foundation Wiki), especially chapter 11, and the LO Basic programming guide ( If you want to RTFM the you had better cite actual pages.

My desired development situation is to work in a single code instance of a Basic library established via Tools->Macros->Organize Macros->Basic in [My Macros] then test the functions intended for Calc use in several Calc spreadsheet files. Yes, I understand library deployment (LibreOffice Developer's Guide: Chapter 11 - LibreOffice Basic - The Document Foundation Wiki) but I am not there yet. Skipping that piece of user-facing doc that has one copying source code around Calc files, I have established an event handler infrastructure to GlobalScope.BasicLibraries.LoadLibrary() over a list of my development libs resident in [My Macros] at Start_Application event time.

My test function BASIC code is:

function test_func()
end function

I have MsgBox() instances in my code that confirm my test library loads at Start_App time and that the subs and functions within are accessible from the macro BASIC scripting context. So now on to an actual Calc use.

In a Calc test file cell:

My experiments procedure most of the time is 3 steps:

(1) Re-launch LO (not a direct app-file open) to the LO files thumbnails display GUI.
(2) From the LO GUI, launch a Calc test file.
(3) Tools->Macros->… to fiddle stuff, then close LO completely, loop to step (1).

Through tedious experimentation I have discovered that loading those [My Macros] libs any later than Start_Application event time, even before launching my Calc test file, results in Calc throwing a #NAME? error on a my test function name. The #NAME? error suggest Calc is not recognizing the name of my test function. Loading my test lib at Start_App time changes that thrown error to #VALUE! To me this suggests that Calc recognizes the name but then does not have access to or understand the return type of the function. I have tried explicit variations of:

function test_func() as double
function test_func() as variant

to no change from the #VALUE! error. All the macro examples I have found create their macros in the Calc file Standard library, not in [My Macros] then somehow getting a Calc file to search and load from [My Macros]. No help there.

OK, code replicating my test function in a Calc file Standard library does work as all the examples show, but no, not doing that as I would have to replicate code changes in repeated waves to my various test Calc files.

The workaround is this:

In the Calc file Standard lib (code that must be copied to every Calc file for testing) plug in a wrapper function:

function test_func_wrapper
end function

In the test Calc file cell:

works as desired. This indicates symbol resolution in to my loaded [My Macros] libs works just fine.

QUESTION: Is this function-symbol-required-in-Calc-file-Standard-lib the intended behavior?

What am I missing to make the function symbols loaded from [My Macros] directly usable by a Calc file cell formula without this in-Calc-file wrapper function run-around?

Two other issues of note:

The library export and import functions in Organizer are not symmetric, meaning you cannot import a library as a unit the you exported as a unit. The Organizer “Export as a BASIC library” creates a directory in your chosen parent directory containing the *.xlb meta-data xml files and the *.xba xml file(s) containing the BASIC code. OK, good. However, the import function opens a Directory/File Navigator widget that will only recognize files of [File type:] “BASIC” in the combo box. You cannot change that. Unlike the helpful convention everyone-else-uses of listing the *.suf suffixes that tell the user what are this widget’s idea of “BASIC”, the user is left to guess. Well, it turns out the *.xlb are recognized but *.xba are not. You cannot import as a unit that library you just exported. Oops.

Of course, this behavior puts a noticeable and error-prone copy-code-around work barrier compared to just repeatedly deleting then re-importing a changed-code library.

QUESTION: Is this macro lib export/import asymmetry the intended behavior?

Finally, Macro Security. I have been forced to develop with a Tools->Options->Security->Macro Security setting of LOW because the [Trusted Sources]->Trusted File Locations does not seem to work as implied. First, “File Location” suggests the pathnames in the list box are intended to be file system directory pathname both local and possibly accepting a remote protocol pathname. Well, just local filesystem pathnames do not seem work for me. Further, given that “Location” is presumptively a directory-like object, what about sub-directories? Do sub-directories inherit “Trusted” or must they be listed explicitly? The documentation on this function is silent.

QUESTION: What is the concept of operations of [Trusted Sources]->Trusted File Locations?

Chewy stuff.


I know that I have always considered the BASIC XML files as a complete file structure. The location in Windows is C:\Users\USERNAME\AppData\Roaming\LibreOffice\4\user\basic\Standard. The .xlb files are manifests for the .xla files that appear in the same directory. If I backup or move them to new installations, I do so by copying the whole Standard directory then copying in the .xlb and .xla file sets from that source Standard (or each of multiple sources) into the target installation Standard directory.

Soooo… the library import function processes the *.xlb XML files (script.xlb does list the non-dialog .xba files) to in turn capture those .xba files for import? That is a perfectly fine way to do it, but is that concept of operations documented in user-facing doc somewhere? A nice hint would have been the file system navigator widget display a [File type:] of “library manifest files.” That hints that the widget is not looking for all files that natively appear in the directory.

If the lib import function does use that manifest-files-to-find-other-files conops, the your reply answers that issue.


For Standard library behavior description, see tdf#79588 comment 29.

A friendly advice: please try to structure your questions in a more answerer-friendly way.

  1. One question per one Ask topic. This topic has three.
  2. Put the concise description of the observations and the question first, before the details of your torments on the road to understanding the situation. These details mostly don’t help people to understand what you ask, and to answer your question, but are a serious barrier for that - tl;dr.
1 Like