October 19, 2004

Getting started with OpenOffice.org macros

Author: Andrew Pitonyak

An OpenOffice.org macro is a saved sequence of commands or keystrokes that are stored for later use. An example of a simple macro is one that "types" your address. Macros support commands that allow a variety of advanced functions, such as making decisions (for example, if the balance is less than zero, color it red; if not, color it black), looping (if the balance is greater than zero, subtract 10 from it), and even interacting with a person (asking the user for a number). It is common to assign a macro to a keystroke or toolbar icon so that it can be quickly started.

This article is excerpted from the recently published book OpenOffice.org Macros Explained.

The OpenOffice.org macro language is very flexible, allowing automation of both simple and complex tasks. Although writing macros and learning about the inner workings of OpenOffice.org can be a lot of fun, it is not always the best approach. Macros are especially useful when you have to do a task the same way over and over again, or when you want to press a single button to do something that normally takes several steps. Once in a while you might write a macro to do something you can't otherwise do in OpenOffice.org, but in that case you should investigate thoroughly to be sure OOo cannot do it. For instance, a common request on some of the OpenOffice.org mailing lists is for a macro that removes empty paragraphs. This functionality is provided with AutoFormat (select Tools | AutoCorrect/AutoFormat | Options tab). It is also possible to use regular expressions to search for and replace empty space. There is a time and a purpose for macros and a time for other solutions.

The OpenOffice.org macro language is based on the BASIC programming language. OOo Basic runs one line at a time. However, you usually need more than one line to get anything done, so you will typically write routines -- also known as procedures -- that consist of a number of lines that, when all are run, do a particular thing. For instance, you might write a routine that deletes a header from a file and inserts your preferred header.

In OpenOffice.org, routines that are logically related are stored in a module. For example, a module might contain routines for finding common mistakes that require editing. Logically related modules are stored in a library, and libraries are stored in library containers. The OpenOffice.org application can act as a library container, as can any OOo document. Simply stated, the OpenOffice.org application and every OpenOffice.org document can contain libraries, modules, and macros.

Storing a macro in a document library

Each OpenOffice.org document is a library container able to contain macros and dialogs. When a document contains the macros that it uses, possession of the document implies possession of the macros. This is a convenient distribution and storage method. Send the document to another person or location and the macros are still available and usable.

The traditional method of introducing a programming language is by writing a program that somehow outputs the message "Hello World," and in that spirit, my first macro shows a variation of "Hello World."

Step 1. Create a library
All OOo documents, regardless of document type, may contain macros. To add a macro to any OOo document, the document must be open for editing. Start by opening a new text document, which will be named "Untitled1" -- assuming that no other untitled document is currently open. When a document is created, OpenOffice.org creates an empty library named Standard. The Standard library, however, remains empty until a new module is manually created. Use the Macro dialog to organize libraries and modules: select Tools | Macros | Macro.

The "Macro from" list shows the available library containers; this includes every open document as well as the application library container. The document library containers are listed below the "soffice" container using the document's assigned name. Most library containers already have a library named Standard. Double-click a library container icon to toggle the display of the contained libraries. Double-click a library to toggle the display of the contained modules.

The Standard library in the untitled document is highlighted. This library was automatically created when the new document was created. The document currently contains no modules -- remember that macros are stored in modules. Although you could click the New button to create a new module, don't! The point of this section is to create a new library.

Note: Do not store your macros in the Standard library. Create a new library with a descriptive name and store your macros there. When a library is appended it will overwrite an existing library with the same name. If all of your libraries are named Standard, this prevents you from appending your libraries to other library containers.

Click the Organizer button to open the Macro Organizer dialog. As with the Macro dialog, all of the library containers are listed. In other words, each document is listed, as is the "soffice" application library container. The Standard library is highlighted in the document "Untitled1"; scroll down the list to find "Untitled1" if required. The Macro Organizer dialog is a tabbed dialog, and the tab in focus is Modules. As the name implies, the Modules tab deals with modules.

The purpose of this section is to create a meaningfully named library that is contained in the "Untitled1" document. Click the Libraries tab to deal with libraries. When this portion of the dialog is displayed, the application library container (soffice) is selected in the Application/Document list. Select the "Untitled1" document so that the changes are made to the untitled document. The buttons displayed on the Libraries tab affect libraries, not modules.

Click the New button to create a new library. Although the default name is "Library1," it is better to choose a meaningful name such as "MyFirstLibrary" or "TestLibrary." Click OK to create it.

Step 2. Create a module

Macros are stored in a module, so the next step is to create a module in the newly created library. Assuming that the Macro Organizer is still open, select the Modules tab. The newly created TestLibrary is now displayed in the Macro Organizer. Select TestLibrary or any module contained in that library, and then click the New Module button to create a new module. The default name is Module1; choose a more descriptive name for the module and click OK to create it.

A common mistake is to highlight the wrong library container in either the Macro dialog or the Macro Organizer dialog. The most common mistake is to select a library or module in the application container (soffice) rather than a specific document. Find the document name in the list. The document name is determined by the title as set in the document's Properties dialog. Use File | Properties to open the document's Properties dialog. The title is set from the Description tab. If no title is set, the file name is used instead.

Step 3. Enter your first macro

If the Macro Organizer dialog is still open, you can highlight the newly created module and click the Edit button. This will open the Basic IDE. Another option is to use the Macro dialog. If the Macro Organizer dialog is open, click the Close button to open the Macro dialog. If the Macro Organizer dialog is not open, select Tools | Macros | Macro to open the Macro dialog.

