Macros & Basic with OpenOffice Calc

By Remy Lentzner

This book focuses on the macros you can record with OpenOffice CALC. A macro is actually a BASIC procedure that you can modify in a specific programming environment. It enables you to create customized applications to control the objects interaction in the spreadsheet accurately. You will learn how to develop a dialogue box to manage information easily. 

BASIC programming is relatively simple to understand and all the exercises of this book will help you to achieve manual actions automatically.


ABOUT THE AUTHOR

Rémy Lentzner has been an IT trainer since 1985. Specialized in mastering office automation tools, he supports companies in the professional training of their employees. Self-taught, he has several computer books to his credit.

• Language: English
• Publisher: Remylent
• Release date: Oct 20, 2022
• ISBN: 9782490275816

Book preview

Chapter 1

Macros with CALC

This chapter deals with macros inside the CALC spreadsheet. You will learn how to create, save and execute them to make your manual actions automatic. A macro is also a BASIC procedure that you can modify in a programming environment. You will study the language and its various features.

1.1 General information about macros

A macro or macro command is a sequence of keystrokes that performs an operation on one or more parts of the spreadsheet.

For instance, to hide a column, you place the pointer in a column and you perform the actions Format / Column / Hide. If you consider that this operation is often repeated in your daily work, you can memorize this sequence of manual actions using a macro.

Another example is the printing of a part of a table. If you have to print a specific group of rows every month, you can record the whole manual process.

The macro feature makes it easier to perform repetitive tasks. You can record as many macros as you like.

With OpenOffice, a recorded macro automatically creates a suite of code in the BASIC language.

Figure 1.1 shows an INCOME.ODS document containing some accounting data and formulas.

Figure 1.1 : Formulas inside a document

Workshop : Creating a macro to format the whole table with a Calibri font and a size of 10.

1.1.1 Recording a macro

The following shows how to create a macro:

Select the cells.

Tools / Macros

Record Macro

Figure 1.2 : Recording the tasks and waiting to stop

Perform all the operations manually to record the macro.

Click on the Stop Recording button to stop the macro.

Figure 1.3 shows the dialogue box that displays the existing macros, their locations and the different buttons than can help you to manage macros.

Figure 1.3 : The macros management

Click on the New Module button.

Enter a module name then finish with the OK button.

Close the dialogue box. The creation of the macro is completed.

Enter the name of the macro in the Macro name box then finish with the Save button.

A macro is always stored in a module that is a macros container.

If a module already exists, it is not necessary to create a new module. Select the module you are interested in before saving the new macro.

After stopping the macro, OpenOffice will display the OpenOffice BASIC Macros dialogue box in which you specify a macro name and a particular location.

You can save a macro in several places:

In the My Macros library. Here, you can use the macros for other documents.

In the OpenOffice Macros library. These pre-existing macros are used by OpenOffice.

In the INCOMES.ODS document. The macros saved here are only available in this document.

In brief, you can store macros in a public container, a system container or a local container.

1.1.2 Running a macro

Once the macros have been created, you can run it with the following:

Tools / Macros / Run macro

Select the library.

Choose the macro name then click on the Run button.

1.1.3 Modules and procedures

A procedure is the structure that is automatically created when you record a macro. It starts with the keyword Sub and ends with the keyword End Sub. A procedure is always stored in a module, i.e. a container of procedures. When the macro is completed, OpenOffice will save it in a procedure called main if you don't rename the macro.

You can always edit a macro with the following:

Tools / Macros / Organize Macros

OpenOffice BASIC

Figure 1.4 : Editing a macro

Select the macro to edit.

Edit

OpenOffice displays the BASIC programming environment