Macros in libreoffice calc. How to enable macros in LibreOffice Calc

I have been using Linux for about 10 years. About 5 years ago I started using OpenOffice, and 3 years ago - LibreOffice. To my surprise, and even shame, I don't have macros written for Calc. But for Excel, enough of them have accumulated during this time, starting from simple forms and formulas and ending with powerful add-ons for enterprises that automate the preparation of information. Today I vowed to slowly begin to study the package and its capabilities, in which I spend quite a lot of time.

For most people, a macro is something incomprehensible, and, perhaps, an unnecessary invention. For them, it not only does not optimize the work, but also increases it. This opinion is caused by a lack of understanding of the principles of using macros. Macros are used when you need to perform an action many times. Most often, this is the same type of document processing (complex formatting, selections), sometimes forms for filling, formulas, calculations for graphs ... Based on this and on our preferences, we choose the way of storing our macros:

• global(or add-on) - always available when you open the program;
• formulaic- available when opening a specific template;
• local- available when working only in a specific document.

The latter is used less often, mainly in very complex interactive documents.
Today, only small companies that do not have IT specialists in their staff use office suites (it does not matter LibreOffice or MS Office) as they are "out of the box". In most cases, the office suite is only the base on which the add-ons are deployed, and the employee of the company often does not even suspect how many small but very convenient things have been done for him until he leaves or moves to another company.

Learning how to write macros in Excel is advised to start with the Record Macro tool. Indeed, writing down the pieces of actions, it is enough to simply imagine the surface level of the language and the API that you will need to use in the future. Also, sometimes, in order to quickly create a simple macro, as if you "throw it by writing it down" and then polish it, bringing it to mind in the code editor.

Connecting the Record Macro tool in LibreOffice 4.1

In LibreOffice 4.1, the default function “ Record Macro"Is disabled. Therefore, the first thing to do is enable it: Service → Options (Tools → Options) expand the LibreOffice group and at the very bottom in the item " Extended capabilities» (« Advanced") Check the box against" Enable macro recording (limited)» (« Enable macro recording (limited)»).

After that, in your menu: Tools → Macros (Tools → Macros) the menu item “ Record Macro» (« Record Macro»).

Using the Macro Recorder in LibreOffice 4.1

To demonstrate the tool “ Recording macros»Here is a simple example:
1. Let's open a new Calc document and save it to disk under a name convenient for you;
2. Select the cell A1;
3. Let's enable macro recording Tools → Macros → "Record Macro" (Tools → Macros → "Record Macro"). A panel will appear with a single button " Finish Recording» (« Stop recording»);
5. Select the cell B1 and click " Finish Recording» (« Stop recording»);
6. The following window will open:

We are making a macro with access only in this document, therefore, we will open the item with the name of the document (I have article.ods, you will have this name under which you saved the document) and select the only present library Standard... There are no modules in it yet, so let's create it.
7. Press the button " Create module» (« New Module") And in the window that opens, enter the name of the module.

By default, the module is created with an empty macro called Main... Let's enter in the field “ Macro name» (« Macro name") I entered the desired name" Main") And click" Write down» (« Save») To save the macro we recorded. In my case, a warning will appear that there is already such a name for the macro.

The macro is recorded, and if we now save the document, the macro will be saved with it. This means that every time we open this document, we can use this macro.

Launching and Editing a LibreOffice 4.1 Macro

There are two ways to run a macro in LibreOffice 4.1.
First, open the window " Run Macro»: Tools → Macros → "Run Macro"(Tools → Macros → "Run Macro ..."), select the required macro and press " Run».

The invite has been received. :)

Hello hackers!
Today I want to tell you about the use of macros in LibreOffice.

Foreword
Every day at home and at work, we use office applications to perform any tasks. It often happens that you specialize in one job and end up performing the same tasks: insert the same text, format it using a large number of hotkeys. In all these cases, you can save your valuable work time by automating some of your work.
Later in the article I will tell you about using macros in LibreOffice.

What are macros and why are they?

Macro is a sequence of certain actions that can be recorded using the usual menu. When you select a macro recording, the actions you perform are automatically saved as script code. And in your further work, it will already be performed independently, exactly repeating your actions in the program. A macro can be as simple as it is very complex - it all depends on what kind of result you need to achieve. The main criteria for macros is to be powerful and easy to use at the same time. LibreOffice macros meet exactly these requirements. Let's try it in practice.

To use the package LibreOffice, You need to have a preinstalled operating system Linux, Windows or MacOS, as well as the LibreOffice package installed (you can install it, for example, from the ppa repositories, as described in this topic)

Macro management

