Linux.com

There are two ways to create a macro in OOo. One is to use OpenOffice.org Basic to write the macro. The other is to use the macro recorder. That will be the approach we focus on.

The macro recorder is great, because it lets you create a macro without any programming, and when you're done you can look at the code it built and add your own enhancements.

We'll sort a grocery list to illustrate how to build macros. I update my OpenOffice.org Calc-created grocery list spreadsheet weekly before trudging off to the store. I don't know how some of you shoppers do it with your handwritten random lists.

Before I run my macro, I delete the quantity of each item from the previous week. I sort the list alphabetically by grocery item (column A), then enter the desired number of each grocery item (column B). Once I've done that data entry, I want to sort the list from lowest to highest according to aisle (column C), filter the list so only non-zero-quantity items show up, then print the filtered list.

I created a macro to sort by item name using the macro recorder:

• Select the Tools menu item, then Macros.
• Click Record Macro to begin to record your keystrokes.
• Left-click on the first item in column A.
• Drag the mouse down to the bottom of the list, then across to include columns B and C.
• Click the Data menu item, then Sort.
• Select Column A and Ascending.
• Click OK to do the sort.
• Click the Stop Recording button that popped up when you clicked Record Macro. The recording box will close and open a menu for specifying the macro name. Click My Macros, then Standard, and finally Modules1. Move the cursor up to the upper left input box and give the new macro a reasonable name. Since I was sorting on the A column, I called the macro "sorta."
• Finish up by clicking OK.

Running the macro is even easier than creating it. Step through the Tools menu, Macro, and Run Macro. Pick the macro out of the list and push the Run button at top right. In my case it was My Macros, Standard, Module1, and "sorta." The spreadsheet flashed briefly and then it was sorted alphabetically by column A.

Creating a macro to sort by aisle was the same process, except I sorted on Column C instead of Column A and named it "sortc."

I also created a "finddeli" macro that looks for all instances of the word "deli" in my list. You can record just about any sequence of actions or key clicks and turn them into a macro.

Attaching macros to buttons

Clicking through the Tools, Macro, Run Macro sequence is almost as much effort as just sorting manually. A worthwhile upgrade I made was to attach the sorta macro to a button that could be placed right on the spreadsheet:

• Turn the control toolbar on with View, Toolbars, and Controls. The floating toolbar will appear.
• Click the Design Mode On/Off button (the ruler with the little draftsman's triangle) on the Controls toolbar to light up the various controls. Click the pushbutton and then move down to the spreadsheet and use the mouse to drag out a rectangle.
• Right-click on the new button, then select the Controls menu item to bring up the button properties menu.
• On the General tab fill in an appropriate Label for the button. In my case it was "sort a."
• On the Events tab move down to the Mouse Pressed item and click the triple dot button on the right.
• On the Assign Menu, click the Assign button to bring up the Macro Selector menu, where you can choose the macro to be actuated by the button. In my case I chose My Macros, Standard, Module1, and the sorta macro.
• Click OK to complete the assignment.
• Again click the Design Mode On/Off button to allow the button to be pushed in the spreadsheet.

You can now run the sorting macro by clicking on the button.

Creating buttons and macros for simple repetitive jobs like this can save you loads of time. You might look at your spreadsheets and make a list of the tasks that you do over and over, then record a macro and run it to see if it saves you some time. Any situation where you flip back and forth between some spreadsheet state is a candidate for some pushbutton automation.

If you want to get more sophisticated with your spreadsheets, you can also use text boxes, radio buttons, and list boxes. Controls like buttons and list boxes on forms are another way to interface with macros.

For a thorough education on OpenOffice.org macros be sure to get "OpenOffice.org Macros Explained" by Andrew Pitonyak. Don't let the book's massive 476 pages intimidate you. It has vast sections of basic programming practice that explain things in minute detail. It could be a knowledgeable silent companion for anybody who wants to be a departmental OpenOffice.org macro guru.

Rob Reilly is a consultant, writer, and commentator who advises clients on business and technology projects. His Linux, portable computing, and public speaking skills-related articles regularly appear in various high-end Linux and business media outlets.

Share    Print    Comments