If you make heavy use of a word processor, a spreadsheet application, or a text editor, then you may find yourself performing the same editing operations repeatedly. For instance, if you are working at a company that is undergoing a name change, you might be responsible for updating the company name in dozens if not hundreds of documents. If all of these documents were plain text — such as HTML files on the company's website — then you could update all of them in one fell swoop (usually referred to as "global search and replace"), using any powerful programmer's editor.
But the documents in question are much more likely to have been created using a word processing or spreadsheet program, and thus will not be in plain text. Consequently, the single global-search-and-replace method is not going to do the trick, because the text replace feature in the typical office productivity application can only affect the document that you are currently editing. In fact, if you are making changes to an spreadsheet workbook that contains multiple worksheets, for example, then any search-and-replace action performed using the Edit menu, will only update a single one of those worksheets. You would have to go to each one of the worksheets and repeat the same action for each one.
The same predicament exists in our name-change example, because when you begin the task of updating all of those company documents, you would have to open up every one of them, individually, click on the Edit menu, and choose whatever menu item is provided for replacing text. If you are lucky, the string of text to search for, and the string with which to replace it, would already be populated in the dialog box's fields, or at least available in drop-down lists. But even then, the process would be quite tedious. Wouldn't it be great if you could save that search-and-replace action somehow, and execute it with a single keystroke?
That is what macros are all about. Think of a macro as a series of actions grouped together — like a miniature computer program that can be called upon at any time, to perform some useful task inside of your application. A macro is always given a name, so as to be easily referenced at any time in the future. Macros are either stored inside of a special file that is available to the parent application (word processing and spreadsheet programs invariably are designed this way), or they are stored in separate text files (this approach is largely limited to programmer's editors).
OpenOffice.org, the free and powerful office productivity suite that is gaining fans worldwide, supports the creation and use of macros to the same extent as does its main rival, Microsoft Office. Writer is the word processing component of OpenOffice.org; its Office counterpart is Word. Both applications support macros, using quite different variations of the BASIC programming language.
There is an extensive amount of information that one would need to learn in order to master the development of macros in Writer, so in this article we can only scratch the surface. But it will be enough to give anyone an idea as to what is involved and how to begin creating one's own macros.
The Easy Way
If all of the actions that you wish to capture inside a macro, can be performed entirely using the keyboard and pointing device, then the fastest way to make a new macro is to use the macro recording feature built into the given application. Let's use our earlier example to illustrate how to do this in OpenOffice.org's Writer. Specifically, we will replace all occurrences of the string "Acme, Inc." with "XYZ, Inc." in the current document.
Begin the recording process by choosing the menu item Tools > Macros > Record Macro. A small dialog box will pop up, containing a single "Stop Reporting" button.
Choose the menu item Edit > Find & Replace, type "Acme, Inc." in the "Search for" field and "XYZ, Inc." in the "Replace with" field, and then click the "Replace All" button. If any instances of the search string were visible on the screen at the time that you executed the replacement, they should all have been changed to the replacement string. After acknowledging that the replacements have been done, click the Close button on the Find & Replace dialog box, and then click the Stop Recording button mentioned earlier.
You will be prompted to save the macro that you just created. Let's give it the name "find_and_replace_company_name", and save it in the default module, Module1. To test the macro, undo the text replacement that you just performed (Edit > Undo); all instances of the old company name should be restored. Then choose the menu item Tools > Macros > Run Macro. In the dialog box that pops up, locate the macro you created.
Click the Run button, and you should see the earlier text replacements performed again. You can then assign that macro to a new menu item, using the menu item Tools > Macros > Organize Macros > OpenOffice.org Basic > Assign.
At this point you may be wondering what the instructions (i.e., the source code) inside the macro looks like. Choose the menu item Tools > Macros > Organize Macros > OpenOffice.org Basic. (We are using the first item on that cascading menu, because recorded macros are coded using the OpenOffice.org BASIC language.)
By default, the most recently created macro is highlighted already. Click the Edit button. In the window that pops up, you will see the code for find_and_replace_company_name, as well as any other macros you may have created. But the edit cursor will be positioned at the line "sub find_and_replace_company_name". The term "sub" is short for "subroutine", since that is the name used for any function in OpenOffice.org BASIC. The full code listing is shown below, except for the comments (any line beginning with "rem" — short for "remark").
sub find_and_replace_company_name dim document as object dim dispatcher as object document = ThisComponent.CurrentController.Frame dispatcher = createUnoService("com.sun.star.frame.DispatchHelper") dim args1(18) as new com.sun.star.beans.PropertyValue args1(0).Name = "SearchItem.StyleFamily" args1(0).Value = 2 args1(1).Name = "SearchItem.CellType" args1(1).Value = 0 args1(2).Name = "SearchItem.RowDirection" args1(2).Value = true args1(3).Name = "SearchItem.AllTables" args1(3).Value = false args1(4).Name = "SearchItem.Backward" args1(4).Value = false args1(5).Name = "SearchItem.Pattern" args1(5).Value = false args1(6).Name = "SearchItem.Content" args1(6).Value = false args1(7).Name = "SearchItem.AsianOptions" args1(7).Value = false args1(8).Name = "SearchItem.AlgorithmType" args1(8).Value = 0 args1(9).Name = "SearchItem.SearchFlags" args1(9).Value = 65536 args1(10).Name = "SearchItem.SearchString" args1(10).Value = "Acme, Inc." args1(11).Name = "SearchItem.ReplaceString" args1(11).Value = "XYZ, Inc." args1(12).Name = "SearchItem.Locale" args1(12).Value = 255 args1(13).Name = "SearchItem.ChangedChars" args1(13).Value = 2 args1(14).Name = "SearchItem.DeletedChars" args1(14).Value = 2 args1(15).Name = "SearchItem.InsertedChars" args1(15).Value = 2 args1(16).Name = "SearchItem.TransliterateFlags" args1(16).Value = 1280 args1(17).Name = "SearchItem.Command" args1(17).Value = 3 args1(18).Name = "Quiet" args1(18).Value = true dispatcher.executeDispatch(document, ".uno:ExecuteSearch", "", 0, args1()) end sub
How does this code compare to that of an identical macro recorded in Microsoft Word (version 97), for instance?
Sub find_and_replace_company_name() Selection.Find.ClearFormatting Selection.Find.Replacement.ClearFormatting With Selection.Find .text = "Acme, Inc." .Replacement.text = "XYZ, Inc." .Forward = True .Wrap = wdFindContinue .Format = False .MatchCase = False .MatchWholeWord = False .MatchWildcards = False .MatchSoundsLike = False .MatchAllWordForms = False End With Selection.Find.Execute Replace:=wdReplaceAll Selection.Find.Execute End Sub
The Word code is recorded in the macro language VBA (Visual Basic for Applications). While it is certainly more concise than the OpenOffice.org code, in some respects VBA can be just as inelegant and difficult to write from scratch (which we will address next).
The Not-So-Easy Way
Space limitations do not allow us to get into the details of how to write OpenOffice.org macros from whole cloth. In fact, an entire book could be written on the subject. But we will consider some resources, for any reader who would like to pursue the subject further.
Perhaps the best place to start is the OpenOffice.org BASIC Programming Guide, which is organized as a wiki. It can also be downloaded as a PDF or ODT file, in case you would like to print it or do some work off-line and still be able to reference the information. The programming guide explains the OpenOffice.org BASIC language, the run-time library, and the Uno API (Universal Network Objects application programming interface). The guide includes sections on how to write macros to work with Writer documents, as well as documents generated by the other components of OpenOffice.org — Calc, Draw, Impress, and Base.
While the sample code listed above may look daunting to some at first glance, developing macros in OpenOffice.org's flavor of BASIC is not too difficult to learn, and, as with any programming language, gets easier with experience.
The Non-Basic Way
The first section of code makes available five API classes, and the code that follows creates some needed objects, and ends by writing out the desired message string at the end of the text range within the current document. Creating new macros in BeanShell and Python results in code that performs a similar operation.
As should be evident, there is so much that one could learn about writing macros for Writer, and there is even a choice of computer languages — a rare bonus among applications that support macros. If you choose to try writing some macros of your own, not only is the aforementioned programming guide available, but be sure to check out the OpenOffice.org Community Forum. You may soon discover how macros are the best way to take the tedium out of repetitive editing tasks.