Thread: Macros in open office base

Overview of macros
A macro is like a script that can avoid a series of manual operations involved in GUI applications. In 000, macros can be coded in many languages. In this article we consider one simple language-OOBasic (OpenOffice. org Basic) to develop our macros. It is a dialect of traditional BASIC.

Some flavours of 000 come with the support ofVBA macros to make documents prepared in Microsoft Office interoperable with 000.

000 comes with built-in tools for the development of macros in OOBasic, i.e., no external SDK or development tools are required. 000 provides a macro organiser to create and maintain, and an IDE to code, run and debug macros.

Your first macro
Start any 000 component (e.g., Calc), and access Tools-Macros-Organize Macros- Basics. This opens a macro organiser window. In the pane on the left, expand the current document's name, select the standard, and click on New to create a new module. Or, expand the standard, select an existing module and an available macro, and click on Edit to modifY code.

This opens a basic IDE to write code. Macros are classified based on the location they are stored in.

•My Macros: User written macros common for all documents
•OpenOffice.org Macros: A library of macros provided by 000
•Document Specific: These are also user written but on a 'per document' basis and are available to the current document only.

The following is the code for a simple macro to greet people.

Figure 2 shows the editor, where you can enter the above code. When this code is run, a dialogue appears with the message, "Hello World". This macro is available to the present document only. To make it available for all documents place it under 'My Macros'.

This example works for all types of 000 documents. In the upcoming examples,.we will concentrate more on Calc documents because there is greater business value for spreadsheet automation.

Running and debugging

We can use different items in the standard toolbar of the Basic IDE to run/debug macros. A simple one is Run Macro (shortcut: F5). Other options are compile, stop, step into, step over, step out, toggle breakpoint, watch, etc. Macros can be created and maintained from this IDE also.

The following is the second example of a macro to access sheets, cells, etc, in a Calc document:

•Line 5 accesses the current document reference through ThisComponent and stores it to oBook.
•In Line 6 oBookSheets represents collection of all sheets in
the present workbook and then ditplays the count
•Line 7 renames first sheet to "Hello".
•Line 8 accesses the same sheet and stores it in curSheet.
•Line 9 accesses the Al cell in the first sheet and stores it in oCell
•Line 10 changes the value of the Al oCell to 10.
Let's consider some more code snippets to learn how spreadsheets can be controlled in different ways by macros.



Easy access to macros
Macros can be accessed in a more user-friendly way:
• A new menu item can be created to invoke a macro,
which can be placed under the existing menu or an altogether new menu
•A tool bar item can be created
•A keyboard shortcut can be assigned
To achieve all this, follow the options under the different tabs of the Customize window- Tools-Customize.

Suppose a new menu item has to be created for the previous example of FindAndRed ( for searching a key pattern and highlighting all cells in red), then the following steps are helpful to create a menu item.

Go to Tools-Customize-Menus. Under Menu List select an existing menu or create a new one by selecting the New option. In this example let's create a new menu called 'My Menu'.

Under Menu Content-Entries, use the Add option. Scroll to the end and specify the macro from the available list. A new menu titled 'My Menu' will appear. Under this a menu item with the macro name of ' Main' will appear for invoking the macro. You can use the Rename option under the ModifY button to change the default name from 'Main' to 'FindNRed', or anything else for that matter.

A similar process can be followed to create a toolbar item. Let's see another example of assigning a key combination for the same macro.

Go to Tools-Customize-Keyboard. Under the list of functions select the Macros' category and choose the macro. From the list of available keys select one which is free (better not to disturb standard shortcuts) and use the Modify option to assign it.

As you can see in Figure 4, we have assigned Shift+F3 to the FindAndRed macro.
Macros as event handlers Want to perform some task on starting or closing an application, or opening or closing a document? It's easy in OOo-put the desired code in a macro and it can be associated with some event by accessing Tools-Customize-Events.

Macro security
Although macros have their own advantages, they are dangerous too! Macros may contain vulnerable code that may harm the files and even your system. That said, there's no need to worry too much about this. 000 provides different levels of security to handle macros in documents.
•Very High: Only from trusted sources, even signed macros from untrusted sources are disabled.
•High: Signed macros from trusted sources only.
•Medium: Confirms before enabling macros while opening a document ifit's from an untrusted source .
•Low: All macros will be executed without any confirmation.
To control the security level, configure Toots-Options-Macro Security.

Trusted locations and certificates can be managed from the second tab of the Security manager window.

There are a few limitations with OOBasic macros. Basic is a scripting language, so the code for macros is always available with the document. Even though it's a good practice as per the open source philosophy, at the user level one may damage the code accidentally (or even intentionally), which may cause a huge loss of data or corrupt the system.

Extensions written in compiled languages are a better solution in this respect, as only the binary of the extension will be available tothe end user. Only trusted persons with a knowledge of programming can be provided the source code.