You are using an outdated browser. Please upgrade your browser or activate Google Chrome Frame to improve your experience.

English | 中文 (简体) | Deutsch | Español | Français | Italiano | More...

logo

  • English documentation
  • Support LibreOffice
  • Documentação em português
  • Apoie o LibreOffice
  • Dokumentace v češtině
  • Documentación en español
  • Documentazione in italiano
  • Unitevi a noi!
  • Documentation en français
  • Documentatie in het Nederlands
  • Doe met ons mee
  • Dokumentado en Esperanto
  • Polska dokumentacja
  • Dokumentacija v slovenskem jeziku
  • 支持 LibreOffice
  • Türkçe Belgeler
  • --> --> --> --> --> -->
  • English documentation /

Macro Guides

Basic reference cards.

J.F. Nifenecker reference cards for quick access to LibreOffice BASIC programming.

BASIC Overview

Download BASIC Overview Reference Card

BASIC Dialogs

Download BASIC Dialogs Reference Card

BASIC Events

Download BASIC Events Reference Card

BASIC Files

Download BASIC Files Reference Card

BASIC Runtime Library

Download BASIC Runtime Library Reference Card

BASIC Runtime parameters

Download BASIC Runtime parameters Reference Card

BASIC Structured Data Types

Download BASIC Structured Data Types Reference Card

Andrew Pitonyak Latest Macro Guide

LibreOffice and OpenOffice.org share the same Basic macro language and API (Application Programming Interface).

  • @libreoffice
  • @LibreOfficeBugs

Reddit

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy) | Statutes (non-binding English translation) - Satzung (binding German version) | Copyright information: Unless otherwise specified, all text and images on this website are licensed under the Creative Commons Attribution-Share Alike 3.0 License . This does not include the source code of LibreOffice, which is licensed under the Mozilla Public License v2.0 . “LibreOffice” and “The Document Foundation” are registered trademarks of their corresponding registered owners or are in actual use as trademarks in one or more countries. Their respective logos and icons are also subject to international copyright laws. Use thereof is explained in our trademark policy . LibreOffice was based on OpenOffice.org.

add macro libreoffice calc

DebugPoint.com

Linux and Dev Portal

Calc Macros

Working with Dialog Controls in LibreOffice Calc using Macro – Part 1

Arindam

This tutorial will cover on how to create a basic LibreOffice Dialog and adding various controls such as TextBox. Also it covers on how to read the control values using Calc Macros.

Table of Contents

Adding a Dialog

To add a Dialog in LibreOffice Calc, select from menu: Tools -> Macros -> Organize Dialogs…

Create Dialog from Menu

Once above options is chosen, below LO Basic Macro Organizer would open. On the Dialog Tab, click New button.

Create a New Dialog

On the New Dialog pop-up , give a name to your dialog. For this tutorial, lets keep it as default Dialog1. Press OK.

New Dialog

You can see the Dialog1 is come up under My Dialogs -> Standard -> Dialog1.

Adding a TextBox Control

On the same LO Basic Macro Organizer, click EDIT while Dialog1 is selected.

Edit Dialog

Entire Dialog editor would open inside LO Calc. Now its time to add some controls. For simplicity, I would add a textbox and a button.

The controls are placed at the bottom of the screen. Select a control and drag your mouse inside the Dialog to place your desired control. For this tutorial, add a textbox and a button. If you are unable to see the toolbox at the bottom of the screen, select from menu: View -> Toolbars -> Toolbox.

The Dialog should look like this now.

The Dialog

To set the properties of each controls, click/select the control, then you can see the properties window opened on the bottom-left side of the editor. On the properties window, the ‘name’ value of the General tab would be used to identify the objects inside Macro. For textbox the default name is: TextField1, for the button it is: CommandButton1.

Dialog with Properties

Now, we are all set with the Dialog, for now. Lets write the macro.

Using a LO Calc macro, I will show how to open the dialog, read the contents of the textbox and some content from Calc cell from sheet1. Then show the content of the textbox and contents from Cell as a message box prompt while the button is clicked.

Go to the macro editor (click the Module1 tab) and create two functions as below. Tip: Read the tutorial on how to create a Macro

Declare an object oDialog1 as Object type outside of the functions scope. Because we need to access the Dialog in a difference function.

In StartDialog1() function we will load the “Tools” library. As per current design of OpenOffice/LibreOffice, the libraries are implemented as UNO interfaces, NOT as UNO Services. When OpenOffice/ LibreOffice is initiated, not all the basic libraries are loaded to save time. Thus if you have created your own library say, Library1, you need to load the library explicitly inside basic to identify the user defined functions. To load any library, use the built-in properties BasicLibraries.LoadLibrary.

For this tutorial, we will use the OpenOffice/LibreOffice predefined library “Tools” with below code. LoadLibrary property will load the Tools library and then using LoadDialog() function and Execute function we will show the created Dialog.

Alternate Code Using below snippets also, a dialog can be opened.

Now its time to read the content of TextBox and 1st cell of Sheet1 of Calc i.e. A1. To get a hold of a control, use GetControl( controlName ) function of the dialog object. Once the object is ready, you can use various properties available. To get the text entered in the textbox, use control’s Text() property.

Before we run the macro, we need to assign the readDialog1() function to button click event. To do that, go back to Dialog Editor and select the commandbutton. In the properties window, select Events tab. Assign the readDialog1() function by clicking the Execute Action button and choosing the macro.

Tip: Click here to learn more on how to assign a macro to a form control / button.

Put any text in Calc sheet1’s cell A1 – for this tutorial, I have put “Hello World”.

Open the Macro editor and run the function StartDialog1(). The dialog would execute and popup. Now, put some text on the text box and click the button. You should see the text from the textbox from the dialog as well as from the Calc sheet1’s A1 cell.

Running the Macro

This way, you can work with dialogs and controls via basic Macro in OpenOffice/ LibreOffice Calc.

Watch the video of this tutorial.

Function References – Used in this article

var = Dialog .GetControl(“ ControlName ”) : Returns handle to the control object TextBoxControl .Text() : Returns Textbox value [String] BasicLibraries.LoadLibrary(“ LibraryName ”) var = LoadDialog(“ LibraryName “, “ DialogName “) : Returns handle to the dialog. DialogHandle .Execute()

Complete Macro

To run, Copy and paste this entire code block below in Macro Editor of LibreOffice.

Looking for Something Else?

If you are looking for something else in LibreOffice macro tutorials, Or, wants to learn more about it, please follow below link for complete Macro Tutorials Index:

LibreOffice Macro Tutorial Index

Share this:

Arindam

Posted by Arindam

guest

This site uses Akismet to reduce spam. Learn how your comment data is processed .

 alt=

forgot password

Genuine software, legally FREE - no cracking required!

Edit Word, Excel, and PPT effortlessly

Read, edit, and convert PDFs with the powerful PDF toolkit.

Microsoft-like interface, easy to use.

Windows • MacOS • Linux • iOS • Android

banner

  • Articles of Office solution

Writing a Macro in LibreOffice Calc - A Comprehensive Guide

Dive into our guide on LibreOffice Calc macros, designed for content writers and office app aficionados. Unleash the power of automation and supercharge your productivity. Let's transform the way you work!

Part 1: What is the macro in LibreOffice Calc?

A macro in LibreOffice Calc is a set of recorded actions that can be replayed to automate repetitive tasks. It allows users to perform complex operations with a single click, saving time and effort.

Macros are used for a wide range of purposes, including:

Automating Tasks: Macros can perform sequences of actions, such as formatting, calculations, and data manipulation, in one go.

Customizing Functions: They enable users to create custom functions that may not be available through standard menu options.

Data Cleaning and Analysis: Macros can be used to clean and analyze large datasets quickly and efficiently.

Creating Interactive Reports: They can be employed to generate dynamic reports that update based on changing data.

Enhancing Productivity: Macros streamline workflows, allowing users to focus on higher-level tasks.

How to create a macro in LibreOffice Calc:

Step 1: Click on Tools > Macros > Record Macro.

Step 2: Enter a name for the macro and click on Start Recording.

Step 3: Perform the actions that you want to automate.

Step 4: Click on Tools > Macros > Stop Recording to save the macro.

How to run a macro in LibreOffice Calc:

Click on Tools > Macros > Run Macro and select the macro that you want to run.

Macros can be a very powerful tool for automating tasks in LibreOffice Calc. However, it is important to note that macros can also be used to spread malware. Therefore, it is important to only run macros from trusted sources.

Part 2: Steps to Write your first macro in LibreOffice Calc

Here is step-by-step tutorial on how to write a macro in LibreOffice Calc:

Step 1: Adding a Macro

Click on Tools > Macros > Organize Macros.

In the Macros dialog box, click on the New button.

Enter a name for the macro and click on OK.

The macro editor will open.

Step 2: Recording a Macro

Click on the Record button.

Perform the actions that you want to automate.

Click on the Stop Recording button to save the macro.

Step 3: Running a Macro

Click on Tools > Macros > Run Macro.

