Page 197 - Computer - 10(A&B)

Macros to Work as Built-in Functions

Although Calc finds and calls macros as normal functions, they do not really behave as built-in functions. For
example, macros do not appear in the function lists. You have to run the macro whenever you need it. Moreover,
it should be in the same workbook. It is possible to write functions that behave as regular functions by writing
as Add-In.

Accessing Cells Directly
Macros can also directly access the cells in a Calc workbook and perform actions without any manual selection
needed by the user. This is a very desirable functionality that allows a lot of work to be automatically done
efficiently and without human error.
Let us create a table header generating function that will automatically create the column names when we run
the macro in the first row of the current Calc sheet.
Step 1: Create a new Calc workbook and go to the BASIC window and type the following code.

Here, in the macro the value of each cell is defined.
We want to fill a row of cells using range like headings of the columns i.e., A1 to E1. For this, we need
to define an array with (0,4) where 0=row and 4=column. Note that columns start with 0, thus 0 to 4 is
counted as 5 columns that is going to fill.
Similarly all the cells in the first row are defined in the subsequent lines of the code.

Step 2: Now, go back to the Calc Workbook and Run our macro.
The result will be:

Sorting the Columns Using Macro
Let’s see how we can sort the column values using a macro.

79