LESSON – 5
USING MACROS IN SPREADSHEET NOTES
1. INTRODUCTION
✓ A macro is used to automate repeated tasks that are always performed
in the same way over and over again.
✓ Macros are a set of stored functions used to automate processes
repeatedly.
✓ They are tools which can be used to perform most of the redundant
tasks with relative ease.
2. Recording a Macro
✓ A macro is a single instruction that executes a set of instructions.
✓ These set of instructions can be a sequence of commands or
keystrokes that can be used for any number of times later.
✓ By default the macro recording feature is turned off when
LibreOffice is installed on our computer.
✓ Macro recording can be enabled by selecting,
Tools > Options > LibreOffice > Advanced.
✓ Observe the optional features.
✓ There are two options which are not check marked.
✓ Put the check mark on the option “Enable macro recording”.
3. Steps to record a Macro
✓ Click on Tools > Macros > Record Macro.
✓ Now start taking actions that will be recorded.
✓ Click on “Stop Recording” button to stop the recording of actions.
✓ Basic Macros dialog window open to save the Macro.
✓ Select the object in the save Macro to list box.
✓ Type the name of the Macro and click on save button.
NOTE:
✓ By default, the name of the macro is Main and is saved in the
Standard Library in Module1.
✓ A Library is a collection of modules which in turn is a collection of
Macros.
✓ If all the macros will be given the same name then they will
overwrite the previous Macro created by that name.
✓ The Macro recorder work only in Writer and Calc.
4. Following actions are not recorded in Macro
✓ Opening of windows
✓ Actions carried out in another window than where the recording was
started.
✓ Window switching
✓ Actions that are not related to the spreadsheet contents. For ex, changes
made in the options dialog, macro organizer, customizing.
✓ Selections are recorded only if they are done by using the keyboard, not
by the mouse.
5. Rules for naming a Macro, Module or a Library
✓ Begin with a letter
✓ Not contain spaces
✓ Not contain special characters except for (underscore).
6. Running a Macro
To run a macro we need to perform the following steps.
✓ Tools > Macros > Run Macro
✓ Macro selector dialog box will open.
✓ Select the Library that contains the macro then select the Macro under
“Macro Name”.
✓ Click on Run to run the Macro
7. Code of a Macro
✓ The action recorded by a Macro is recorded as instructions in a
programming language called BASIC.
✓ It is also possible to view and thus edit the code of a macro.
✓ But remember, it is advised to edit a macro only if you have
knowledge of the language.
✓ We can view the code generated for the macros by going to
Tools > Macros > Edit Macros
✓ The code of the macro begins with sub followed by the name of
the macro and ends with End sub.
8. Creating and organising a simple Macro
✓ Click on Tools > Macros > Organize Macros > Basic.
✓ Basic Macro dialog window open.
✓ Click Organizer to open the Basic Macro organizer dialog.
✓ Click on Library > New
✓ Click on Module tab and then New to create Module to store
macro.
NOTE:
The module can be executed from the IDE by either clicking the Run
button or Pressing F5.
9. Macro as a Function
✓ Suppose we need to perform the same calculation again and again on
different sheets and there is no predefined function for it.
✓ In such a situation it will be convenient to create a macro that performs
the calculations.
✓ It will save our effort of remembering and typing the formulas.
✓ It is possible to do so if we use Macro as a function.
✓ Instead of writing instructions in between sub and End sub, we can write
instructions in between Function and End Function.
✓ A function is capable of accepting arguments or values.
10. Passing function without Parameters
SYNTAX:
Function <function Name> ()
<Function Name> () = <Value>
End Function
Ex:
Function Total ()
Total () = 10 + 20
End Function
11. Passing Function with Parameters
SYNTAX:
Function <Function Name> (optional <name>)
Function code
End Function
Ex:
1) Function double (optional A)
Double = A * 2
End Function
2) Function square (optional A)
Square = A * A
End Function
12. Steps to do Sort Macro
✓ Tools > Macros > Record Macro
✓ Select Range
✓ Data > Sort
✓ Select sorting category
✓ Stop Recording
✓ Save Macro
✓ Run Macro