In the Macros dialog box, select the macro that you want to run and click on Run.

You can use macros to automate a wide range of tasks in LibreOffice Calc. For example, you can use macros to:

Format spreadsheets

Create charts

Perform calculations

Generate reports

Macros can be a very powerful tool for saving time and improving efficiency.

Part 3: Creating shortcuts of macros in LibreOffice Calc

To create a keyboard shortcut for a macro in LibreOffice Calc, follow these steps:

Step 1: Open the Macro dialog box by clicking Tools > Macros > Organize Macros.

Step 2: Select the macro that you want to create a shortcut for and click the Modify button.

Step 3: In the Macro Properties dialog box, click the Shortcut Keys tab.

Step 4: In the Shortcut Keys section, press the keyboard shortcut that you want to assign to the macro.

Step 5: Click the Assign button.

Step 6: Click the OK button to close the Macro Properties dialog box.

Step 7: Click the Close button to close the Macro dialog box.

The keyboard shortcut will now be assigned to the macro and you can use it to run the macro. Be careful not to assign keyboard shortcuts that are already assigned to other functions in LibreOffice.

Tip: You can also create macros and assign keyboard shortcuts to them using the LibreOffice Basic IDE. This is a more advanced way to create macros, but it gives you more control over the macro code.

Part 4: Best Excellent Alternative to LibreOffice Calc - WPS Spreadsheet

WPS Spreadsheet is a free and open-source spreadsheet application that is compatible with Microsoft Excel, Google Sheets, LibreOffice Calc, and OpenOffice Calc. It is available for Windows, macOS, Linux, Android, and iOS devices.

WPS Spreadsheet offers a wide range of features, including a powerful formula engine, charting tools, pivot tables and slicers, conditional formatting, and collaboration tools.

WPS Spreadsheet is a good choice for a wide range of users, including students, home users, and businesses of all sizes.

How to enable macros in WPS Spreadsheet

To enable macros in WPS Spreadsheet, follow these steps:

Step 1: Open WPS Spreadsheet.

Step 2: Click on the File tab and select Options.

Step 3: In the Options dialog box, click on the Trust Center tab.

Step 4: Click on the Trust Center Settings button.

Step 5: In the Trust Center dialog box, click on the Macro Settings tab.

Step 6: Select the Enable all macros (not recommended; potentially dangerous code can run) option.

Step 7: Click on the OK button to close the Trust Center dialog box.

Step 8: Click on the OK button to close the Options dialog box.

Note: Enabling macros can pose a security risk, so it is important to only enable macros from trusted sources.

How to Download WPS Office :

Step 1: Head to the official Free Download WPS Office for PC/ Windows/ Mac | Download Latest Version

Step 2: Choose your preferred package (Windows or Mac).

Step 3: Follow the straightforward installation process with visual guidance:

a. Windows: https://www.wps.com/installation-guide/?package=wps_wid-exe

b. Windows (Alternative Guide): https://www.geeksforgeeks.org/how-to-install-wps-office-on-windows/

Opt for WPS Office , the intelligent choice for a free, feature-rich alternative to Microsoft Office on both Windows and Mac platforms.

Use Word, Excel, and PPT for FREE, No Ads.

Edit PDF files with the powerful PDF toolkit.

Microsoft-like interface. Easy to learn. 100% Compatibility.

Boost your productivity with WPS's abundant free Word, Excel, PPT, and CV templates.

Can anyone create LibreOffice Calc macros?

Yes, anyone with basic knowledge of LibreOffice Calc can create macros. The software offers a recording feature for simple tasks and a programming language, LibreOffice Basic, for more complex automation. While advanced skills may be required for complex tasks, even beginners can start creating macros with practice.

Where can I find more resources to learn about LibreOffice Calc macros? 

You can find resources to learn about LibreOffice Calc macros from official documentation, tutorials, forums, books, online courses, blogs, and the LibreOffice community. Some recommended sources include the official Calc Macros Guide, YouTube tutorials, and forums like LibreOffice Community Forums.

This guide introduces LibreOffice Calc macros, emphasizing their power in automating tasks. It covers creation, execution, and shortcuts. WPS Spreadsheet is recommended as a compatible alternative, known for its templates, lightweight design, collaboration features, and stability. Instructions for enabling macros in WPS Spreadsheet and downloading WPS Office are provided. The guide emphasizes safety when enabling macros and offers a resource list for further learning.

  • 1. How to Make and Use Pivot Table in LibreOffice Calc Step-By-Step Guide
  • 2. How to Download Libreoffice Calc for Free [2024 Guide]
  • 3. How to Use LibreOffice Calc(Spreadsheet) Online for Free_ A Comprehensive Guide
  • 4. How to Print Gridlines in LibreOffice Calc(Step-By-Step Guide)
  • 5. How to Record a Macro in Word: A Comprehensive Guide
  • 6. [2024 Updated] LibreOffice Calc Using and Download Guide

add macro libreoffice calc

15 years of office industry experience, tech lover and copywriter. Follow me for product reviews, comparisons, and recommendations for new apps and software.

LibreOffice 6.2 Help

Working with VBA Macros

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic which is built into all Microsoft Office applications.

Support for VBA is not complete, but it covers a large portion of the common usage patterns. Most macros use a manageable subset of objects in the Excel API (such as the Range, Worksheet, Workbook, etc.) and the support include those objects, and the most commonly used method/properties of those objects.

Loading Microsoft Office documents with executable VBA macros

Choose LibreOffice - Preferences Tools - Options - Load/Save - VBA Properties and mark the Executable code checkbox. Then load or open your document.

Executable code

The VBA (Visual Basic for Applications) code will be loaded ready to be executed. If this checkbox is not checked, the VBA code will be commented out so it can be inspected, but will not run.

After loading the VBA code, LibreOffice inserts the statement Option VBASupport 1 in every Basic module to enable a limited support for VBA statements, functions and objects. See Option VBASupport Statement for more information.

Running VBA Macros

Run VBA macros in the same way as LibreOffice Basic macros.

Since support for VBA is not complete, you may have to edit the VBA code and complete the missing support with LibreOffice Basic objects, statements and functions.

Editing VBA Macros

VBA macros can be edited in the LibreOffice Basic IDE.

Related Topics

VBA Properties

LibreOffice Basic IDE

If this page has been helpful, you can support us!

Macros for LibreOffice Calc

TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate

  • Development
  • Documentation
  • Localization
  • Accessibility
  • General Macros
  • Macros for Writer
  • Macros for Calc
  • Macros for Impress
  • Macros for Draw
  • Macros for Base
  • Macros for Math
  • Basic/Python

The following macros can be used with LibreOffice Calc:

(See Macros/Calc/fr for more content.)

LibreOffice Basic

Refer to LibreOffice Basic Help for using Basic as a macro language.

Refer to Python Scripts Help for an introduction to Python as a macro language.

Refer to JavaScript macros - xlated from japanese - for an introduction to JavaScript as a macro language.

  • Hiding/Unhiding sheets based on cell content

add macro libreoffice calc

add macro libreoffice calc

Getting Started Guide 7.4

Chapter 11, Getting Started with Macros

Using the Macro Recorder … and Beyond

This document is Copyright © 2022 by the LibreOffice Documentation Team. Contributors are listed below. You may distribute it and/or modify it under the terms of either the GNU General Public License ( https://www.gnu.org/licenses/gpl.html ), version 3 or later, or the Creative Commons Attribution License ( https://creativecommons.org/licenses/by/4.0/ ), version 4.0 or later.

All trademarks within this guide belong to their legitimate owners.

Contributors

To this edition

To previous editions

Please direct any comments or suggestions about this document to the Documentation Team’s forum at https://community.documentfoundation.org/c/documentation/loguides/ (registration is required) or send an email to: [email protected] .

Everything you send to a mailing list, including your email address and any other personal information that is written in the message, is publicly archived and cannot be deleted.

Publication date and software version

Published January 2022. Based on LibreOffice 7.4 Community. Other versions of LibreOffice may differ in appearance and functionality.

Using LibreOffice on macOS

Some keystrokes and menu items are different on macOS from those used in Windows and Linux. The table below gives some common substitutions for the instructions in this document. For a detailed list, see the application Help.

Introduction

A macro is a set of commands or keystrokes that are stored for later use. An example of a simple macro is one that enters your address into an open document. You can use macros to automate both simple and complex tasks. Macros are very useful when you have to repeat the same task in the same way.

The simplest way to create a macro is to record a series of actions through LibreOffice’s user interface. LibreOffice saves recorded macros using the open source LibreOffice Basic scripting language, which is an implementation of the well-known BASIC programming language. Such macros can be edited and enhanced after recording using the built-in LibreOffice Basic Integrated Development Environment (IDE).

The most powerful macros in LibreOffice are created by writing code using one of the four supported scripting languages (LibreOffice Basic, Python, JavaScript, and BeanShell). This chapter provides an overview of LibreOffice’s macro facilities, mostly focused on its default macro scripting language, LibreOffice Basic. Some introductory examples are included for the BeanShell, JavaScript, and Python scripting languages. However, an in-depth discussion of how to use these languages for scripting is beyond the scope of this chapter.

Your first macros

Adding a macro

The first step in learning macro programming is to find and use existing macros. This section assumes that you have a macro that you want to use, which you may have found in a book or on the internet. For this example, the macro in Listing 1 is used. You should create a library and module to contain your macro; see “ Macro organization ” below for more information.

Listing 1 : Simple macro that says hello

Sub HelloMacro

Print "Hello"

Use the following steps to create a library that will contain your macro:

1)  Open any LibreOffice application.