Before we start creating our own macro, we need to get acquainted with the macro management tool. We go along the way Tools - Macros - Organize Macros - LibreOffice Basic Macros(Tools - Macros - Manage Macros - LibreOffice Basic Macros):

In the left column of the window, we can see a list of installed default macros. By clicking on any of them, you can edit or delete it.
The main task of the window- select the macro you need, assign it a button on the toolbar or associate it with an event. Assigning a button to a macro allows you to quickly execute the ones you use most often.

I want to note that creating a macro in automatic mode (recording) requires at least a little knowledge of the language for writing macros, since sometimes we need to edit it. In order to see what the macro looks like, select the macro we need and press Edit(Edit). Below is an example of what the macro looks like in the source code:

Let's take a simple example. We have some text already inserted into the document and we need to make a title for it, since our text will be used in business correspondence.
Our future macro should be able to:
- place the text at the top center of the page;
- enter text that will be used as a title.
After we create a macro, we will assign it a button on the toolbar. After that, one click of the mouse will be enough to insert the title into the document.
Let's follow the sequence of steps.

Step 1.
Open a text document. Let's move on Tools - Macros - Record Macro... A small "Record Macro" window will appear with a single "End Macro" button ( Stop recording):

Step 2.
Let's create a title for the text. Let's click on the button "Center alignment"("Centered"), so that our future text is exactly in the center of the document. Now we write the text of the title itself. You should not set different formats for it (font or bold / italic / underlined), since the macro does not remember such actions.

Step 3.
After the title creation is complete, click on the "End Macro" button in the "Record Macro" window. The Macro Organizer window will appear immediately. Give the new macro a name (for example PostHead). Now you can save it to wherever you want (for example, the folder " My macro").

Step 4.
Now we just need to add a button on the toolbar for the macro PostHead... This process is not as simple as it would seem at first glance.
We open LibreOffice Basic Macros, push the button " Assign"(" Assign "). The following window will appear Customize, in which we should go to the tab Toolbars and make sure that the "Toolbar" field is "Standard" ( Standart) .

Click the "Add" button in this window ( Add). Another window will appear - "Add commands" ( Add commands).
In the list of the left window "Categories" ( Category) we find " LibreOffice Macros"(at the very bottom of the list). Open it and get to our macro. As soon as we find it, it remains to perform the last and simplest step. Drag the Letterhead macro with the mouse to the place on the toolbar where we would like to see it.
Everything, coped with the task.
Now all we need to do is use the button PostHead, which we added to the panel. For example, open a new document and click the button PostHead... Our macro will create a title with exactly the text that we "assigned" to it and will place it at the top of the document, exactly in the center.
Isn't it convenient?

Outcome
Of course, in my example, we created a very simple macro. But with the above tools, you can create very complex macros as well. And not only in Writer, but also in all other applications of the package LibreOffice(Spreadsheet, Presentations, etc.). Now that you know how to create macros, you can easily create your own to make your job easier.
The purpose of this article is to learn the basics of working with a macro recording tool.

Sometimes you need to work with the text or process the text in any complex way and the usual means provided by the interface are not enough for this. In addition, it is sometimes necessary to carry out the same sequence of routine actions, which sometimes takes a lot of time, but I would like to reduce the sequence of these actions to one button press. Writer allows you to create special macros, which are, in fact, text processing procedures written in a programming language, in our case, the Basic language acts as a programming language. At the same time, having a set of all standard operators inherent in high-level programming languages, it is possible to access objects of the text editor Writer, open documents, functions for opening documents, all objects of this document, including pictures, paragraphs, headers and footers, selected text, lists, words, letters, fonts, etc ..

2.1. Objects and Classes.

What is an Object. From the point of view of the real world, an object is something material that exists and has properties and behavior in the real world, often objects have some common properties, thanks to which we refer each object to some class of objects. For example - a car, there are different concrete implementations and objects of cars, but the general class is a car that has four wheels, is able to drive and is driven by a driver. The same can be said about text or a document, a document is an object that contains a text object, and a text object contains words, paragraphs, letters, text is edited, changed, displayed, a document is created and saved. We perform all these actions with these objects using the functions of the editor, but we can call these functions using the algorithmic language.

