Senior author, Open Source enthusiast.

Thursday, February 7, 2019 at 23:50

Macro in LibreOffice Made Easy

This is a simple introduction to macro for LibreOffice users. I intend this article for people who do not know macro but interested in making one. You will not write code with macro, instead, you do something with menu and toolbar then LibreOffice records your actions automatically as code. That's it, that's how it works. You will exercise some exciting, fancy macros here by using LibreOffice Writer (word processor) and Calc (spreadsheet) to grasp the basics. You will be introduced to the tools to make it and going through steps to make formatting macro on Writer as well as a really simple game macro on Calc. I hope this simple introduction helps you a lot to continue making better and greater macros. Go ahead!

Subscribe to UbuntuBuzz Telegram Channel to get article updates directly.

Summary

About macro
Limitations
Frequently used menu
Macro Manager dialog
Enabling macro feature in LibreOffice
Your first macro
Your first edit
Adding a push button
Making a simple game with macro
Making shortcuts of macros
Further readings

About Macro

Basically, a macro is an automatically created code based on user's interaction. Macro is widely used in software like Microsoft Office and LibreOffice. You do something once, record it as macro, and the software will repeat that job for you as much as you want. You do not write the macro code, the software writes it for you. After the code created, then you can freely edit that code to suit your needs. You can copy the macro code from one computer to another one to run your macro there. You can share your macros with your friends. For teachers, macro is great to make quizzes. For shop owners, macro is great to create transaction program ("Point of Sales" or POS). For general users, macro is great to automate everything you want to be automated.

Limitations

LibreOffice can only record macro in Writer and Calc. Today, Impress still cannot record macro.
Macro recording feature must be enabled first in order you to record macro.
Acts that involve window dialog cannot be recorded.
Mouse cursor movements and selections cannot be recorded, only keyboard pointer and keyboard selections.

1. Frequently Used Menu

Recording a macro:
Tools > Macros > Record a Macro

Run a macro:
Tools > Macros > Run a macro

Edit a macro:
Tools > Macros > Organize Macros > LibreOffice Basic > select a macro name > Edit.

Save a macro:
On the macro dialog: edit the Macro Name (top-left corner) > select location either under My Macros, or under LibreOffice Macros, or under current document > Save.

2. Your new dear friend

This is the Macro Manager dialog you will always (yes, always) use in LibreOffice. The rest of this article will make you to use this Macro Manager a lot. So know it and please be prepared.

(Macro Manager: menu bar Tools > Macros > Organize Macros > LibreOffice Basic)

[This macro manager shows three macros namely 'autobullet', 'autonumber', and 'autocolor' stored globally in LibreOffice]

3. Enabling Macro Feature

Go to menu bar Tools > Options.
Go to Advanced > give check mark for Enable macro > OK.

(Where to enable macro feature on LibreOffice)

4. Your First Macro

To grasp the concept, alright, let's create macro to automate coloring for mere 3 lines. It is very easy: start with record macro: you navigate your pointer with keyboard, select every line and give it yellow color; end with stop recording. You will save the macro created with file name you choose and place either in general place or within you document file.

Result talks first:

(Gif animation: automate coloring, bulleting, and numbering of three lines)

How to make it:
1) Write three lines of "first second third".
2) Record a macro
3) While recording, use your keyboard, select first line and color it yellow
4) Select second line and color it yellow
5) Select third line and color it yellow
6) Stop recording
7) Macro Manager dialog appears
7) Give it name autocolor (see animation above) and Save

How to run it:
0) Undo all colors, press Ctrl+Z to do it
1) Place your pointer to the beginning of first line
2) Run macro autocolor (find it under My Macros > Standard > Module1)
3) You see all lines automatically became yellow

Now you can make the two other macros of 'autobullet' and 'autonumber'.

5. Your First Edit

Your newly created macro is too quick. You cannot see the process clearly. To make it clearer, you should slow it down. To do that, you edit the macro source code, you add delay time of 1 second (1000 milisecond) for every step.

Where is the macro editor:
Go to menu bar Tools > Macros > Organize > LibreOffice Basic > select a macro named 'autocolor' > Edit.

How to edit a macro:

Notice that the code started with sub autocolor and ended with end sub
Do not get confused with the code. Simply see, there are same lines saying dispatcher.execute___(___,___).
Write the code Wait 1000 right below every that line.
Click save button.
Close macro editor.
Run the same macro once again.

(LibreOffice Basic: the macro editor which resembles MSO macro editor)

[The yellow highlights show you where to edit]

What do you see? You would see your macro runs slower like my first animation above. Easy, right?

Now you can also edit the two other macros of 'autobullet' and 'autonumber'.

6. Playing with form controls

In order to make a game, of course you need buttons. See gif animation below showing how to enable and disable form editing in LibreOffice. Once enabled, you see all user interface components are enabled including Push Button. Once disabled, they are disabled as well.

Form Controls dialog is available under menu bar View > Toolbars > Form Controls.

To create a button is to enable form editing above and select Push Button and drag and drop. Once created, you disable form editing, you can click it like push buttons. See gif animation below. It is simple.

7. Making a simple game with macro

The design is simple: click a button, you got score RIGHT. Click other button, you got score WRONG. Easy. This will require the Form Design feature in LibreOffice. Let's make it.

1) Put your pointer to A1 cell
2) Record a macro, type RIGHT in the A1 cell, stop recording. Delete the text.
3) Record another macro, type WRONG in the A1 cell, stop recording. Delete the text.

Up to this point, you have two macros, one saying RIGHT and one saying WRONG. Alright.

4) Open the Form Design dialog and create one button after another one. Now, you have two button.
5) Right-click first button > Controls > Events > Mouse Button Pressed > open the macro for RIGHT.
6) Do the same thing for second button, but with the macro for WRONG.
7) Disable Form Editing.
8) Click the first button, A1 should shows RIGHT. Click the second button, A1 should shows WRONG.

Congratulations! You've created a simple game with LibreOffice macro.

(The simple game)

8. Making shortcuts of macros

See my first animation above? There is a floating toolbar with shortcuts to macros. That makes our job easier. How to make it yourself? It is simple:

1) Go to Tools > Customize > Toolbars
2) Click New button > name it 'My Own Macros!'
3) Click Add Command button
4) Scroll down > select LibreOffice Macros > select your macro > click add > repeat for all other macros you want > OK
5) Call the new toolbar from menu bar View > Toolbars > My New Macros

See? It's not that hard.

(Oh, and yeah, you can even add icon for every macro like that)

Telling you a secret

The secret is, there would be many tutorials in macro for Microsoft Office that could be applied to LibreOffice. There are so many books in macro that teach you Word and Excel macros, that you can try in Writer and Calc. Okay, you can take a start from Western Sydney University tutorial in making quiz in Excel macro. Perhaps not always working instantly, but you should only need to adapt some of it. You can start doing the simplest macros first. Please share this secret to your friend.

Closing Words

That's all. This is just a very simplistic introduction. I hope you can learn by yourself more. If you want to learn more right now, simply read further readings below. You can now develop your own macros. Happy working!