2)  Go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog ( Figure 1 ).

3)  Click Organizer to open the Basic Macro Organizer dialog ( Figure 2 ) and select the Libraries tab.

4)  Set the Location drop-down to My Macros & Dialogs , which is the default location.

5)  Click New to open the New Library dialog (not shown here).

6)  Enter a library name, for example TestLibrary , and click OK .

7)  On the Basic Macro Organizer dialog, select the Modules tab ( Figure 3 ).

8)  In the Module list, expand My Macros and select your library (in the example, TestLibrary ). A module named Module1 already exists and can contain your macro. If you wish, you can click New to create another module in the library.

9)  Select Module1 , or the new module that you created, and click Edit to open the Integrated Development Environment (IDE) ( Figure 4 ). The IDE is a text editor and associated facilities that are built into LibreOffice and allow you to create, edit, run, and debug macros.

Figure 1 : Basic Macros dialog

Image1

Figure 2 : LibreOffice Basic Macro Organizer dialog, Libraries tab

Image12

Figure 3 : Basic Macro Organizer dialog, Modules tab

Image11

Figure 4 : LibreOffice Basic IDE (Integrated Development Environment) window

Image16

10)  When a new module is created, it contains a comment and an empty macro named Main , which currently does nothing.

11)  Add the new macro either before Sub Main or after End Sub . Listing 2 shows the new macro added before Sub Main .

Listing 2 : Module1 after adding the new macro

REM ***** BASIC *****

If you prefer, you can remove the Sub Main … End Sub code from the Module and leave only the HelloMacro subroutine.

12)  (Optional) To check if the macro is written as expected by the programming language, click the Compile icon on the Macro toolbar.

13)  Double-click the HelloMacro subroutine in the Object Catalog window under TestLibrary > Module 1 and click the Run icon on the Macro toolbar, or press the F5 key, to run the HelloMacro subroutine in the module. A small dialog will open with the word “Hello” displayed (see below).

14)  Click OK to close this small dialog.

Image3

15)  If no subroutine or function is selected, a dialog like the one in Figure 5 will open. Then select the macro and click Run to execute it.

16)  To select and run any macro in the module, click the Select Macro icon on the Standard toolbar or go to Tools > Macros > Organize Macros > Basic .

17)  Select a macro and then click Run .

Figure 5 : Dialog to select and run a macro

Image13

Recording a macro

When you record a macro in LibreOffice, you are actually recording the steps to perform a certain task using programming language. For example, consider that you have to repeatedly enter the same information into a document. You can copy this information after it has been entered into the document for the first time, then paste the information into the document each time you want to use it. However, if something else is copied to the clipboard, the contents of the clipboard are changed. This means that you have to re-copy this piece of information again to later paste it into the document. To overcome this problem, you can create a macro that enters this piece of information without having to copy it every time you need it.

For some cases when you want to repeatedly enter information into a document, it may be more convenient to create an AutoText. See Chapter 2, Working with Text: Basics, in the Writer Guide for more information.

Make sure macro recording is enabled by going to Tools > Options > LibreOffice > Advanced and selecting the option Enable macro recording under Optional Features . By default, this feature is turned off in LibreOffice.

1)  Go to Tools > Macros > Record Macro to start recording a macro. A small dialog with a Stop Recording button is displayed indicating that LibreOffice is recording a macro.

2)  Type the desired text you want to be entered when this macro is run. As an example, type your name.

3)  Click Stop Recording on the small dialog. This will cause Basic Macros dialog to open (similar to Figure 1 above , but with different action buttons).

4)  Open the library container My Macros .

5)  Find the library named Standard in My Macros . Note that every library container has a library named Standard .

6)  Select the Standard library and then choose an existing module in which to save the macro. Alternatively you can click New Module to create a new module to contain the newly recorded macro.

7)  In the Macro Name text box at the top left section of the dialog, type a name for the macro you have just recorded, for example EnterMyName .

8)  Click Save to save the macro and close the Basic Macros dialog.

If you followed all of the above steps, a macro named EnterMyName will have been created inside the selected module.

Whenever you create a new module in LibreOffice, a subroutine named Main is automatically added to the module.

Running a macro

1)  Go to Tools > Macros > Run Macro to open the Macro Selector dialog ( Figure 6 ).

2)  For example, select your newly created macro EnterMyName and click Run .

3)  Alternatively, go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog ( Figure 1 ), select your macro and click Run.

Figure 6 : Use the Macro Selector dialog to select and run an existing macro

Image17

Viewing and editing macros

To view and/or edit the macro that you created:

1)  Go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog.

2)  Select your new macro EnterMyName and click Edit . The Basic IDE will open and the macro EnterMyName will be shown as in Listing 3 .

This first macro is not complicated. A little explanation will significantly help you in understanding macros. The discussion starts with first line of the macro and describes features through the whole listing.

Listing 3 : Code recorded for the EnterMyname macro

sub EnterMyName

rem -------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem get access to the document

document = ThisComponent . CurrentController . Frame

dispatcher = createUnoService ( "com.sun.star.frame.DispatchHelper" )

dim args1 ( 0 ) as new com . sun . star . beans . PropertyValue

args1 ( 0 ). Name = "Text"

args1 ( 0 ). Value = "Your name"

dispatcher . executeDispatch ( document , ".uno:InsertText" , "" , 0 , args1 ())

Commenting with REM

All comments in Basic macro coding begin with REM, which stands for remark. All text after REM on the same line is ignored by the Basic interpreter when the macro is run.

As a shortcut, you can use the single quote character (') to start a comment.

LibreOffice Basic is not case-sensitive for keywords, so REM, Rem, and rem can all start a comment. If you use symbolic constants defined by the Application Programming Interface (API), it is safer to assume that the names are case-sensitive. Symbolic constants are an advanced topic not covered by this user guide and are not required when using the macro recorder in LibreOffice.

Defining subroutines with SUB

Individual macros are stored in subroutines and these subroutines begin with the keyword SUB . The end of a subroutine is indicated by the words END SUB . The code starts by defining the subroutine named Main , which is empty and does nothing. Note that the code in Listing 3 for the EnterMyName macro starts with the keyword SUB and ends with END SUB .

There are advanced topics that are beyond the scope of this user guide, but knowing about them might be of interest:

You can write subroutines that accept input values to be used inside the macro. These values are called arguments. This can only be done when you create subroutines from scratch. Recorded macros in LibreOffice do not accept arguments.

Another kind of subroutine is called a function, which is a subroutine that can return a value as a result of its work. Functions are defined by the keyword FUNCTION at the beginning. Recorded macros in LibreOffice create subroutines only.

Defining variables using DIM

You can write information on a piece of paper so that you can look at it later. A variable, like a piece of paper, contains information that can be changed and read. The DIM keyword originally stood for Dimension and was used to define the dimensions of an array. The DIM statement used in the EnterMyName macro is similar to setting aside a piece of paper to be used to store a message or note.

In the EnterMyName macro, the variables document and dispatcher are defined as the type object . Other common variable types include string, integer, and date. A third variable, named args1 , is an array of property values. A variable of type array allows a single variable to contain multiple values, similar to storing multiple pages in a single book. Values in an array are usually numbered starting from zero. The number in the parentheses indicates the highest usable number to access a storage location. In this example, there is only one value, and it is numbered zero.

Explaining macro code

The following is an explanation of the code used in the EnterMyName macro. You may not understand all the details, but the explanation of each line of code may give you some idea of how a macro works.

Defines the start of the EnterMyName macro.

Defines document as an object variable. Objects are a specific variable type with multiple fields (s ometimes they are called properties) and actions (also they are called methods). The fields can be perceived like variables (including an object) and actions like subroutines which allow us to operate with the object.

Sometimes the word service is used. A service is supplied by a type of object which are distinguished in order to point out how they are used.

Defines dispatcher as an object variable.

ThisComponent is a runtime object created by LibreOffice that refers to the current document.

CurrentController is a property referring to a service that controls the document. For example, when you type, it is the current controller that takes note of what you type. CurrentController then dispatches the changes to the document frame.

Frame is a controller property that returns the main frame for a document. Therefore, the variable named document refers to a document’s frame, which receives dispatched commands.

Most tasks in LibreOffice are accomplished by dispatching a command. LibreOffice includes a DispatchHelper service, which provides an easy way to dispatch a URL using one call instead of multiple ones and does most of the work when using dispatches in macros. The method CreateUnoService accepts the name of a service and it tries to create an instance of that service. On completion, the dispatcher variable contains a reference to a DispatchHelper .

Declares the args1 array of properties. Each property has a name and a value. In other words, it is a name/value pair. The created array has one property at index zero.

The com.sun.star.beans.PropertyValue expression is a Universal Network Objects (UNO) structure. Structures are special variable types that contain other variables united by logical basis. They can be convenient to operate with sets of heterogeneous information that should be treated as a single whole. An explanation of UNO and user structures goes far beyond the scope of this book. For more information on creating and using structures, see the LibreOffice Help system and other Basic guides.

Gives the property the name “Text” and the value “Your name”, which is the text that is inserted when the macro is run.

The dispatch helper sends a dispatch to the document frame (stored in the variable document ) with the command .uno:InsertText . The next two arguments, frame name and search flags, are beyond the scope of this book. The last argument is the array of property values to be used while executing the command InsertText .

In other words, this line of code executes the UNO command .uno:InsertText passing the value “Your Name” as the “Text” parameter.

The last line of the code ends the subroutine.

Creating a macro

When creating a macro, it is important to ask two questions before recording:

1)  Can the task be written as a simple set of commands?