If you have already worked with programming languages ​​and wrote programs, then you know that in any programming language there is a set of operators or instructions with which you can write instructions or a program that the processor can understand and execute. There is a set of standard operators with which you can write almost any algorithm, of any complexity - this is a branch, a loop, a linear sequence of operator execution, arithmetic operations and the ability to access variables and write some values ​​or results of logical or arithmetic expressions in them. Usually in high-level languages ​​they try to avoid the complex work with memory inherent in machine languages, a standard assignment operation is introduced, which allows a certain - variable - symbolic sequence to be assigned a value. Roughly speaking, using this character sequence in expressions, you work with what is contained in this variable as in a box. You can describe the assignment operation as follows: Into a glass with the name - St1, we pour milk from a mug Cr1 and say, pour me milk in St1 from Cr1. Thus, you will be poured with what is contained in Cr1. It's the same with the variable. Let's say Val1 = 20; Val2 = 30; Val3 = Val1 + Val2; then Val3 will contain the value 50. You know that a variable can only contain data of a certain type, and not everything in a row (although there is a special variant data type when the type of a variable can be determined during program execution). After all, we can store names (strings) and numbers, and objects. Therefore, each variable is assigned some kind of data type, or domain or scope of the values ​​that it can take. Typically in programming languages, these are integer types, real, string, character, boolean, enumerated, set, complex numbers, record type, or structures. So what is a variable object, it is a kind of reference to an object, which is a complex data structure that, among other things, can contain methods for working with this data and object, as well as protect the data and restrict or allow access to it.

An object variable is a variable that contains other objects, properties and actions performed on an object, an object is a specific implementation of a certain class (a class is a description of a certain set of objects with the same properties). Typically, properties and functions of complex data types (such as classes) are accessed by writing the name of an object variable, followed by a dot in the name of the function and or property of that object.

2.2. Variables and Objects in Basic

To declare a variable, specify the dim keyword and then a comma-separated list of variables, the word as and the type of the variable.

Dim a, b as integer - declaration of a variable of an integer type. Dim s as string - declaration of a variable of a string type.

Dim mass () as integer - declaration of a dynamic one-dimensional array of integer type. Redim mass (100) - change the length of the array and set it to 100.

Dim desk as com.sun.star.frame.Desktop is a variable of the desktop type of the UNO unified network model, this variable can refer to objects of the Desktop type.

In the Basic language, you can access variables that are references to objects, these can be text objects, paragraphs, tables displayed on the window screen, they have a set of properties and methods for working with these objects. The object model can be any, as well as its implementation, for example, the Microsoft Office package has its own object model, the LibreOffice or OpenOffice package has its own, therefore the objects and the way of interacting with these objects in these different packages are different.

2.3. Basic Operators

For loop operator.

For index = n1 to n2 Rem loop body

The Index variable runs through values ​​from n1 to n2 with incrementing s (increasing by s), in this case s can be a variable or constant of an integer type, square brackets indicate that the construction is optional, if it is not specified then the step is 1.

For example val = 0

For xyz = 4 to 50 step 4 val = val + xyz

The algorithm calculates the sum of values ​​from 4 to 50 with a step of 4, that is, the sum of 4, 8, 12, 16 ...

up to 48 into the variable val. val1 = 0

For aval = 1 to 50 val1 = val1 + aval next aval

In this case, the sum of integers from 1 to 50 is calculated.

While loop statement, do while the condition is met. The statements inside the loop are repeated as long as the condition is met.

While<условие>Wend operators

Example: While i

The loop is executed as long as the variable i is less than N. The conditional If statement,

if<условие>then

<последовательность операторов если условие выполняется>end if

Example: if I is less than 100 (if the condition is met) then increase I by 1, otherwise decrease by 1.

If i<100 then i=i+1

else i = i-1 end if

2.4. Procedures and functions.

Functions and procedures are separate blocks of statements that can be called in the main program or subroutine, usually a function or procedure is called in a program by specifying its name and parameters passed to it, after the execution of the function statements, control is returned to the program or subroutine that called it and execution of statements following the function or procedure begins. Obviously, the purpose of procedures and functions is not to write the same code every time for frequently repeated operations performing a certain logically completed action. At the same time, inside functions and procedures, it is possible to use your own local variables, which can have the same names as variables in other procedures and functions and in the main program. At the same time, from outside the procedure, we cannot change the local variables of the function. A typical use of procedures and functions is that we pass some values ​​to the function, on the basis of which this function performs a series of actions and calculates some result. The main difference between procedures and functions is that the function name is associated with some type of return data, roughly speaking, the function can be used in expressions, for example, arithmetic or logical, in conditional statements and loops. The procedure is called outside of any expression.

The function returns the sum of two numbers passed as actual parameters to the function from an external program

Function sum (a, b as integer) as integer Sum = a + b

Using the sum function in a program. Dim x as integer

x = 2 x = x + sum (x, 4) * 2

An example of a procedure that allows you to add two numbers, the value is returned in the formal parameter with, when calling the procedure, it should not be a constant, but should be a variable of the integer type

