making self-defined functions available for all spreadsheets

users
#1

Hi,

how can I make a self-written function (i. e. a macro) available for all spreadsheets so that I can use this function for any spreadsheet I create or load and modify?

Ideally, this should work with multiple users in that I'd like to deploy the macro(s) somewhere central for others to use.

#2

Hi,
You have two choices:

1) Write an add-in package which is installable via the extension
manager. These functoons can have their own localized help pages and
function names. They appear in the function wizard just like a built-in
function. You can not write add-ins Basic code.

2) Quick and drity Basic functions without any integration can be stored
in the "Standard" library of the global macro name space ("My Macros").
This library is loaded automatically so the functions can be found at
any time.
2a) Since it is possible to call macro code from different languages,
you can write or re-use functions written in Java, Python or JavaScript
and write a Basic wrapper. The spreadsheet calls the Basic function
which itself calls the other script.

HTH

#3

Thank you very much! For now, I'll just put the macro into the "Standard" library --- I tried it and it works.

Would it be possible to have one file this "Standard" library is saved in for multiple users, or would that give problems with several users accessing the same file? Perhaps I could put it on a network share and allow only read-only access to it.

#4

Yes. Build your own LO installation package with your macros in the user
profile.
Just in case your macros refer to a specific class of user defined
document templates: In this case you store all macros in the template
they refer to. And yes, distributeion of such templates requires an
install package with modified profile or a user who installs the
template into the profile via File>Templates>Save...
I can't find the LO administration guide on libreoffice.org. May be
someone else can point us to it.

#5

and then there are templates (with specific macros, styles, layouts etc)
packaged in an extension.

#6

How do you build an installation package?

LO is installed on a Windows terminal server for all users, yet each user has their own settings. To get macro distributed, I have to save it to a text file which then can be copied and pasted from for each user. Or is there a better way?

BTW, is there a way to use an external editor to edit these macros? I'd rather use a decent editor like emacs than the poor thing built into LO.

#7

How do you build an installation package?

The code needs to live in your own name space (not library "Standard").

Basic...

[Organizer...]
Tab [Libraries]
[Export]

Non-Standard libraries can be imported from documents and templates. For
instance, you may distribute a document with documentation and examples
carrying the macros in a non-Standard library and use the Basic
organizer import/update that code.

LO is installed on a Windows terminal server for all users, yet each
user has their own settings. To get macro distributed, I have to save
it to a text file which then can be copied and pasted from for each
user. Or is there a better way?

BTW, is there a way to use an external editor to edit these macros? I'd
rather use a decent editor like emacs than the poor thing built into LO.

You can use Python or JavaScript as macro language with your favourite
development tools. The Basic "IDE" is horrible, indeed.
Such macro code can be dumped as pure source code into the user profile.

#8

Hi,

hw schrieb:

Hi,

how can I make a self-written function (i. e. a macro) available for all
spreadsheets so that I can use this function for any spreadsheet I
create or load and modify?

Ideally, this should work with multiple users in that I'd like to deploy
the macro(s) somewhere central for others to use.

In addition to the suggestions by Andreas, in the Basic Organizer, you can export a macro library to an extension (file name .oxt). When you then install this extension "for all users" the library goes to <installation directory>/share/uno_packages/... and with the next start of LibreOffice another user will find it in its UI in "LibreOffice Macros&Dialogs".

But although this works in general, it will not work for Basic functions, which are written to be used in Calc formulas, see https://bz.apache.org/ooo/show_bug.cgi?id=53615 and likely the same root cause in https://bugs.documentfoundation.org/show_bug.cgi?id=79588.

Kind regards
Regina

#9

Thank you very much! This is good to know, though currently, it is actually a macro written in BASIC. It provides some functions to be used with spreadsheets.

BTW, is there a macro readily available somewhere to genearate EAN-13 codes for spreadsheets? I got something for perl which I could translate, and it might save time if there's already a function for spreadsheets.