2)  Can the steps be arranged so that the last command leaves the cursor ready for the next command or entering text or data into the document?

A more complex example of a macro

A common task is to copy rows and columns of data from a website and format them as a table in a text document as follows:

1)  Copy the data from the website to the clipboard.

2)  To avoid strange formatting and fonts, paste the text into a Writer document as unformatted text.

3)  Reformat the text with tabs between columns so that it can be converted into a table using Table > Convert > Text to Table .

With the two questions above in mind, inspect the text to see if a macro can be recorded to format the text. As an example of copied data, consider the text copied from the API website describing the FontWeight constants ( Figure 7 ). The first column in this example indicates a constant name and each name is followed by a space and a tab, and each line has two trailing spaces.

Figure 7 : Example of copied data

Suppose you want the first column in the table to contain the numeric value of the font weight, the second column the constant name, and the third column the text description. This task can be accomplished easily for every row except for DONTKNOW and NORMAL, which do not contain a numeric value.

Below are the steps to record this macro using keystrokes and assuming that the cursor is at the beginning of the line with the text “THIN”.

1)  Make sure macro recording is enabled by going to Tools > Options > LibreOffice > Advanced and selecting the option Enable macro recording . By default, this feature is turned off when LibreOffice is installed on your computer.

2)  Go to Tools > Macros > Record Macro to start recording.

3)  Press Ctrl+Right Arrow to move the cursor to the start of the word “specifies”.

4)  Press Backspace twice to remove the tab and the space.

5)  Press Tab to add the tab without the space after the constant name.

6)  Press Delete to delete the lower case “s” and then press Shift+S to add an upper case “S”.

7)  Press Ctrl+Right Arrow twice to move the cursor to the start of the number.

8)  Press Ctrl+Shift+Right Arrow to select and move the cursor before the % sign.

9)  Press Ctrl+C to copy the selected text to the clipboard.

10)  Press End to move the cursor to the end of the line.

11)  Press Backspace twice to remove the two trailing spaces.

12)  Press Home to move the cursor to the start of the line.

13)  Press Ctrl+V to paste the selected number to the start of the line.

14)  Pasting the value also pasted an extra space, so press Backspace to remove the extra space.

15)  Press Tab to insert a tab between the number and the name.

16)  Press Home to move to the start of the line.

17)  Press Down Arrow to move to the next line.

18)  Stop recording the macro and save the macro, see “ Recording a macro ” above .

It takes much longer to read and write the steps above than to actually record the macro. Work slowly and think about the steps as you do them. With practice you will learn how to organize the steps to create macros that can be used to automate repetitive tasks.

The generated macro code in Listing 4 has been modified to contain the step numbers in the comments to match the steps discussed above.

Listing 4 : Copying numeric value to start of the column

sub CopyNumToCol1

rem (3) Press Ctrl+Right Arrow to move the cursor to the start of “specifies”.

dispatcher . executeDispatch ( document , ".uno:GoToNextWord" , "" , 0 , Array ())

rem (4) Press Backspace twice to remove the tab and the space.

dispatcher . executeDispatch ( document , ".uno:SwBackspace" , "" , 0 , Array ())

rem (5) Press Tab to add the tab without the space after the constant name.

dim args4 ( 0 ) as new com . sun . star . beans . PropertyValue

args4 ( 0 ). Name = "Text"

args4 ( 0 ). Value = CHR$ ( 9 )

dispatcher . executeDispatch ( document , ".uno:InsertText" , "" , 0 , args4 ())

rem (6) Press Delete to delete the lower case s ....

dispatcher . executeDispatch ( document , ".uno:Delete" , "" , 0 , Array ())

rem (6) ... and then press Shift+S to add an upper case S.

dim args6 ( 0 ) as new com . sun . star . beans . PropertyValue

args6 ( 0 ). Name = "Text"

args6 ( 0 ). Value = "S"

dispatcher . executeDispatch ( document , ".uno:InsertText" , "" , 0 , args6 ())

rem (7) Press Ctrl+Right Arrow twice to move the cursor to the number.

rem (8) Press Ctrl+Shift+Right Arrow to select the number.

dispatcher . executeDispatch ( document , ".uno:WordRightSel" , "" , 0 , Array ())

rem (9) Press Ctrl+C to copy the selected text to the clipboard.

dispatcher . executeDispatch ( document , ".uno:Copy" , "" , 0 , Array ())

rem (10) Press End to move the cursor to the end of the line.

dispatcher . executeDispatch ( document , ".uno:GoToEndOfLine" , "" , 0 , Array ())

rem (11) Press Backspace twice to remove the two trailing spaces.

rem (12) Press Home to move the cursor to the start of the line.

dispatcher . executeDispatch ( document , ".uno:GoToStartOfLine" , "" , 0 , Array ())

rem (13) Press Ctrl+V to paste the selected number to the start of the line.

dispatcher . executeDispatch ( document , ".uno:Paste" , "" , 0 , Array ())

rem (14) Press Backspace to remove the extra space.

rem (15) Press Tab to insert a tab between the number and the name.

dim args17 ( 0 ) as new com . sun . star . beans . PropertyValue

args17 ( 0 ). Name = "Text"

args17 ( 0 ). Value = CHR$ ( 9 )

dispatcher . executeDispatch ( document , ".uno:InsertText" , "" , 0 , args17 ())

rem (16) Press Home to move to the start of the line.

rem (17) Press Down Arrow to move to the next line.

dim args19 ( 1 ) as new com . sun . star . beans . PropertyValue

args19 ( 0 ). Name = "Count"

args19 ( 0 ). Value = 1

args19 ( 1 ). Name = "Select"

args19 ( 1 ). Value = false

dispatcher . executeDispatch ( document , ".uno:GoDown" , "" , 0 , args19 ())

To run this macro, first place the cursor at the beginning of the line to which you want to apply the recorded steps. Then go to Tools > Macros > Run Macro, select the CopyNumToCol1 macro and click Run . Figure 8 shows the original line and the resulting line after applying the macro.

Figure 8 : Result of applying the recorded macro

Image14

Keep in mind that the steps described above will only work properly if the line follows the format we assumed while creating the macro. If you run this macro on the “DONTKNOW” and “NORMAL” lines the results will not be as expected because these two lines have different formats. Figure 9 shows the original line starting with “DONTKNOW”.

Figure 9 : Result of using the macro in a line with a different structure

Image18

Running a macro quickly

It is not convenient to repeatedly run macros using Tools > Macros > Run Macro . If you frequently need to use a certain macro, you can assign a keyboard shortcut to quickly run it. Next are the steps to assign the shortcut Ctrl+K to the CopyNumToCol1 macro.

1)  Go to Tools > Customize . This will open the Customize dialog.

2)  Select the Keyboard tab. In the Shortcut Keys section, select the Ctrl+K shortcut.

3)  In the Category section, select LibreOffice macros . Navigate this section and select the CopyNumToCol1 macro.

4)  Now click the Modify button to assign the Ctrl+K shortcut to the CopyNumToCol1 macro.

5)  Click OK to close the Customize dialog.

Now you can run the CopyNumToCol1 macro using the Ctrl+K shortcut. This is very fast and easy to configure after you get used to the macro workflow. Figure 10 illustrates the steps involved.

Figure 10 : Assigning a shortcut to a macro

Image19

Limitations of the macro recorder

The Macro Recorder has some limitations, which means that some actions may not be recorded. A deeper knowledge of LibreOffice internal workings helps to understand how and why the macro recorder will work. The main cause of these limitations is the dispatch framework and its relationship to the macro recorder.

Dispatch framework

The purpose of the dispatch framework is to provide uniform access to components (documents) for commands that usually correspond to menu items. Using File > Save , the shortcut keys Ctrl+S , or clicking the Save icon on the Standard toolbar are all commands that are translated into the same “dispatch command”.