Sub sum (a, b, c as integer)

Dim c as integer Call sum (2,2, c)

2.5. Creating a Macro in LibreOffice

To create a macro in LibreOffice, select Service + Macros + Macro Management + LibreOffice Basic (Tools + Macros + Organize Macros). This will display the window shown in the figure below (Figure 19). In order for the macro to be saved in the document itself, you must select your document, select the set of standard modules "standard" and then click "create", then you must enter the name of the module. After creating a module, you can select it, select the Main macro in the window on the right and click Edit. Or, after creating the module (Module1), write a new name for the macro in the Macro Name field and click create (Figure 20).

Figure 19 - Window for creating and editing macros

Figure 20 - An example of creating a new macro MyMainMacros

As a result of creating and editing a macro, the BASIC editor window appears, the figure shows an example with two macros, of course there can be more of them and they can have input parameters.

Figure 21 - Basic editor and two macros

In LibreOffice, as already noted, the object model is slightly different than in Microsoft Office, in LibreOffice Basic the so-called UNO unified network object model is used. Below is an example of an openoffice macro that increases the font size of each paragraph.

Dim Doc As Object

Dim Enum As Object

Dim TextElement As Object

" StarDesktop - the main object accessible from the macro

" creating an enumeration object

Enum = Doc.Text.createEnumeration

" loop through all text elements While Enum.hasMoreElements TextElement = Enum.nextElement

" checking if the current block is a table

If TextElement.supportsService ("com.sun.star.text.TextTable") Then

MsgBox "The current block contains a table"

I publish any materials in two formats - odt and pdf... The latter is convenient for distribution. Export to pdf carried out by standard means of LibreOffice through the dialog “ File -> Export to PDF". In the process of proofreading the manual and correcting errors in it, you often have to re-save the document. And so as not to manually export the document every time, I thought it would be nice if this happens automatically when saving odf file. And knowledge of the LibreOffice macro language is absolutely unnecessary in this case.

The task is solved using the macro recording function. It is not available by default. To enable it go to " Tools -> Options" In chapter " LibreOffice"Select the item" Extended capabilities"And check the box next to" Enable Macro Recording».

After restarting LibreOffice in the " Tools → Macros"The item" Record Macro».

After clicking " Tools -> Macros -> Record Macro"A window with a button" End recording».

The bottom line is simple:

1. Enable macro recording
2. Perform the required action while recording is in progress. In my case, I just performed the export to PDF via “ File -> Export to PDF»
3. Click the " End recording»

That's it, the export to PDF macro has been recorded, you just need to save it.

For convenience, I have created a module “ Export_to_PDF" in library " Standard"And saved my macro in this module.

Now all that remains is to assign the triggering of the macro to a specific event. In our case, to save the document. We go to " Service -> Settings"To the" Developments».

Attention! The macro was written in LibreOffice 4.4.3.2 and Windows XP.

Start LibreOffice Calc. Select Tools -> Macros -> Manage Macros -> LibreOffice Basic

In the Libre Office BASIC Macros window, in the "Macro name" field, write "Main". In the "Macro from" field, select "My macros" - "Standard" and click the "Create" button. See Figure 1.

Figure 1. LibreOffice Basic Macros Window

In the New module window set the name "Module1" and press the "OK" button. See Figure 2.

Figure 2. Creating a new module

Figure 3. LibreOffice Basic Macro Editor

We have created the Module1 module with the main procedure Main. To create a dialog, select the Tools -> Macros -> Dialogue Management ... menu item in the LibreOffice Basic Macro Editor.

Select the "Dialogues" tab. In the "Dialogue" section, select "My Dialogues" -> "Standard" and press the "Create" button. See Figure 4.

Figure 4. Creating a macro dialog

In the New Dialog window set the name "Dialog1" and press the "OK" button. See Figure 5. Then the "Close" button of the LibreOffice Basic Macro Management window.

Figure 5. Creating a new dialog

Next, in the LibreOffice Basic macro editor window, double-click on our newly created dialog with the name "Dialog1", see number 1 in Figure 6. After that, the visual dialog editor will open, see number 2 in Figure 6.

Figure 6. LibreOffice Basic Dialog Editor

Now drag a visual element named label and a visual element button to our dialog. Visuals can be dragged from the visuals panel, see number 3 in Figure 6.

If you select the Label visual element, a set of properties for this element will open. You can customize the element through these properties. Name the lblNow element and leave the text blank. For the button element we will name OKButton and the text will be OK. See numbers 1,2,3 in figure 7.

Figure 7. Adding visual elements in the LibreOffice Basic Dialog Editor

