LibreOffice makes it easy for programmers by allowing macros to be written in four languages

The best macros are not recorded through the user interface of an application. They are hard coded through the application’s coding interface.

In the last blog, I compared recording macros in LibreOffice to doing the same in Microsoft Office. In this article we will compare how each one handles macros in general.

Most Microsoft power users are familiar with macros in Office. The famous office suite uses Visual Basic for Applications (VBA). It has been part of it for two decades, being based on Visual Basic 6.0.

LibreOffice, by default, uses LibreOffice Basic, an open-source scripting language that was taken from OpenOffice.org. Most programmers are not familiar with this language, so LibreOffice uses other languages that are familiar to many programmers.

The open-source suite uses JavaScript, Python, and BeanShell to create macros in addition to LibreOffice Basic.

This article will focus on creating new macros in LibreOffice and the Windows and Mac versions of Microsoft Office.

LibreOffice
While LibreOffice can create and run macros created in JavaScript, Python, and BeanShell, its primary macro language is LibreOffice Basic. It has more options for this language in its dialogs.

First let’s understand how LibreOffice organizes macros, both new ones that you create and those that come with the installation.

All the macros are centrally organized through the macro library. It has three main

Macros Selector dialog in LibreOffice
The Macros Selector is accessed by clicking Run Macros in the Macros sub-menu. It shows the three main sections (or folders) where macros are stored: My Macros, LibreOffice Macros, and one for the current document. All the dialogs for macros show this structure.

folders in it: My Macros, LibreOffice Macros, and one for each of the open documents. These can be found when you:

  1. Click the Tools menu.
  2. Then highlight the Macros sub-menu.

Clicking either Run Macros or Edit Macros will launch dialogs with these three folders.

  • My Macros contains all the universal macros that have been created by you.
    • Universal macros are available to more than one document. They can be used in current documents or new documents that you create.
    • They may be able to run in any type of document. The script would run in an text document, spreadsheet, presentation, etc.
    • Or they may only be available to one document type, such as a spreadsheet.
    • It will contain LibreOffice Basic, Python, JavaScript, and BeanShell scripts.
  • LibreOffice Macros contains all the macros that came with the installation. These scripts are in one of the four languages.
  • For an individual document. This folder contains scripts in the four languages that are for that specific document.

Creating a macro
To create anew macro, you need to highlight the Organize Macros sub-menu that is in the Macros sub-menu. The choices are as follows:

  • LibreOffice Basic
  • BeanShell
  • JavaScript
  • Python

LibreOffice Basic
Clicking this opens the LibreOffice Basic Macros dialog. This dialog has more

LibreOffice Basic, derived from OpenOffice Basic, has been the suite's language for macros since it was forked from OpenOffice.org.

features than the dialogs for the other three languages. It lists the three folders mentioned in the previous section. The folder will have sub-folders which contains the macros.

When you click one of the macros, the different parts of the macro will be listed in the box to the right of it. You can click the Run button in the dialog to run the part you clicked on.

You can create new macros in My macros or one of the ftolders for the open documents. As mentioned before, My Macros is for universal macros and the document folders are for macros for that specific document.

Click on the folder or sub-folder where you want to store the macro. For example, if I want to store the macro in TestLibrary under My Macros, I click it.

Then I click the New button, from the column of buttons at the right of the dialog. This launches a My Macros & Dialogs window. This is where you can write code for a macro in the LibreOffice Basic language.

To learn more about the LibreOffice Basic language visit the following Web sites. It is the same as the OpenOffice Basic language.

BeanShell, JavaScript, and Python macros
Choose one of these languages from the Organize Macros sub-menu. Then choose the desired language. A corresponding dialog will open: BeanShell Macros, JavaScript Macros, or Python Macros.

These have the same features, but they only show macros for the language youJavaScript macro dialog chose.

  1. First click the desired folder or sub-folder where you want to create a new macro.
    • You can store a macro in any of the three main folders: My Macros, LibreOffice Macros, or one of the folders for the open documents.
    • NOTE: You can create a new macro in the LibreOffice Macros folder in the BeanShell, JavaScript, and Python dialog, but not in the LibreOffice Basic dialog.
  2. Click the Create button. Macros can only be created in a sub-menu. They cannot be created directly in My Macros, LibreOffice Macros, or one of the document folders.
    • If you have selected a sub-folder, this will open a Create Macro dialog, where you can name your new macro.
    • If you have selected My Macros, LibreOffice Macros, or one of the document folders, you can create a new sub-folder (or library). Clicking the Create button will launch a dialog to name the new library.
  3. Type over the default name, then click the OK button.
    Click the Edit button in the BeanShell, JavaScript, or Python Macro. This will open a debugger application for the macro.
  4. Type the code to create a new macro.

There are numerous resources for the three languages. Here are some popular ones.

BeanShell

JavaScript

Python

Microsoft Office
Macros are written in Visual Basic for Applications, a scripting language based on Visual Basic 6.0. They can be created in Word, Excel, PowerPoint, and Access for Windows and Word, Excel, and PowerPoint for Mac.

Since Access has a different interface and dialogs for macros, it will not be covered in this article.

Windows
The macro interface is the same in Word, Excel, and PowerPoint.

