Chapter 5 Using Macros in Spreadsheet PDF

Using Macros in Spreadsheet 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. A sequence of actions such as keystrokes and clicks can be recorded...

Using Macros in Spreadsheet 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. A sequence of actions such as keystrokes and clicks can be recorded and then run as per the requirement. By default the macro recording feature is turned off when LibreOffice is installed in the computer. Macro recording can be enabled using the Tools option on the main menu bar by selecting Tools >Options >LibreOffice > Advanced. Put the checkmark on the option ‘Enable macro recording”. Record Macro option found under Tools>Macros is visible now. The Macro records all the keyboard and mouse actions except the following actions. 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 example, changes made in the Options dialog, macro organizer, customizing. Selections are recorded only if they are done by using the keyboard, not when the mouse is used. The macro recorder works only in Calc and Writer. Follow the steps given below to record a macro. Step 1. Click on Tools > Macros and then click on the Record Macro option. Step 2. Now start taking actions that will be recorded. Step 3. Once you click on Record Macro option, recording of actions starts and a small alert will be displayed. Clicking on “Stop Recording” button will stop the recording of actions. Step 4. This will open the Basic Macros dialog window to save and run the created macro Step 5. To save the macro, first select the object where you want the macro to be saved in the Save macro to list box. Step 6. The name of the macro by default is Main and is saved in the Standard Library in Module1. You can change the name of the macro. Step 7. Click on Save button Note: 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. Rules for naming a Macro, Module or a Library: A Macro/Module/Library name should : 1.Begin with a letter 2. Not contain spaces 3. Not contain special characters except for _ (underscore) Running a Macro To run a macro perform the following steps. Step 1. Click Tools > Macros > Run Macro to open the Macro Selector dialog box. Libreoffice Macros library is provided by LibreOffice and contains modules with pre recorded macros and should not be changed. My Macros contain macros that we write/add to LibreOffice. Step 3. Select the library and module in the Library list. Step 4. Select the macro in the Macro name list. Step 5. Click Run to run the macro. 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 it is advised to edit a macro only if you have knowledge of the language. To view the code generated for the macros—Tools>Macros>Edit Macros. Chose the Macro name from the object Catalog and the associated code will be visible. Note: The code of a macro begins with Sub followed by the name of the Macro and ends with End Sub. Do not make any changes to the code unless you are aware of the language. Creating and Organising a SimpIe Macro The recorded macro is internally stored as instructions written in a programming language that are executed when the macro is executed or run. We selected a library or module to store our recorded macro, similarly while creating a macro, either create a new library/module or edit an existing module stored in a library. Steps to organize the macro. Step 1. Click on Tools > Macros > Organize Macros > LibreOffiee Basic to open the LibreOffice Basic Macro dialog window Step 2. Click Organizer to open the Basic Macro Organizer Dialog Step 3. Click Library>New to create library to store Macro Step 4. Click Module tab and New to create Module to store macro The Module can be executed from the IDE by either clicking the Run button or pressing F5. Steps to create a simple Macro using BASIC programming instructions that will display Hello in a dialog box stored in the Standard Module. Macro as a Functions 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 formula. 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. It can perform operations on the arguments, perform calculations and return the result. Select Tools > Macro > Organize Macros » Libreoffice Basic to open the LibreOffice Basic Macros dialog to create functions. Check your progress A. Answer the following questions 1. What is a Macro? List any 2 real life situations where they can be used. 2. List the actions that are not recorded by a macro. 3. How is LibreOffice Macros Library different from my Macros? 4. Differentiate between predefined function in Calc and Macros as a function 5. List the rules that should be kept in mind while naming a macro. 6. Give any one advantage of macros. B. State whether true or false. 1. Macro is a group of instructions executing a single instruction. 2. Once created, Macro can be used any number of times. 3. By default, the Macro recording feature is turned on. 4. It is not possible to stop recording of a Macro. 5. Every Macro should be given a unique name. 6. A macro once created can be edited later. C. Fill in the blanks 1. _______library is automatically loaded when the document is opened. 2. IDE stands for__________. 3. Macro as a function is capable of accepting and returning____ 4. Macro allows us to add, delete and ____a module. 5. The code of macro begins with ___followed by the name of the macro and ends with____. a. By default a macro is saved in the ______. D.Multiple choice Questions