The dispatch framework can also be used to send “commands” back to the user interface (UI). For example, after saving a new document, the list of recent files is updated.

A dispatch command is text, for example .uno:InsertObject or .uno:GoToStartOfLine . The command is sent to the document frame and this passes on the command until an object is found that can handle the command.

How the macro recorder uses the dispatch framework

The macro recorder records the generated dispatches. The recorder is a relatively simple tool to use and the same commands that are issued are recorded for later use. The problem is that not all dispatched commands are complete. For example, inserting an object generates the following code:

dispatcher . executeDispatch ( document , ".uno:InsertObject" , "" , 0 , Array ())

It is not possible to specify what kind of object to create or insert. If an object is inserted from a file, you cannot specify which file to insert.

If while recording a macro you use Tools > Options to open and modify configuration items, the generated macro does not record any configuration changes. In fact, the generated code is commented so it will not even be run.

rem dispatcher.executeDispatch(document, ".uno:OptionsTreeDialog", "", 0, Array())

If a dialog is opened, a command to open the dialog is likely to be generated. Any work done inside the dialog is not usually recorded. Examples of this include macro organization dialogs, inserting special characters, and similar types of dialogs. Other possible problems using the macro recorder include things such as inserting a formula, setting user data, setting filters in Calc, actions in database forms, and exporting a document to an encrypted PDF file. You never know for certain what will work unless you try it. For example, the actions from the search dialog are properly captured.

Other options

When the macro recorder is not able to solve a specific problem, the usual solution is to write code using the LibreOffice objects. Unfortunately, there is a steep learning curve for these LibreOffice objects. It is usually best to start with simple examples and then increase the scope of macros as you learn more. Learning to read generated macros is a good place to start.

Macro organization

In LibreOffice, macros are grouped in modules, modules are grouped in libraries, and libraries are grouped in library containers. A library is usually used as a major grouping for either an entire category of macros, or for an entire application. Modules usually split functionality, such as user interaction and calculations. Individual macros are subroutines and functions. Figure 11 shows an example of the hierarchical structure of macro libraries in LibreOffice.

Figure 11 : Macro Library hierarchy

Image15

Go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog ( Figure 1 above ). All available library containers are shown in the Macro From list. Every document is a library container, capable of containing multiple libraries. The application itself acts as two library containers, one container for macros distributed with LibreOffice called LibreOffice Macros , and one container for personal macros called My Macros .

The LibreOffice Macros are stored with the application runtime code, which may not be editable to you unless you are an administrator. This helps protect these macros because they should not be changed and you should not store your own macros in the LibreOffice Macros container.

Unless your macros are applicable to a single document, and only to a single document, your macros will probably be stored in the My Macros container. The My Macros container is stored in your user area or home directory.

If a macro is contained in a document, then a recorded macro will attempt to work on that document, because it primarily uses ThisComponent for its actions.

Every library container contains a library named Standard . It is better to create your own libraries with meaningful names than to use the Standard library. Not only are meaningful names easier to manage, but they can also be imported into other library containers whereas the Standard library cannot.

LibreOffice allows you to import libraries into a library container, but it will not allow you to overwrite the library named Standard . Therefore, if you store your macros in the Standard library, you cannot import them into another library container.

Just as it makes good sense to give your libraries meaningful names, it is prudent to use meaningful names for your modules. By default, LibreOffice uses names such as Module1 , Module2 , and so on.

As you create your macros, you must decide where to store them. Storing a macro in a document is useful if the document will be shared and you want the macro to be included with the document. Macros stored in the application library container named My Macros , however, are globally available to all documents.

Macros are not available until the library that contains them is loaded. However, in contrast to other libraries, the Standard and Template libraries are automatically loaded. A loaded library is displayed differently from a library that is not loaded. To load the library and the modules it contains, double-click on the library.

Where are macros stored?

LibreOffice stores user-specific data in a folder inside the user’s home directory. The location is operating system specific. Go to Tools > Options > LibreOffice > Paths to view where other configuration data are stored. User macros written in Basic are stored in LibreOffice\4\user\basic . Each library is stored in its own directory inside the basic directory.

For casual use, it is not necessary to understand where macros are stored. If you know where they are stored, however, you can create a backup, share your macros, or inspect them if there is an error.

Exporting macros

The LibreOffice Basic Macro Organizer dialog allows you to export macro libraries so that they can be reused and shared with other people. To export a macro library:

1)  Go to Tools > Macros > Organize Macros > Basic and the click the Organizer button.

2)  Click the Libraries tab and choose which library you want to export.

3)  Click Export and then select Export as BASIC Library (note that you cannot export the Standard library).

4)  Choose where you want to save the library and click Save .

When a library is exported, LibreOffice creates a folder containing all files related to the library. Figure 12 shows an example of how a library named TestLibrary with a single module called Module1 would be exported.

Figure 12 : Folder containing the exported library

Image20

Importing macros

The LibreOffice Basic Macro Organizer dialog allows you to import macro libraries into your document as well as creating, deleting, and renaming libraries, modules, and dialogs.

1)  On the Libraries tab, select the library container to use and then click Import to import macro libraries.

2)  Navigate to the directory containing the library to import ( Figure 13 ). There are usually two files from which to choose, dialog.xlb and script.xlb . It does not matter which of these two files you select; both will be imported. Macros can be stored in libraries inside LibreOffice documents. Select a document rather than a directory on disk to import libraries contained in a document.

Figure 13 : Navigating to a macro library

graphics5

3)  Select a file and click Open to continue and open the Import Libraries dialog ( Figure 14 ).

Figure 14 : Choose library import options

graphics6

4)  Select the following options for importing libraries:

If no options are selected, the library is copied to your user macro directory. However, if the library you are importing has the same name and you are importing into the same location, it will not be copied.

Select Insert as reference (read-only) if you want to use the library as reference, but not import it into the document. When a library is used as a reference, it remains in its current location and is fully functional, but cannot be modified in the Basic IDE.

Select Replace existing libraries if the library you want to import has the same name and you want to replace the existing library.

5)  Click OK to import the macro library you selected.

You cannot export/import the library named Standard .

On Linux, LibreOffice-specific files are stored in the user’s home directory inside the .config folder. Directories and files with names beginning with a dot may be hidden and not shown in a normal file selection dialog. When using LibreOffice dialogs, rather than the operating system’s specific dialogs, type the name of the desired directory in the Name field.

Downloading macros to import

You can find macros created by the community to download from the internet. Some macros are contained in documents, some as regular files that you need to import, and some are published as text and need to be copied and pasted into the Basic IDE. See “ Adding a macro ” above on how to add macros to your macro library and “ Viewing and editing macros ” above on how to edit macros using the Basic IDE.

Some macros are available as free downloads on the Internet (see Table 1 ).

Table 1 . Places to find macro examples

How to run a macro

Although you can use Tools > Macros > Run to run all macros, this is not efficient for frequently used macros. LibreOffice offers many ways for you to quickly run your macros.

In addition to assigning a shortcut to macros, as seen in “ Running a macro ” above , you can also link macros to a toolbar icon, menu item, event, or a button embedded in a document. When choosing a method, it is also good to ask questions such as:

Should the macro be available for only one document or globally for all documents?

Is the macro for a specific document type, such as a Calc document?

How frequently will the macro be used?

The answers will determine where to store the macro and how to make it available. For example, you will probably not add a rarely used macro to a toolbar. To help determine your choices, see Table 2 .

Table 2 . Where to store a macro

Toolbars, menu items, and keyboard shortcuts

To add a menu item, keyboard shortcut, or toolbar icon that calls a macro, use the Customize dialog, which contains pages to configure menus, keyboard shortcuts, toolbars, and events. To open this dialog, go to Tools > Customize . Use of the Menus , Toolbars , Context Menus , and Keyboard tabs are covered in Chapter 13, Customizing LibreOffice.

Whenever something happens in LibreOffice, it is called an event. For example, opening a document, changing status of modified, or moving the mouse cursor are all events. LibreOffice allows events to trigger the execution of a macro; the macro is then called an event handler. Full coverage of event handlers is well beyond the scope of this chapter, but a little knowledge can accomplish much.

Be careful when you configure an event handler. For example, assume that you write an event handler that is called every time that a document is modified, but you make a mistake so the event is not properly handled. One possible result is that your event handler will force you to kill LibreOffice.

1)  Go to Tools > Customize to open the Customize dialog and select the Events tab ( Figure 15 ). The events in the Customize dialog are related to the entire application and specific documents.

2)  In the Save In drop-down, select LibreOffice , or a specific document from the menu to save your event.

3)  A common use is to assign the Open Document event to call a specific macro. The macro then performs certain setup tasks for the document. Select the desired event and click Macro to open the Macro Selector dialog (similar to Figure 6 above but with different action buttons).

4)  Select the desired macro and click OK to assign the macro to the event. The Events tab will show that the event has been assigned to a macro.

Many objects in a document can be set to call macros when events occur. The most common use is to add a control, such as a button, into a document. Even double-clicking on a graphic opens a dialog with a Macros tab that allows you to assign a macro to an event.