In Word do the following:Microsoft Word macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. Do not click the arrow below it. This launches the Macros dialog.
  3. Type a name for the macro.
    In the Macros in drop-down list:

    • All active templates and documents: This setting makes the macro available to other documents. For creating a macro, this is the same as the Normal.dotm choice.
    • Normal.dotm: This setting makes the macro available to other documents.
    • Word commands: This is a list of commands. Many come with the Office installation. New commands can be created and added to this list
    • (The name of the current document): This selection means the macro will only be available to this document.
  4. Click the Create button at the right. This will launch the Visual Basic for Applications application.
  5. Write your code.

In Excel do the following:Microsoft Excel macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. Do not click the arrow below it. This launches the Macros dialog.
  3. Type a name for the macro.
  4. In the Macros in drop-down list:
    • All Open Workbooks.
    • This Worbook.
    • (The name of the current document)
  5. Click the Create button at the right. This will launch the Visual Basic for Applications application.
  6. Write your code.

In PowerPoint do the following:Microsoft PowerPoint macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. Do not click the arrow below it. This launches the Macros dialog.
  3. Type a name for the macro.
  4. In the Macros in drop-down list:
    • All Open Presentations
    • (The name of the current presentation):
  5. Click the Create button at the right. This will launch the Visual Basic for Applications application.
  6. Write your code.

An alternative to using the View ribbon is to use the Developer ribbon. The developer ribbon can be accessed in Word, Excel, and PowerPoint.

  1. Click File.
  2. Click Options. This launches an Options dialog.
  3. In the dialog, click Customize Ribbons.
  4. In the second box of the Customize Ribbon tab, click the check box next to Developer.
  5. Click the OK button.

When you click the Developer tab in one of the three applications, there is a Visual Basic icon and a Macros icon. The Macros icon launches one of the previously discussed dialogs.

The Visual Basic icon launches the Microsoft Visual Basic for Applications application. You can choose whether the macro you are going to write in the Normal document to make it globally available, or you can save it to the current document, so it is only available to that document.

Visual Basic for Applications interface

This application is the same in Windows and Mac.

Mac
Macros in the Mac versions of Word, Excel, and PowerPoint have slightly different interfaces than their Windows counterparts, but they are mainly the same.

In Word do the following:Word for Mac macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. Do not click the arrow to the right of it. This launches the Macros dialog.
    Alternatively:

    1. Click the Tools menu
    2. Highlight the Macros sub-menu.
    3. Click the Macros item.
  3. Type a name for the macro.
  4. In the Macros in drop-down list:
    • All active templates and documents: This setting makes the macro available to other documents. For creating a macro, this is the same as the Normal.dotm choice.
    • Normal.dotm: This setting makes the macro available to other documents.
    • Word commands: This is a list of commands. Many come with the Office installation. New commands can be created and added to this list
    • (The name of the current document): This selection means the macro will only be available to this document
  5. Click the “+” button. This will launch the Visual Basic for Applications application.
  6. Write your code.

In Excel do the following:Excel for Mac macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. This launches the Macros dialog.
    Alternatively:

    1. Click the Tools menu
    2. Highlight the Macros sub-menu.
    3. Click the Macros item.
  3. Type a name for the macro.
  4. In the Macros in drop-down list:
    • All Open Workbooks.
    • This Worbook.
    • (The name of the current document)
  5. Click the “+” button. This will launch the Visual Basic for Applications application.
  6. Write your code.

In PowerPoint do the following:PowerPoint for Mac macro dialog

  1. Click View to reveal the View ribbon.
  2. Click the Macros icon. This launches the Macros dialog.
    Alternatively:

    1. Click the Tools menu
    2. Highlight the Macros sub-menu.
    3. Click the Macros item.
  3. Type a name for the macro.
  4. In the Macros in drop-down list:
    • All Open Presentations
    • (The name of the current presentation):
  5. Click the “+” button. This will launch the Visual Basic for Applications application.
  6. Write your code.

An alternative to using the View ribbon or the Tools menu is to use the Developer ribbon. The developer ribbon can be accessed in Word and Excel.

  1. Click the Word or Excel menu.
  2. Click Preferences. This launches an Preferences dialog.
  3. In the dialog, click Ribbon & Toolbar.
  4. In the second box of the Customize Ribbon tab, click the check box next to Developer.
  5. Click the Save button.

Conclusion
Most people are familiar with macros in Microsoft Office, and there are many more books and other tutorials about Visual Basic for Applications than there are about creating macros for LibreOffice or OpenOffice.org

Creating macros in Office’s applications is also easier to set up than it is in LibreOffice. All you need to do is open the Macro dialog, give the macro a name, choose whether it is global or simply tied to one document (if you are writing it for Word). In LibreOffice, you need to know what library you want to create the macro in and then make sure you are using the dialog for the language you want to write in.

LibreOffice is more flexible than Office. There are four languages to choose from rather than just one. This means that power users with different programming skills will have an easier time creating macros in LibreOffice than Office.

Java programmers can use BeanShell. JavaScript and Python programmers won’t have to learn different languages. They can use what they know.

The centralization of the apps in LibreOffice also makes it flexible. You can create macros that work in multiple applications. The dialogs and libraries for macros also can be accessed from anyone of the applications. You can see spreadsheet macros when you launch the macro dialog from a Writer document, for example.

Office users can only see spreadsheet macros from Excel. They cannot see them when they are using Word or PowerPoint. There is not an easy way to write a macro that works in Word, Excel, and PowerPoint equally.

While not as many people write macros in LibreOffice as do in Office, the open-source suite is more welcoming to programmers from different backgrounds because of this flexibility. It is a familiar tool for many more programmers.

Leave a Reply

Your email address will not be published. Required fields are marked *