The purpose of the Macro dialog is to operate on individual macros. Select MyFirstModule and click the Edit button to open the Basic IDE. One empty subroutine, Main, is automatically created when a module is created. The new button on the Macro dialog creates a second empty subroutine, Macro1. Delete these subroutines and replace them with the code following code:

Sub main
  HelloWorld2()
End Sub
Sub HelloWorld1
  Print "Hello World One"
End Sub
Sub HelloWorld2
  Print "Hello World Two"
End Sub

The IDE contains a Macro toolbar and a Function toolbar. Rest your mouse cursor on a toolbar icon for a few seconds to read the text that appears; this provides a hint at what that icon does. Click the Compile icon to check the macro for syntax errors. No message is displayed unless an error is found. The Compile icon compiles only the current module.

Modify the above code to demonstrate an error. Remove the second double quotation mark from the Print statement in HelloWorld1. Click the Compile icon. A dialog displays a relevant error message for the first error encountered. The error message indicates that a double quotation mark was expected but not found. The first double quotation character is highlighted, and a red arrow marks the line with the error. Click the OK button to close the error dialog, fix the line by adding a double quotation mark at the end, and then compile the code again.

Click the Run icon to run the first routine in the current module. It is not necessary to click the Compile icon first, because clicking the Run icon automatically compiles every module in the current library. The Run icon always runs the first macro in the current module. As a result, a different approach is required to run HelloWorld1. To run HelloWorld1, you can use one of the following methods:

  • Place HelloWorld1 first in the module and click the Run icon.
  • Modify the main subroutine to call HelloWorld1 rather than HelloWorld2.
  • Use the Macro dialog to run any routine in the module.
  • Add a button to your OpenOffice.org document that calls HelloWorld1.
  • Assign the macro to a keystroke. To do this, click Tools | Configure to open the Configuration dialog, and then select the Keyboard tab. Macro libraries are at the bottom of the Category list. You can also find this by clicking Tools | Macros | Macro, selecting the specific macro, and then clicking the Assign button to launch the Configuration window. Various tabs in this dialog allow you to assign the macro to execute as a menu item, from a keyboard key, a toolbar icon, or a system event.
  • Add an icon to the toolbar that calls HelloWorld1.

To use the Macro dialog to run any subroutine in a module, follow these steps:

1. Select Tools | Macros | Macro to open the Macro dialog.
2. Find the document that contains the module in the "Macro from" list.
3. Double-click a library to toggle the display of the contained modules.
4. Select the module to display the contained subroutines and functions in the "Existing macros in: " list.
5. Select the desired subroutine or function to run-for example, HelloWorld1.
6. Click the Run button to run the subroutine or function.

When a document containing macros is opened, OpenOffice.org issues a warning to help you avoid accidentally running a macro containing a virus. Although you can still manually run any macro using the Macro dialog, macro buttons in the document will not function. Click Run to fully enable the macro buttons added to a document.

It is possible to add a directory to the list of "secure paths." If you are certain that a path contains documents that you can safely trust not to contain macro viruses, you can check the box, and the path will be added to the list of secure paths. In the future, all documents loaded from the same location will be considered safe, and macros will always run.

Storing a macro in the application library

The OpenOffice.org application itself is a library container. This is an excellent place to store code and dialogs common to multiple documents. Version control is easier if macros are stored in one location. If five documents all contain the same macro, not only is storage space wasted, but if the macro changes, you must change the macro in five different documents.

To store a macro in the application libraries, use the same methods used for documents. The application-level container is named "soffice." The OpenOffice.org application includes multiple libraries. The Standard library includes a module named Module1, which contains one empty macro that does nothing. Use the Macro Organizer dialog to add new libraries.

Note: Uninstalling OpenOffice.org may delete libraries stored at the application level, so you should always keep a backup of your libraries. Reinstalling or installing a new version of OpenOffice.org may overwrite application-level libraries. Back up these libraries when you back up your documents. In most cases, the libraries that you created are still there, but the configuration files are new and do not reflect the new libraries. Therefore, it's usually possible to restore your libraries from the standard library location.

Each application library is stored in its own directory. To determine where OpenOffice.org stores application libraries, select Tools | Options. In the Options dialog, expand the OpenOffice.org branch in the tree menu and select Paths. The Basic entry shows the locations of the external libraries.

Before installing a new version of OpenOffice.org, make a copy of all application-level libraries. If you install OOo into the same location, it overwrites the configuration file that tells OOo where your application-level libraries are. The libraries are usually still there but OOo does not know about them. To restore lost libraries, regardless of where they are located, use the Libraries tab on the Macro Organizer. Verify that "soffice" is selected in the Application/Document list, and then click the Append button. Navigate to the directory containing the library that you want to add. Select the file script.xlb and click Open. Do this for each library that you want to restore. This method can also be used to add libraries stored in documents.

To practice adding a macro to the application-level library, open the Macro Organizer. Verify that the "soffice" library container is the current container. Click the New Module button to add new modules to the application-level libraries. To add new libraries, select the Libraries tab. Verify that "soffice" is selected in the Application/Document list, and then click the New button.

Libraries stored in documents may be appended to the application library container. When a library is appended, it overwrites an existing library with the same name. It is, therefore, a good idea to create meaningful library names to hold macros. This limits problems moving macros between library containers.

Now that you've mastered macro basics we can begin diving into OOo's IDE. We'll get to that next time.

Click Here!