Figure 15 : Events tab in Customize dialog

Image2

Using extensions

An extension is a package that can be installed into LibreOffice to add new functionality. Extensions can be written in almost any programming language and may be simple or sophisticated. Extensions can be grouped into types, for example:

Calc add-ins, which provide new functionality for Calc, including new functions that act like normal built-in functions.

New components and functionality, which normally include some level of User Interface (UI) integration such as new menus or toolbars.

Chart add-ins with new chart types.

Linguistic components such as spelling checkers.

Document templates and images.

Although individual extensions can be found in several places, there is currently an extension repository at: https://extensions.libreoffice.org/ and some documentation at https://libreplanet.org/wiki/Group:OpenOfficeExtensions/List .

For more about obtaining and installing extensions, see Chapter 13, Customizing LibreOffice.

Writing macros without the recorder

The examples covered so far in this chapter were created using the macro recorder and the dispatcher. You can also write macros that directly access the objects that comprise LibreOffice if you are confident in writing computer code. In other words, you can create a macro that directly manipulates a document using more advanced programming logic.

Directly manipulating LibreOffice internal objects is an advanced topic that is beyond the scope of this chapter. A simple example, however, demonstrates how this works.

An example of a macro for Writer

The sample code in Listing 5 is a simple example of a macro created without the recorder that adds the string “Hello” at the end of a Writer document.

To add this macro to a library, follow the steps below:

1)  Go to Tools > Macros > Organize Macros > Basic .

2)  In My Macros , navigate to the library where you want the macro to be created. In this example, consider the library TestLibrary .

3)  Select one of the modules already available in the library, for example Module1 . If you want to create a new module, click Organizer and then add the new module.

4)  With the module selected, click Edit . This will open the Basic IDE window and show the code for the macros implemented in the selected module.

5)  Enter the code in Listing 5 into the module.

Listing 5 : Append the text “Hello” at the end of to the current document

Sub AppendHello

Dim sTextService$

REM ThisComponent refers to the currently active document.

oDoc = ThisComponent

REM Verify that this is a text document.

sTextService = "com.sun.star.text.TextDocument"

If NOT oDoc . supportsService ( sTextService ) Then

MsgBox "This macro only works with a text document"

REM Get the view cursor from the current controller.

oCurs = oDoc . currentController . getViewCursor ()

REM Move the cursor to the end of the document.

oCurs . gotoEnd ( False )

REM Insert text "Hello" at the end of the document.

oCurs . Text . insertString ( oCurs , "Hello" , False )

An example of a macro for Calc

One powerful way to extend the functionalities of LibreOffice Calc is to write macros that automate repetitive tasks. You can use the Basic language to write macros that can do tasks ranging from simple cell handling and formatting to advanced data manipulation.

As a simple example, consider you want to analyze a range of cells to determine if all values are between 0 and 100. Additionally, values ranging from 50 to 100 should be marked in light green whereas cells with values that are greater than or equal to 0 and less then 50 should be marked in light red. If any values outside the allowed range of 0 to 100 are found, a warning message should be displayed and cells should be marked in light gray. Listing 6 shows the Basic code for such a macro.

Listing 6 : Calc macro to format ranges based on values

Sub FormatRangeBasedOnValue

'Gets the current selection

Dim oRange as Object , oCell as Object

Set oRange = Thiscomponent . getCurrentSelection ()

'Checks if the selected range is a single range

If Not oRange . supportsService ( "com.sun.star.sheet.SheetCellRange" ) Then

MsgBox "This macro applies only to single ranges."

' Number of columns and rows in selection

Dim nCols as Long : nCols = oRange . Columns . getCount ()

Dim nRows as Long : nRows = oRange . Rows . getCount ()

Dim col as Long , row as Long

Dim cellValue as Long

Dim isError as Boolean : isError = False

' Iterate over all cells in the range

For col = 0 To nCols - 1

For row = 0 to nRows - 1

Set oCell = oRange . getCellByPosition ( col , row )

cellValue = oCell . getValue ()

If cellValue >= 50 and cellValue <= 100 Then

' Sets background to light green

oCell . CellBackcolor = RGB ( 144 , 238 , 144 )

ElseIf cellValue >= 0 and cellValue < 50 Then

' Sets background to light red

oCell . CellBackcolor = RGB ( 255 , 127 , 127 )

' Sets background to light gray

oCell . CellBackcolor = RGB ( 220 , 220 , 220 )

isError = True

' Displays a message indicating that there were errors

If isError Then

MsgBox "Some cells outside the range 0 to 100 were marked in light grey"

To add this macro to a library, follow the steps described in “ An example of a macro for Writer ” above . To run this macro, first create a new Calc sheet and add some numeric values in a range of cells. Then, select the cells and use one of the methods described in “ How to run a macro ” above to execute it.

Figure 16 shows an example of the macro being run on a set of cells. Because some of the cells have values that are not between 0 and 100, the message box in Figure 17 will also be displayed.

Figure 16 : Cell values after being formatted by the macro

Image21

Figure 17 : Message box presented if some values are not between 0 and 100

Image22

The ScriptForge library

Macro programmers frequently need to perform tasks such as creating and opening files, accessing form controls, reading data from databases embedded in Base documents, and so forth. The objective of the ScriptForge library is to make it easier to execute such commands without having to learn the required LibreOffice APIs (Application Programming Interfaces) and commands, which may be difficult for casual programmers.

The ScriptForge library is organized into a set of services, each of which provides methods and properties related to a specific topic. For example, the Dialog service provides access to dialogs available in script modules and the Database service allows to execute SQL commands in Base documents. The example in Listing 7 shows a macro written in Basic using the ScriptForge library that opens a Calc document, creates a new sheet named NewSheet , and inserts the string “Hello” into cell A1 . The macro also saves and closes the document.

Listing 7 : Macro using the ScriptForge library

Sub CreateSheetExample

' Loads the ScriptForge library

GlobalScope . BasicLibraries . LoadLibrary ( "ScriptForge" )

' Instantiates the UI service

Dim ui as Object , myDoc as Object

ui = CreateScriptService ( "UI" )

' Opens the file "myfile.ods"

Set myDoc = ui . OpenDocument ( "/home/user/Documents/myfile.ods" )

' Inserts a new sheet named "NewSheet"

myDoc . InsertSheet ( "NewSheet" )

' Inserts the string "Hello" into cell "A1" of the new sheet

myDoc . SetValue ( "NewSheet.A1" , "Hello" )

' Shows the sheet "NewSheet"

myDoc . Activate ( "NewSheet" )

' Saves the document

myDoc . Save ()

' Closes the document

myDoc . CloseDocument ()

As seen in the example, the ScriptForge library provides straightforward commands to execute commands, which seeks to simplify the creation of macros.

To learn more about the ScriptForge library, visit LibreOffice’s online Help at https://help.libreoffice.org/7.4/en-US/text/sbasic/shared/03/lib_ScriptForge.html?DbPAR=BASIC . Each of the 26 supported services have been extensively documented and examples are provided for both Basic and Python programming languages.

UNO Object Inspector

LibreOffice has an extensive API that can be used by macro programmers to automate almost any aspect of its applications. However, one of the main challenges for programmers is to discover object types as well as their supported services, methods, and properties.

The UNO Object Inspector can help macro developers inspect objects and discover how they can be accessed and used in macros. This feature is available in Writer, Calc, Impress, and Draw. To enable it, go to Tools > Development Tools . The Object Inspector will be opened at the bottom of the user interface, as shown in Figure 18 .

Figure 18 : UNO Object Inspector opened in a Writer document

Image23

The left portion of the Object Inspector consists of the Document Object Model (DOM) navigator, which allows the user to navigate through all the objects in the document. When an object is selected, information about the object are shown in the right portion of the Object Inspector window:

The names of all implemented interfaces.

The names of all services supported by the object.

The names and types of all properties available in the object.

The names, arguments and return types of all methods that can be called by the object.

Instead of inspecting objects using the DOM navigator , it is possible to directly inspect the currently selected object in the document by toggling the Current Selection button.

For example, suppose you want to change the background color of the text selected in a Writer document. You can select a portion of text, open the Object Inspector and toggle the Current Selection button. Now, inspect the object properties in search of a property that matches the desired effect. Figure 19 shows the CharBackColor property selected, which is the property used to set text background color.

Figure 19 : Using the Object Inspector to find a property

Image24

Now it is possible to write a macro using this property to change the background color of the selected text. Listing 8 shows the code for this macro.

Listing 8 : Macro that changes the background color of a text range

Sub ChangeTextBGColor

Dim oSel as Object

Set oSel = ThisComponent . getCurrentSelection ()

oSel ( 0 ). CharBackColor = RGB ( 255 , 127 , 127 )

Note that in Writer it is possible to have multiple ranges selected at once, hence oSel(0) accesses the first range of text.

Overview of Python, BeanShell, and JavaScript macros

