Part B — Electronic Spreadsheet (Advanced)

Many spreadsheet tasks are performed repeatedly. For example, entering the same school name, address or contact information with a particular format, or applying the same formula to different sheets.

Performing such tasks manually again and again can take time and may lead to typing or formatting mistakes.

Definition of Macro

A macro is a single instruction that executes a set of instructions.

These instructions may be a sequence of commands or keystrokes that can be used any number of times later.

• Automates repeated tasks.
• Saves time.
• Reduces repeated manual work.
• Helps maintain standardisation.
• Reduces typing mistakes in repeated tasks.

By default, the macro recording feature is turned off when LibreOffice is installed.

Therefore, before recording a macro, macro recording must be enabled.

Steps to Enable Macro Recording

1. Click Tools on the main menu bar.
2. Select Options.
3. Select LibreOffice.
4. Select Advanced.
5. Under Optional Features, locate Enable macro recording.
6. Put a check mark on Enable macro recording.

Once macro recording has been enabled, the Record Macro option becomes available under the Tools → Macros menu.

Procedure

1. Click Tools → Macros → Record Macro.
2. Start performing the actions that you want to record.
3. When recording starts, a small alert is displayed.
4. Click Stop Recording when the required actions are completed.
5. The Basic Macros dialog box opens.
6. Select the object where the macro has to be saved.
7. Give the macro a suitable name.
8. Click Save.

The Macro Recorder records keyboard and mouse actions performed during the recording.

However, some actions are specifically not recorded.

Actions NOT Recorded

• Opening of windows.
• Actions carried out in another window from where recording was started.
• Window switching.
• Actions unrelated to spreadsheet contents.
• Changes made in the Options dialog.
• Actions in Macro Organizer.
• Customizing actions.

After recording is stopped, the Basic Macros dialog appears.

The recorded macro can then be saved in the required object/library.

Important Points

• A Standard Library is present by default when a spreadsheet is created and saved.
• The default macro name is Main.
• By default, Main is saved in the Standard Library → Module1.
• The macro name can be changed.

Library

A Library is a collection of modules.

Module

A module contains macros. Therefore:

The chapter specifies rules that must be followed while naming a Macro, Module or Library.

Examples

Names such as the following are invalid:

A previously created macro can be run whenever the same task is required again.

Procedure

1. Type the required heading or data in the worksheet.
2. Click Tools → Macros → Run Macro.
3. The Macro Selector dialog box opens.
4. Select the required library and module.
5. Select the required macro from the Macro Name list.
6. Click Run.

The actions recorded by a macro are stored as instructions in a programming language called BASIC.

Viewing Macro Code

The generated macro code can be viewed through:

The selected macro's associated code can be viewed in the IDE.

The code of a macro begins with Sub followed by the name of the macro and ends with End Sub.

Macros can also be organised manually by creating libraries and modules.

Opening Basic Macro Organiser

1. Click Tools → Macros → Organize Macros → LibreOffice Basic.
2. The LibreOffice Basic Macro dialog opens.
3. Click Organizer.
4. The Basic Macro Organizer opens.

Creating a Library

To create a new library containing modules:

The newly created library can then be used to organise modules and macros.

The chapter introduces the IDE for viewing and editing macro code.

The IDE displays the programming code associated with the macro.

The chapter also explains that a macro can be used as a function in Calc.

A macro used as a function can accept arguments/values and return a result/value.

Difference from Normal Macro

Arguments / Values

Arguments are the input values supplied to a macro function.

Result / Value

The return value is the result produced by the macro function.

The textbook demonstrates a macro as a function using a calculation for the number of tiles required.

The macro function accepts required values and returns the calculated result.

The chapter also covers execution of a module from the IDE.

• Macro recording is OFF by default.
• Macro recording can be enabled from Tools → Options → LibreOffice → Advanced.
• The option to enable is Enable macro recording.
• The default macro name is Main.
• Default storage is Standard Library → Module1.
• A library is a collection of modules.
• A module contains macros.
• Macro instructions are written in BASIC.
• Macro code begins with Sub and ends with End Sub.
• IDE stands for Integrated Development Environment.
• A macro as a function accepts arguments/values and returns a result/value.
• F5 can be used to execute a module from the IDE.
• Macro names cannot contain spaces.
• Special characters are not allowed except underscore (_).

The textbook's Check Your Progress for Chapter 5 focuses on the following areas:

• Macro definition.
• Uses of macros.
• Actions not recorded by Macro Recorder.
• Difference between LibreOffice Macros and My Macros.
• Macro as a function.
• Rules for naming macros.
• Advantages of macros.
• Default library.
• IDE full form.
• Arguments and return values.
• Macro Organizer.
• Sub and End Sub.