As I wrote above, the Module1 module with the main procedure Main has been created (see Figure 8), but it has not been completed yet.

Figure 8. Adding the source code of the macro

The Main procedure is the main one, in which we load a dialog box named Dialog1, these are the following lines of code:

DialogLibraries.LoadLibrary ("Standard")
Dlg = CreateUnoDialog (DialogLibraries.Standard.Dialog1)

We form a string with the current date and time, these are the following lines of code:

Dim strNow As String
strNow = Format (Now (), "yyyy-mm-dd")
strNow = strNow + Format (Time (), "hh: mm")

We assign a string with the date and time to the visual element a label named lblNow, these are the following lines of code:

Dim clblNow As Object
clblNow = Dlg.getControl ("lblNow")
clblNow.Text = strNow

With the last line of code in the Main procedure, we execute a dialog box named Dialog1:

Dlg.Execute ()

There is one more procedure, its name is OKButton_execute, this is an event procedure, it is executed at the moment of pressing the button named OKButton, and it closes the dialog box named Dialog1, this is the following line of code:

Dlg.endExecute ()

Now assign the procedure named OKButton_execute to the button named OKButton as an event on "Mouse Key Release". To do this, open the visual editor of the Dialog1 dialog, see number 1 in Figure 9. Select the Button named OKButton and go to the "Events" tab, see number 2 in Figure 9. In the "Release the mouse button" event, specify the procedure named OKButton_execute by clicking the button with three dots, see number 3 in Figure 9.

Figure 9. Assigning an event to the OK button

We have done everything necessary to run the macro. First, let's see what the macro does for us. Under the number 2 in Figure 10, you see the created dialog box with the displayed current date and time. Clicking the OK button closes the dialog box.

1. Run a macro from the LibreOffice Basic macro editor. Open our code in the LibreOffice Basic macro editor and execute it by pressing the F5 key or by pressing the macro run button, see number 1 in Figure 10.

2. Running a macro from the "Select Macro" dialog box, see number 1 in Figure 11. In the dialog box, we need to select our macro and click the "Run" button, see number 2 in Figure 11.

Figure 11. Running a macro from the "Select Macro" dialog

3. Run a macro from menus and submenus in LibreOffice Calc or LibreOffice Writer, see number 0 in Figure 12. To create menus and submenus, select the "Tools" menu item and the "Options" submenu, see numbers 1 and 2 in Figure 12. In the dialog box "Settings" on the "Menu" tab, click the "New ..." button, enter the name of our menu (for example, "Macros"), see number 3 in Figure 12. To create a submenu of our "Macros" menu, click the "Add ..." (see number 4 of figure 12.).

Figure 12. Running a macro from menus and submenus in LibreOffice Calc or LibreOffice Writer

Then, in the "Add Commands" dialog box, select the command that will be executed when our submenu is selected (see numbers 1 and 2 in Figure 13), i.e. we select our macro named Main.

Figure 13. Adding a command for the submenu

Rename our submenu by selecting the "Rename ..." menu item of the "Change" button of the "Settings" dialog box of the "Menu" tab (see numbers 1,2,3,4 in Figure 14). Name the submenu "Date Window Macro".

Figure 14. Renaming a submenu

4. Running a macro in LibreOffice Calc or LibreOffice Writer by pressing a keyboard shortcut. To do this, open the "Settings" dialog and select the "Keyboard" tab (see number 1 in Figure 15). Then we select the command, which is our macro named "Main" (see number 2 in Figure 15). Then we select a free key combination and assign them to our command, i.e. a macro named Main (see numbers 3,4,5 in Figure 15).

Figure 15. Assigning a shortcut key to run a macro

5. Launching a macro in LibreOffice Calc or LibreOffice Writer by clicking the button on the toolbar (see number 4 in Figure 16). To create a button on the toolbar, select the "Service" menu item and the "Settings" submenu, see number 1 in Figure 16. In the "Settings" dialog box, on the "Toolbars" tab, click the "New ..." button, enter the name of our toolbar (for example, "Macro panel"), see number 2 of Figure 16. To create a button on the toolbar, click the "Add ..." button (see number 3 of Figure 16).

Figure 16. Running a macro from the toolbar

Then, in the "Add Commands" dialog box, select the command that will be executed when you click on our button on our toolbar (see numbers 1 and 2 in Figure 13), i.e. we select our macro named Main.

Rename our button on our toolbar by selecting the "Rename ..." menu item of the "Change" button of the "Options" dialog box of the "Toolbars" tab (see numbers 1,2,3,4 in Figure 17). Name the button "Macro window with date".

Figure 17. Renaming a toolbar button