Many programmers may not be familiar with LibreOffice Basic, so LibreOffice supports macros written in three other languages that may be more familiar: Python, BeanShell, and JavaScript.

Macros are organized in the same way for all four scripting languages. The LibreOffice Macros container holds all the macros that are supplied in the LibreOffice installation. The My Macros library container holds your macros that are available to any of your LibreOffice documents. Each document can also contain your macros that are not available to any other document.

When you use the recording facility, the macros are created in LibreOffice Basic. To use the other available scripting languages, you must write the code yourself.

When you select to run a macro using Tools > Macros > Run Macro , LibreOffice displays the Macro Selector dialog. This dialog enables selection and running of any available macro, coded in any of the available languages ( Figure 20 ).

When you edit a macro using Tools > Macros > Edit Macros , LibreOffice displays the LibreOffice Basic IDE. This dialog enables selection and editing of any available LibreOffice Basic macro, but not macros in other languages.

Figure 20 : Macro Selector dialog

Image4

The component model used in LibreOffice is known as Universal Network Objects or UNO. LibreOffice macros in any scripting language use a UNO runtime application programming interface (API). The XSCRIPTCONTEXT interface is provided to macro scripts in all four languages, which provides some means of access to the various interfaces which they might need to perform an action on a document.

If you want to learn more about the LibreOffice API as well as UNO objects, refer to the official API documentation at https://api.libreoffice.org/

Python macros

Python is a high-level, general-purpose programming language that was first released in 1991.

When you select Tools > Macros > Organize Macros > Python , LibreOffice displays the Python Macros dialog ( Figure 21 ).

Figure 21 : Python Macros dialog

Image10

Facilities to edit and debug Python scripts are not currently integrated into the standard LibreOffice user interface. However, you can use any Python editor to create scripts and then copy these files into the Scripts folder in your home directory. For more information, refer to “ Where are macros stored? ” above .

Listing 9 presents an example of a Python macro that writes the text “Hello World from Python” into cell A1 of the first sheet in a Calc spreadsheet.

Listing 9 : Sample Python macro

def HelloWorld ():

doc = XSCRIPTCONTEXT . getDocument ()

cell = doc . Sheets [ 0 ][ 'A1' ]

cell . setString ( 'Hello World from Python' )

The Alternative Python Script Organizer (APSO) extension makes it easier to edit and organize Python scripts, in particular when embedded in a document. Using APSO you can configure your preferred source code editor, start the integrated Python shell and debug Python scripts. Visit https://gitlab.com/jmzambon/apso to download APSO and learn more about how to use it.

To learn more about Python scripting in LibreOffice, you can refer to the Wiki “Macros/Python Basics”, where you will find in-depth explanations and examples on how to get started with Python scripting. To learn more, visit https://wiki.documentfoundation.org/Macros/Python_Basics .

BeanShell macros

BeanShell is a Java-like scripting language that was first released in 1999.

When you select Tools > Macros > Organize Macros > BeanShell , LibreOffice displays the BeanShell Macros dialog ( Figure 22 ).

Figure 22 : BeanShell Macros dialog

Image5

Click the Edit button on the BeanShell Macros dialog to access the BeanShell Debug Window ( Figure 23 ).

Figure 23 : BeanShell Debug Window

Image6

Listing 10 is an example of a BeanShell macro that inserts the text “Hello World from BeanShell” in cell A1 of the active Calc spreadsheet.

Listing 10 : Sample BeanShell macro

import com.sun.star.uno.UnoRuntime ;

import com.sun.star.sheet.XspreadsheetView ;

import com.sun.star.text.XText ;

model = XSCRIPTCONTEXT . getDocument ();

controller = model . getCurrentController ();

view = UnoRuntime . queryInterface ( XSpreadsheetView . class , controller );

sheet = view . getActiveSheet ();

cell = sheet . getCellByPosition ( 0 , 0 );

cellText = UnoRuntime . queryInterface ( XText . class , cell );

textCursor = cellText . createTextCursor ();

cellText . insertString ( textCursor , "Hello World from BeanShell" , true );

JavaScript macros

JavaScript is a high-level scripting language that was first released in 1995.

When you select Tools > Macros > Organize Macros > JavaScript , LibreOffice displays the JavaScript Macros dialog ( Figure 24 ).

Figure 24 : JavaScript Macros dialog

Image7

Click the Edit button on the JavaScript Macros dialog to access the Rhino JavaScript Debugger ( Figure 25 ). Detailed instructions for using this tool can be found on Mozilla’s website at https://developer.mozilla.org/en-US/docs/Mozilla/Projects/Rhino/Debugger .

Listing 11 is an example of a JavaScript macro that inserts the text “Hello World from JavaScript” in cell A1 of the first sheet in a Calc spreadsheet.

Listing 11 : Sample JavaScript macro

importClass(Packages.com.sun.star.uno.UnoRuntime);

importClass(Packages.com.sun.star.sheet.XspreadsheetDocument);

importClass(Packages.com.sun.star.container.XindexAccess);

importClass(Packages.com.sun.star.table.XcellRange);

importClass(Packages.com.sun.star.table.Xcell);

documentRef = XSCRIPTCONTEXT.getDocument();

spreadsheetInterface = UnoRuntime.queryInterface(XSpreadsheetDocument, documentRef);

allSheets = UnoRuntime.queryInterface(XIndexAccess, spreadsheetInterface.getSheets());

theSheet = allSheets.getByIndex(0);

Cells = UnoRuntime.queryInterface(XCellRange,theSheet);

cellA1 = Cells.getCellByPosition(0,0);

theCell = UnoRuntime.queryInterface(XCell,cellA1);

theCell.setFormula("Hello World from JavaScript");

Figure 25 : Rhino JavaScript Debugger

Image8

Finding more information

Numerous resources are available that provide help with writing macros. Use Help > LibreOffice Help, or press the F1 key, to open the LibreOffice help pages. The upper left corner of the LibreOffice help system contains a drop-down list that determines which help set is displayed. To view the help for Basic, choose Basic from this list.

Included material

Many excellent macros are included with LibreOffice. Use Tools > Macros > Organize Macros > Basic to open the LibreOffice Basic Macros dialog. Expand the Tools library in the LibreOffice library container. Inspect the Debug module; some good examples include WritedbgInfo (document) and printdbgInfo (sheet).

Online resources

The following links and references contain information regarding macro programming:

https://wiki.documentfoundation.org/Macros

https://ask.libreoffice.org/ (a Q&A site where volunteers answer questions related to LibreOffice)

https://wiki.documentfoundation.org/Documentation/Other_Documentation_and_Resources (look in Programmers section for BASIC Programmers’ Guide and Developers’ Guide; the latter contains a detailed explanation)

http://forum.openoffice.org/en/forum/ (Apache OpenOffice community forum; volunteers answer questions about LibreOffice as well)

Printed and ebook materials

Andrew Pitonyak published a free online book OpenOffice.org Macros Explained , which is a great reference for learning the Basic language used in both LibreOffice and OpenOffice. You can download the PDF version at https://www.pitonyak.org/OOME_3_0.pdf .

Dr. Mark Alexander Bain published the book Learn OpenOffice.org Spreadsheet Macro Programming . See https://www.packtpub.com/openoffice-ooobasic-calc-automation/book .

Roberto Benitez's Database Programming with OpenOffice.org Base & Basic is also a great source to learn more about macro programming.

Ask LibreOffice

How to best encrypt/protect calc sheet?

I have a few Calc sheets with sensitive data, so I’m thinking how to best protect them. Password protection in Calc only protects for changes or to be read only, so that doesn’t fit. I’ve used 7zip to password protect file, and then open it from Archive manager, which works fine, and I believe is also relatively safe and hard decrypt. I’m wondering though if it makes temp files when you open file, and/or makes some autosave/backup files somewhere in temp folders, and if those get left behind? I’ve also noticed it wouldn’t run macros. (Probably because I’ve set to be safe folder in my documents folder, but when it gets uncompressed it probably runs from some temp file) Any other suggestions? I mostly use them in Fedora Linux. I’m not sure if there is an easy solution to encrypt a file from within Fedora and then it will ask you for a password if you want to encrypt it again. I don’t need to encrypt entire filesystem, when it’s a question of a few files, which I don’t even open that often. I’m surprised there is no option in LO to encrypt file for even see it. AFAIK when you chose ready only protection with reasonable password it will actually encrypt the file, so it’s not easy to make it writable, so routine is already there. Or there is already an option to view protect directly in LO Calc, and I just didn’t find it.

Help is a good way to find information. This older question and answer should help, it applies to any LibreOffice program, How do I encrypt a Writer document

Impressum (Legal Info) | Datenschutzerklärung (Privacy Policy)   Content on this site is licensed under a Creative Commons Attribution Share Alike 3.0 license.

