CBSE 10th class Unit 2: Chapter 2 USING MACROS IN SPREADSHEET

 Unit 2: 
Chapter 2

USING MACROS IN SPREADSHEET

A. Multiple Choice Questions

1. (b) Tools
2. (a) 1formatword
• Macro names cannot start with a number.
3. (b) LibreOfficeMacros
• This library contains pre-recorded macros and should not be modified.
4. (b) BASIC
• LibreOffice Basic is the programming language used for creating macros in LibreOffice.
5. (d) F5
• Pressing F5 in the IDE executes the currently selected module.
6. (a) Main
• The default name for a new macro is "Main."

B. Fill in the blanks

1. LibreOfficeMacros library is automatically loaded when the document is opened.
2. IDE stands for Integrated Development Environment.
3. Macro as a function is capable of accepting parameters and returning a value.
4. Module allows us to add, delete a module.
5. The code of a macro begins with SUB name of the macro and ends with END SUB followed by the name of the macro.
6. By default, a macro is saved in the LibreOfficeMacros library.

C. State whether the following statements are True or False

1. True
2. True
3. False
4. False
5. True
6. True

D. Answer the following questions

1. What is a Macro? List any two real-life situations where they can be used.
• A Macro is a series of recorded actions or a set of instructions that can be executed to automate repetitive tasks.
• Real-life situations:
• Automating the formatting of a large document with specific font styles, sizes, and colors.
• Creating complex charts and graphs based on large datasets.
2. List the actions that are not recorded by a macro.
• Mouse movements, manual data entry, and system-level actions like opening or closing files.
3. How is the LibreOffice Macros Library different from my Macros?
• LibreOfficeMacros is a default library that stores pre-recorded macros and system-defined functions.
• My Macros is a user-defined library that allows you to store your own custom macros.
4. Differentiate between predefined functions in Calc and Macros as a function.
• Predefined functions are built-in functions that perform specific calculations (e.g., SUM, AVERAGE, etc.).
• Macros as functions are user-defined functions that can perform more complex tasks, including calculations, formatting, and data manipulation.
5. List the rules that should be kept in mind while naming a macro.
• Macro names should be descriptive and meaningful.
• They should not contain spaces or special characters.
• They should be unique within a library.
6. Give any one advantage of macros.
• Macros can significantly increase productivity by automating repetitive tasks, saving time and effort.

Lab Exercises

Note: The specific steps for recording and running macros may vary slightly depending on your LibreOffice version. However, the general approach will be similar.

1. Record a Macro to Bold and Underline the Heading in Cell A1:
• Open a new document.
• In cell A1, type a heading.
• Go to Tools -> Macros -> Record Macro.
• Give the macro a name (e.g., "BoldUnderlineA1").
• Select cell A1 and apply bold and underline formatting.
• Stop recording the macro.
• Save the macro in a new module named "Basic Formatting" and a new library named "DocumentHeadingA1."
2. Record a Macro to Create a Bar Chart:
• Enter sample data in cells A2 to A9 and K2 to K9.
• Go to Tools -> Macros -> Record Macro.
• Give the macro a name (e.g., "CreateBarChart").
• Select the data range and insert a bar chart.
• Stop recording the macro.
• Save the macro.
3. Run the Macro to Display a Bar Chart for Temperature Data:
• Enter the temperature data for 2010-2017 in the specified format.
• Go to Tools -> Macros -> Run Macro.
• Select the macro you created in step 2 and run it.
• The macro will create a bar chart for the given data.

Exercise 4

(a) Record two macros to create the format given in Fig. 5.20.

Macro 1: Create Headers and Borders

1. Open a new spreadsheet.
2. In row 1, enter the headings: "Roll No.", "Student Name", "English", "Hindi", "Maths", "Science", "Social Science", "Total", "Percentage".
3. Apply bold formatting and center alignment to the headings.
4. In row 2, enter the maximum marks for each subject (e.g., 100, 100, 100, etc.).
5. Apply a border to the entire range of cells (A1 to H2).

Macro 2: Create a Table

1. Select the range of cells from A3 to H10 (or adjust the range as needed).
2. Insert a table.
3. Apply appropriate formatting to the table, such as alternating row colors and borders.

(b) To enter names of students in the list so that she doesn't need to retype or copy the names

1. Create a list of student names: In a separate sheet, create a list of student names in a column.
2. Use the VLOOKUP function: In the "Student Name" column of the Marks list, use the VLOOKUP function to retrieve the names from the list based on the Roll No.
3. Record a macro: Record a macro to copy and paste the student names from the list into the Marks list.

Exercise 5

Use Macro as a function to calculate Mileage of a vehicle.

Mileage (in km/L) = Distance Travelled (in km) / Fuel filled (in Litre)

1. Create a new spreadsheet.
2. In the first row, enter the headings: "Distance Travelled (km)", "Fuel Filled (L)", "Mileage (km/L)".
3. In the subsequent rows, enter the data for distance traveled and fuel filled.
4. In the "Mileage" column, use the following formula: =A2/B2 (assuming the distance is in A2 and fuel filled is in B2).
5. Copy the formula down the column to calculate the mileage for all rows.
6. Record a macro: Record a macro to perform the above steps, including creating the headings, entering the formula, and copying it down the column.

Key points to remember:

• Macro naming: Use descriptive names for your macros.
• Macro organization: Organize your macros into modules for better management.
• Error handling: Consider adding error handling to your macros to prevent unexpected behavior.
• Testing: Thoroughly test your macros to ensure they work as expected.