Comprehensive notes Use macros in spreadsheet information technology class 10

In this article I am going to provide a comprehensive notes Use macros in spreadsheet information technology class 10. Here we start!

Use macros in spreadsheet Information Technology class 10

While working with spreadsheet we need few tasks to be repeated again and again. Macros allow to automate repeated tasks in the same way over and over again.

Macro is a powerful feature of spreadsheet that allows to record a set commands performed by user and can be run anytime are per the requirement.

Macros needs to be created. It can be recorded before use. Now let us see how to record a new macro in spreadsheet.

Record macro

Macro records a series of commands or keystrokes in spreadsheet. These can be recorded one time and then can be used number times later.

By default, in Libre Office Calc the macro recording feature is not active. So ensure that it should be activated before recording macro. To activate macro in Libre Office Calc, follow these steps:

Observe these screenshots:

Macro cannot record the following commands:

1. Opening windows
2. Action carried out in another window than where recording was started
3. Window switching
4. Action not related to the spreadsheet contents
5. Selection will be recorded only if the they are done by using keyboard
6. The macro recorder works only in calc and writer

Follow the given steps to record a macro:

[1] Click on Tools > Macros > Record Macro option. Stop recording button will open.

[2] Perform the commands or actions which needs to be recorded. Now click on Stop Recording option.

[3] It will open basic macros dialog box with Macro Name, Save Macro in, Existing Macros in etc.

[4] The by default macro name is Main and saved in standard in the Standard Library in module1. Macro name can be changed.

[5] Click on Save button.

Rules of naming macro

Spreadsheet follows some rules for naming a macro. They are :

1. Macro name always starts with alphabet
2. Macro name does not contain a letter
3. Macro name does not contain a special character except uderscore

Running a macro

To run a macro in libre office calc, follow these steps:

[1] Place the cursor from where the macro needs to be recorded.

[2] Click on Tools > Macros > Run Macro option.

[3] Macro select dialog box window will appear as displayed in below given screenshot:

[4] Select the library which contains macro module and select the name of macro. Click on Run button.

Creating and Organizing a simple macro

Organizing macro is important tool in spreadsheet software. In a spreadsheet, there may be various macros created by user for different purposes. When macro is created, either it is created through new library or existing module stored in a library.

Follow the given steps to organize macro:

[1] Click on Tool > Macros > Organize Macros > Basic option.

[2] It will open the LIbreOffice Basic Macro dialog box window as displayed in below screen shot.

[3] The basic organizer dialog box will appear as displayed in below screenshot:

This dialog box has following basic components:

Creating a new library to store a macro

To create a new library click on Libraries tab > Choose the location and Library and click on New button.

[2] Type the new Name for library and click on OK.

Creating a new module to store a macro

Click on Modules tab > New Button. Type new module Name and click on OK.

Macro As Function

Sometimes we need to do some tasks repetitively in spreadsheet. For example a same formula is used frequently in spreadsheet on different cells and there is no predefined function for it.

In this scenario, Macro will be created, that performs the calculations. This will save time and efforts to type the desired formulas. These macros can be created Macro As Function.

A function is created in macro editor in between Function and End Function block.

Function <Function_Name>
 tasks
End Function

A function can accept arguments or values. Some operations can be performed on these arguments and result is returned.

Creating a macro as a function

Let see how to create a macro as function, Observe the scenario and open the code editor for writing a function.

I am writing a function which returns a pie value spreadsheet. Create a new spreadsheet or edit existing macro. Follow the given steps:

1. Click on Tools > Macros > Edit Macros option.
2. Now choose the module to write function.
3. Type the desired code for a function.
4. Save the code and close editing window.

Code for accepting pie value:

Option Explicit

Sub Main

End Sub

Function pie_val()
 pie_val=3.14
End Function

Using a function

Open spreadsheet and type the function with = sign as below:

=pie_val()

Press enter key and see the result.

Passing Argument to Macro Function

A function may have some arguments. A macro function can accept some arguments as values and as references. Let us create a macro function that accept two values and display the large number.

Edit macro as suggested above and write code as below:

Function two_max(x,y)
If x>y Then
two_max=x
Else
two_max=y
End IF
End Function

Using Macro function in Libre Office by Passing Argument

In spreadsheet, the function is used by writing = sign followed by function name with arguments. Have a look at this website.

The above function will return maximum value from given two numbers.

Output 1:

Output 2:

Passing the arguments as values

Arguments passed to macro function as a value. It is not possible to know which cells are used. User is will provide the referenced cells rather than values. These reference can be provided either range as string or parse string and obtain values in the referenced cells.

Macros to work like built- in functions

The normal macro functions are something which is not really behave as built-in functions. Macros does not appear in the built-in functions list. It is only possible to write functions as they behave like built-in function by writing add-in.

Accessing cells directly

Libre Office Calc internal objects can be directly manipulated in CALC spreadsheet. To access a cell in spreadsheet ThisComponent is automatically set to the reference. A Calc document contains sheets and the macro accesses these via a call to ThisComponent.getSheets(). Use getCellByPosition(col, row) to return a cell at a specific row and column.

Function SumCellsAllSheets()
 Dim TheSum As Double
 Dim i As integer
 Dim oSheets
 Dim oSheet
 Dim oCell
 TheSum = 0
 oSheets = ThisComponent.getSheets()
 For i = 0 To oSheets.getCount() - 1
   oSheet = oSheets.getByIndex(i)
   oCell = oSheet.getCellByPosition(0, 1) ' GetCell A2
   TheSum = TheSum + oCell.getValue()
 Next
 SumCellsAllSheets = TheSum
End Function

Sorting

For sorting in through macros, following functions are useful:

1. oSortFields(): Sort the data in different fields
2. oSortDesc(): Sort the data in descending order
3. oCellRange(): Specifies the cell ranges for sorting
4. oSheet():Refers a sheet

Observe this code:

Sub SortRange
 Dim oSheet ' Calc sheet containing data to sort.
 Dim oCellRange ' Data range to sort.
 REM An array of sort fields determines the columns that are
 REM sorted. This is an array with two elements, 0 and 1.
 REM To sort on only one column, use:
 REM Dim oSortFields(0) As New com.sun.star.util.SortField
 Dim oSortFields(1) As New com.sun.star.util.SortField
 REM The sort descriptor is an array of properties.
 REM The primary property contains the sort fields.
 Dim oSortDesc(0) As New com.sun.star.beans.PropertyValue
 REM Get the sheet named "Sheet1"
 oSheet = ThisComponent.Sheets.getByName("Sheet1")
 REM Get the cell range to sort
 oCellRange = oSheet.getCellRangeByName("A1:C5")
 REM Select the range to sort.
 REM The only purpose would be to emphasize the sorted data.
 'ThisComponent.getCurrentController.select(oCellRange)
 REM The columns are numbered starting with 0, so
 REM column A is 0, column B is 1, etc.
 REM Sort column B (column 1) descending.
 oSortFields(0).Field = 1
 oSortFields(0).SortAscending = FALSE
 REM If column B has two cells with the same value,
 REM then use column A ascending to decide the order.
 oSortFields(1).Field = 0
 oSortFields(1).SortAscending = TRUE
 REM Setup the sort descriptor.
 oSortDesc(0).Name = "SortFields"
 oSortDesc(0).Value = oSortFields()
 REM Sort the range.
 oCellRange.Sort(oSortDesc())
End Sub

Watch this video for more practical understanding of the topics:

That’s all from use macros in spreadsheet information technology class 10. I hope you enjoyed this article. Feel free to share your valuable feedback in the comment section.