Author: Tamar E. Granor
This article is excerpted from the recently published book OOsSwitch: 501 Things You Wanted To Know About Switching to OpenOffice.org from Microsoft Office.
Can I use macros to simplify repeated actions?
OOo provides two ways to set up macros. The first is to record a macro, where you perform the desired action with OOo “watching.” (The ability to record macros was added in OpenOffice.org 1.1. Earlier versions have no macro recorder.) The second choice is to write it yourself using the OpenOffice.org Basic language. Once you define a macro either way, you can assign a keystroke to it and/or put it on a toolbar to make it instantly available.
Can I use my Office macros in OOo?
Unfortunately, OpenOffice.org uses a different version of Basic than Microsoft Office, so macros created in Office won’t work in OOo. At this time, no mechanism exists for converting Office macros to OOo macros. There’s some discussion of creating one, but due to the complexity of the task, it’s not likely to happen any time soon.
How do I record a macro?
Before recording a macro, make sure your document is set up as you want it, including putting the cursor where the action you’re recording begins. Choose Tools | Macros | Record Macro from the menu. The Recording toolbar appears.
Now, perform the action exactly as you want it recorded, including any cursor movements, choices from the menu, shortcut menu or toolbars, and so forth. (Remember to leave the cursor positioned where you want it to be once the macro finishes. For example, if you record an action to apply to a series of words, lines, or paragraphs one at a time, make sure you leave the cursor at the beginning of the next word, line, or paragraph.) Once you perform all the actions that constitute the macro, click the Stop Recording button.
The Macro dialog appears for you to name the macro and indicate where to store it. Type a name for the macro. The name may not include spaces or a variety of other punctuation characters.
You can store macros with OpenOffice.org itself or with individual documents or templates. By default, macros you record are stored for all of OOo in the Module1 module of the Standard library of OOo.
To store a macro with a document or template, find the document in the left list of the dialog, and click the Standard item for that document. The first time you save a macro to a particular document or template, you are prompted to specify the module; either accept the default Module1 or supply a meaningful name. Once you save one macro with a particular document or template, you can choose the Module1 module to save additional macros there.
How do I use a macro?
Once a macro is defined (whether by recording it or creating it with code), there are a number of ways to run it. The most obvious is to access it from the menu, using Tools | Macros | Macro… to open the Macro dialog. Navigate to the macro you want to run and click the Run button.
You can use a keystroke to run a macro (see “How do I assign a keystroke to a macro?” below) or put a button to call the macro on a toolbar (see “How do I put a macro on a toolbar?”).
A formula in a spreadsheet can use a macro directly. The formula should refer to the macro by name and pass any required parameters. In this case, the macro must return a value appropriate for use in a spreadsheet. In addition, the macro should be stored in the spreadsheet itself, so it’s always available. For example, if you have a macro that computes the cube root of a number, assuming it’s called CubeRoot, a formula could use it like this:
=CubeRoot(D37)
It’s likely a macro you can use in this way is one you write yourself (see “How do I create a macro other than recording it?” below) rather than one you record.
You can also run a macro when an event occurs on a form. Macros can also be called in a variety of other places, such as during validation in a spreadsheet.
How do I assign a keystroke to a macro?
To assign a key combination to a macro you use the Keyboard tab of the Configuration dialog (Tools | Configure on the menu).
In the top section, choose the keystroke you want to assign. Next, in the Category list of the bottom section, find the module containing your macro. Macros stored with OOo are listed under OpenOffice.org Basic Macros. Macros for the current document are listed under
Click the “+” to show the list of libraries and then click “+” for the appropriate library to find the module. Click the module name; the list of macros displays in the Function list.
Choose the macro you want and click the Modify button.
How do I put a macro on a toolbar?
As with assigning keystrokes, putting a button for a macro on a toolbar is the same as creating a button for any other command. Use the Customize Toolbar dialog. To open that dialog, either choose Customize from the shortcut menu of any toolbar, or click Customize on the Toolbars tab of the Configuration dialog.
Find the toolbar to which you want to add the item in the Toolbars drop-down list. In the Available buttons list, find your macro, navigating down through the document, library, and module as needed. The Available buttons list includes each open document, along with an entry for OpenOffice.org Basic Macros.
When you find the macro, you can add it to the specified toolbar by clicking the Add button or by dragging it to the position where you want it and dropping it. Once you add the button, you can move it around on the toolbar using drag-and-drop or using the Move Up and Move Down buttons.
Use the Icons button to specify an icon for the macro. If no icon is specified, the name of the macro appears on the button.
How do I create a macro other than recording it?
You can define macros by writing the actual code needed. OpenOffice.org uses a variant of the Basic programming language. While the documentation calls it OpenOffice.org Basic, it appears to be equivalent to the StarBasic language used by StarOffice.
OpenOffice.org Basic is different from Visual Basic for Applications (VBA), the programming language used by Microsoft Office. While both are based on Basic, the object models are quite different. The object model of a macro language is the part that provides access to documents and their components.
The remainder of this section assumes at least some familiarity with simple programming concepts.
How are macros organized?
OOo uses a multi-level structure for storing macros. There are some macros available throughout OOo. In addition, any document or template can have macros stored with it; such macros are available only when that document or template is open.
Macros are organized into modules and libraries. A module is a group of macros, presumably with related functionality. A library is a group of modules, again presumably related by their function. For example, you might create a library of macros related to interest calculations. In that library, one module might contain macros related to mortgage amortization, while another might have macros for computing simple and compound interest.
Both libraries and modules can have meaningful names. Names must begin with a letter and can use letters, digits, and the underscore character (“_”). It’s a good idea to use names that indicate the purpose and contents of the module or library.
What tools are provided for writing macros?
OOo offers an interactive development environment (IDE) for writing, editing, and testing macros. It’s analogous to the Visual Basic Editor (VBE) provided by Microsoft Office. To open the IDE, choose Tools | Macros | Macro… from the menu. The Macro dialog opens; in this version (Figure 1), it contains buttons for working with existing macros.
| Figure 1. When you open the Macro dialog from the menu, it lets you manage and edit existing macros. |
Find the library and module you want to work on and click the Edit button. When the selected library contains no modules, the Macro dialog includes a New button; click that to create a module and open it for editing.
When the IDE opens, the macro highlighted in the Macro dialog is displayed. When you create a new module, OOo adds two empty macros by default. One is called Main and the other is Macro1. You can delete or rename these, and then create the macros you want. Figure 2 shows the IDE as it opens when clicking Edit in Figure 1.
The IDE has a number of components. The Function toolbar is docked below the menu. Be aware that it’s the regular function toolbar used in all the OOo applications. So, for example, clicking the Open button lets you open a document, and clicking Save saves the current document.
| Figure 2. The IDE for editing macros shows the Basic code and offers a variety of tools for editing and testing it. |
(When you click the Run button on the Macro toolbar, the first macro in the Module runs. This is different from Office’s VBE; in that environment, the Run button executes the macro at the cursor’s current position.)
The Macro toolbar is docked beneath the Function toolbar. It contains controls for testing macros, and offers access to various dialogs and tools for macro construction.
The main macro-editing window is below the Macro toolbar. It shows the code currently being edited. The macro is color-coded to represent its syntax and aid in getting it right. To the left of the code window, a narrow window provides a place to set and remove breakpoints. Double-click next to any line of code to set a breakpoint on that line; double-click it again to remove the breakpoint.
By default, two windows are docked beneath the code window. The Watch window lets you track the value of variables as code executes. To watch a variable, type its name in the Watch text box and press Enter. To remove a variable from the Watch window, click it, and then click the Remove Watch button next to the Watch textbox.
(The Watch window in Microsoft Office accepts expressions as well as variables. OOo’s Watch window handles only variables.)
The Calls window shows the call stack, that is, the list of routines that have been called. The most recently called is at the top and is numbered 0. The list shows the parameters passed to each routine as well.
There’s a tab at the bottom of the IDE for each module of the chosen library. Click a tab to switch to another module. You can rename a module using the shortcut menu for the tab itself.
What does the object model look like?
The OOo object model is quite different from the object models for the Microsoft Office applications. The objects in OOo are organized into services. Services, along with other things like constants, are combined into modules. To access an object, you may need to drill down through the module to the service.
For many things you want to do in a macro, you need to create an instance of a service. You do so using the createUnoService() function, passing the completely specified name of the service. Once you have a reference to a service, you can address its properties and methods.
Table 1 lists some of OOo’s key services and the modules that contain them.
Table 1. OOo’s object model uses a hierarchy of modules and services. You’re likely to use some services quite often.
| Service | Module | Used for |
| Desktop | com.sun.star.frame |
Opening existing documents and creating new ones. |
| TextDocument | com.sun.star.text |
Working with text documents. |
| SpreadsheetDocument | com.sun.star.sheet |
Working with spreadsheets. |
| ChartDocument | com.sun.star.chart |
Specifying data and other characteristics of a graph. |
| PresentationDocument | com.sun.star.presentation |
Working with presentations. |
| DrawingDocument | com.sun.star.drawing |
Working with drawings. |
| FormulaProperties | com.sun.star.formula |
Working with formulas. |
You can use the Desktop service’s loadComponentFromURL method to open existing documents or create new ones. For example, the following code opens a new Writer document:
dim oDesktop as Object
dim sURL as String
dim mNoArgs()
oDesktop = createUnoService("com.sun.star.frame.Desktop")
sURL = "private:factory/swriter"
oDesktop.loadComponentFromURL(sURL, "_blank",0,mNoArgs())
A couple of keywords give you quick access to the OpenOffice.org application object and to the active document. Use StarDesktop to get the application object and ThisComponent for the document object. For example:
Dim oApp As Object Dim oDoc As Object oApp = StarDesktop oDoc = ThisComponent
OOo doesn’t have a feature like Office’s IntelliSense to provide you with help as you type code. You need to know the available properties and methods and their parameters. To make matters worse, the Help file doesn’t include information on services, objects, or properties and methods. There’s no equivalent to the VBA Help files provided with Office.
Without IntelliSense or a Help file for the macro language, how do you find out what objects are out there and what properties and methods they support? There are a variety of resources, but the best place to start is with a document known as Andrew’s Macro Document. This document, created by Andrew Pitonyak, contains an introduction to writing OOo macros, along with dozens of useful macros. At this writing, the document runs about 200 pages; it’s updated pretty regularly. Andrew’s Macro Document points to a variety of other resources for macros.
Can I automate OpenOffice.org from other applications?
One of the most powerful features of Microsoft Office is the ability to call on its functionality from other applications. For example, a custom database application might call on Word to perform a mail merge, or use Excel for complex computations. Fortunately, you can also automate OOo. Once you’re comfortable writing macros, you can apply the same knowledge to run OOo from other applications.
To start an automation session, create an instance of the Service Manager, com.sun.star.ServiceManager, in the application you’re automating OOo from. You do this using the appropriate mechanism of the client language (the one doing the automating). For example, using Microsoft Visual FoxPro, that line would look like this:
loServiceManager = CreateObject("com.sun.star.ServiceManager")
Once you have an instance of the Service Manager, use its CreateInstance method to instantiate the other objects you need, referring to them using their module and service names. For example, in Visual FoxPro, you create a desktop object, like this:
loDesktop = loServiceManager.CreateInstance("com.sun.star.frame.Desktop")
Once you create objects like a desktop, you can use them just as you would in a macro.
In Microsoft Office, one of the best ways to figure out how to automate a particular task is to record a macro for it and then examine the macro code. Unfortunately, OOo’s macro recorder is really just a sophisticated keystroke recorder. The code it generates calls on the executeDispatch method of the Dispatcher service to perform each action in turn rather than using methods of the document services.
You can find documentation for automating OpenOffice.org at http://udk.openoffice.org/common/man/tutorial/office_automation.html.