IMAGES

  1. LibreOffice Calc

    add macro libreoffice calc

  2. Macros e Menus em LibreOffice Calc

    add macro libreoffice calc

  3. LibreOffice Calc Macro To Put Cursor In The Last Row

    add macro libreoffice calc

  4. LibreOffice Calc Macro Basic How To Copy and Paste any data

    add macro libreoffice calc

  5. Ten useful LibreOffice Macro Recipes · Prahlad Yeri

    add macro libreoffice calc

  6. Libreoffice Calc-Cómo grabar una macro básica #macros #visualbasic #libreofficecalc

    add macro libreoffice calc

VIDEO

  1. Libreoffice Calc Sheet Menu

  2. Libreoffice calc formula

  3. Macro

  4. Libre Office calc Insert Menu

  5. If formula in Libreoffice calc || Libreoffice calc If function in hindi

  6. MACRO EM LIBREOFFICE

COMMENTS

  1. Chapter 12 Calc Macros

    The most powerful macros in Calc are created by writing code using one of the four supported scripting languages (LibreOffice Basic, BeanShell, JavaScript, and Python). This chapter provides an overview of Calc's macro facilities, mostly focused on its default macro scripting language, LibreOffice Basic.

  2. Chapter 13 Getting Started with Macros

    Use the following steps to create a library that will contain your macro: Open any LibreOffice application. Go to Tools > Macros > Organize Macros > Basic to open the Basic Macros dialog ( Figure 1 ). Click Organizer to open the Basic Macro Organizer dialog ( Figure 2) and select the Libraries tab.

  3. Chapter 13 Getting Started with Macros

    Introduction A macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that "types" your address. The LibreOffice macro language is very flexible, allowing automation of both simple and complex tasks.

  4. Using Calc Functions in Macros

    Using Calc Functions in Macros In addition to the native BASIC functions, you can call Calc functions in your macros and scripts and set Calc functions in cell formulas. Calling Internal Calc functions in Basic Use the CreateUnoService function to access the com.sun.star.sheet.FunctionAccess service. Example:

  5. PDF Chapter 12 Calc Macros

    Tip. Use Tools > Options > LibreOffice > Advanced and select the Enable macro recording option to enable the macro recorder. Open a new spreadsheet. Enter numbers into a sheet. Figure 1: Enter numbers. Select cell A3, which contains the number 3, and copy the value to the clipboard. Select the range A1:C3.

  6. Writing a Macro in LibreOffice Calc: Getting Started

    Go to the option from the menu: Tools ==> Macros ==> Organize Macros ==> LibreOffice Basic. Below 'LibreOffice basic macros' window will open. Give your desired name in the macro name box and click New. You can use any name you want. For this tutorial, I have used hello_world. Once you have clicked the New button, the macro editor will open.

  7. Basic Macros

    New Module Saves the recorded macro in a new module. Organizer Opens the Macro Organizer dialog, where you can add, edit, or delete existing macro modules, dialogs, and libraries. Related Topics Assigning Scripts in LibreOffice Recording a Macro

  8. Macro Programming in LibreOffice Calc with BASIC

    Macro Programming in LibreOffice Calc with BASIC - Introduction Ackfee 401 subscribers Subscribe Subscribed 474 46K views 3 years ago In this video I will show you the basics of BASIC, the...

  9. Macro Guides

    BASIC IDE BASIC Dialogs BASIC Events BASIC Files BASIC Runtime Library BASIC Runtime parameters BASIC Structured Data Types Andrew Pitonyak Latest Macro Guide LibreOffice and OpenOffice.org share the same Basic macro language and API (Application Programming Interface).

  10. Working with Dialog Controls in LibreOffice Calc using Macro

    To add a Dialog in LibreOffice Calc, select from menu: Tools -> Macros -> Organize Dialogs…. Once above options is chosen, below LO Basic Macro Organizer would open. On the Dialog Tab, click New button. On the New Dialog pop-up , give a name to your dialog. For this tutorial, lets keep it as default Dialog1.

  11. Calling Calc Functions in Macros

    LibreOffice 7.0 Help. Module. Language. ... Calling Calc Functions. In addition to the native BASIC functions, you can call Calc functions in your macros and scripts. Calling Internal Calc functions in Basic. Use the CreateUNOService function to access the com.sun.star.sheet.FunctionAccess service. ... Calling Add-In Calc Functions in BASIC.

  12. Chapter 5, Getting Started with Calc

    However, if you are familiar with Excel you may wish to change the default syntax in Calc by going to Tools > Options > LibreOffice Calc > Formula and choosing Excel A1 or Excel R1C1 in the Formula syntax drop-down menu. For more information on formula syntax, see Chapter 7, Using Formulas and Functions, in the Calc Guide. Macros

  13. User-Defined Functions

    You can apply user-defined functions in LibreOffice Calc in the following ways: You can define your own functions using the Basic-IDE. This method requires a basic knowledge of programming. You can program functions as add-ins. This method requires an advanced knowledge of programming. Defining A Function Using LibreOffice Basic

  14. Writing a Macro in LibreOffice Calc

    Step 1: Click on Tools > Macros > Record Macro. Start with Data menu. Step 2: Enter a name for the macro and click on Start Recording. Write Macro name. Step 3: Perform the actions that you want to automate. Step 4: Click on Tools > Macros > Stop Recording to save the macro. How to run a macro in LibreOffice Calc:

  15. How do I set a button to execute a macro in calc?

    How to edit the Properties and Events of a button: Right click on the button - Control. Finally switch OFF the edit mode. newbie-02 January 18, 2021, 4:58am #2 hello @MikeMcClain46, view - toolbars - form controls, add 'push button', edit it's properties and assign your macro somewhere at 'release button',

  16. Support for VBA Macros

    Working with VBA Macros. Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic which is built into all Microsoft Office applications. Support for VBA is not complete, but it covers a large portion of the common usage patterns. Most macros use a manageable subset of objects in the Excel API (such as the Range ...

  17. Recording a Macro and Adding a Button in LibreOffice 4

    A demonstration of how to enable to the macro recording option in LibreOffice Calc and how to record a macro and add a button to run it.

  18. Calc assign macro to menu

    Calc assign macro to menu English vbas96 October 16, 2012, 9:14pm #1 I've created a macro and stored it in a LibreOffice v.3.5.6.2 Calc module (OS - Windows Vista). I have a Menu for the macro created in the document, but cannot successfully "assign" the macro to the menu. The Path I use is >Tools >Customize >Menus.

  19. Macros for LibreOffice Calc

    (See Macros/Calc/fr for more content.) LibreOffice Basic. Refer to LibreOffice Basic Help for using Basic as a macro language. Python. Refer to Python Scripts Help for an introduction to Python as a macro language. JavaScript. Refer to JavaScript macros - xlated from japanese - for an introduction to JavaScript as a macro language. To Add

  20. Assign macro to toolbar

    1 Like Assign multiple macros to a single button/group toolbar jimk June 2, 2018, 11:45am #2 Tools → Customize, Toolbars tab. Category: Macros (at the bottom of the list) Double-click the tree in the Function pane and browse to the macro to be added. Click the Add Item right arrow to add to a toolbar. AlexKemp closed May 13, 2021, 9:39am #3

  21. Chapter 11, Getting Started with Macros

    Introduction A macro is a set of commands or keystrokes that are stored for later use. An example of a simple macro is one that enters your address into an open document. You can use macros to automate both simple and complex tasks. Macros are very useful when you have to repeat the same task in the same way.

  22. Recording a macro (keystrokes) in Calc v 7.6

    Lupp February 21, 2024, 6:54pm #3. You need to Enable macro recording under >Tools>Options>LinreOffice>Advanced. Calc and Witer will support the recorder then. The functionality is limited. The recorder is not a key logger, and even less a mouse-action-logger. What happens depends on the situation found when the macro was recorded.

  23. How to add cell elements of 2 columns in libreOffice Calc Macro?

    In your code, you must perform addition for each of the lines; group operations on range values are not supported. Sub updateSTOCK () Dim oSheets As Variant Dim oSheet3 As Variant Dim oCellRangeByName As Variant Dim oDataArray As Variant Dim i As Long oSheets = ThisComponent.getSheets () oSheet3 = oSheets.getByIndex (3) oCellRangeByName ...

  24. Hiding macros from view or tampering in Calc

    Hiding macros from view or tampering in Calc. February 20, 2024, 10:56pm. We are able to protect sheets from accidental or deliberate contamination but I don't think we can hide our macros from at least being read as you can with MS Excel. If I'm right then what is the point of passwords if all a person has to do is look it up on your macro.

  25. How to convert links into hyperlinks in bulk in Calc?

    Go to Options, check this checkbox. Follow the replacements recommended in the previous link. Get working formulas with the HYPERLINK () function. Go back to Options and uncheck the checkbox. Now the function is written in your language and still works. 1 Like. michaldybczak February 22, 2024, 3:27pm #5.

  26. How to best encrypt/protect calc sheet?

    I have a few Calc sheets with sensitive data, so I'm thinking how to best protect them. Password protection in Calc only protects for changes or to be read only, so that doesn't fit. I've used 7zip to password protect file, and then open it from Archive manager, which works fine, and I believe is also relatively safe and hard decrypt. I'm wondering though if it makes temp files when ...