OpenOffice.org – all grown up and only three years old

August 15, 2005

Michael Allen

Its been a little over three years since OpenOffice.org went to market as an open source distribution of Star Office 5. There were plenty of reviews of this new office package in 2002, many favorable, but most quick to point out what was considered by some as short comings. Complaints about the lack of a Pivot Table equivalent in Calc and a macro recorder. I am currently using version 1.1 so my comments will reflect my own experiences with OpenOffice.org 1.1 unless stated otherwise, even though there are probably newer versions available for download.

OpenOffice.orgs OOoBasic – Powerful stuff

As a consultant, I get to do a little work automating spreadsheets for report generation and other such things from time to time. Some of these are pretty good size projects but many are small. Some customers require that this work be done in Excel® so writing VBA® applications for Excel is also required on occasion. In fact, if you're not yet writing VBA applications in Excel, and I think it is safe to say that the vast majority of Excel users are not, you are most likely tapping into only a small fraction of Excels capabilities. In the early days of spreadsheets, Lotus 123® for example, writing macros to perform repetitive tasks or carry out multi-sheet calculations or perform a workbook or print job formatting operation, were written in a macro script. By associating a certain keystroke combination with the script, all of the commands in the macro were carried out at once.

Todays office software packages are huge and have far more capabilities built into them than most users ever learn how to take advantage of or even need. Microsoft® includes VBA in all of its Office® applications including Word®, Excel, and Power Point®. OpenOffice.org includes a powerful tool for use by each of its own office applications as well, known as OOoBasic. One important thing to note about both of these are that they are not macro languages. They are complete computer programming languages, and the programs you can write with them can turn your text document, spreadsheet, or presentation into a quite powerful computer program, and, you can also write macros with them as well.

While VBA and OOoBasic, or StarBasic as it is sometimes called, are both derivatives of the Basic programming language, they are very different animals. VBA is a COM based language while OOoBasic uses its own object management system, Universal Network Objects, or UNO, and has "bridges" built in for each programming language for invoking UNO objects from other systems including C++, Java and COM based languages like VB or VBA. The OpenOffice.org development team was interested from the very beginning in creating a cross-platform office application and development tool. Available industry standards including COM, RMI, and COBRA were all lacking in one way or another so they created their own superset of all available formats and it is one powerful package. Thanks to their efforts in developing UNO, users are able to write macros and applications for their document in Java, C++ and Python as well as OOoBasic.

Some basic (no pun) code comparisons

To hide the selected column in the active sheet using VBA is a simple task:

Sub hideSelectedCol()

Selection.EntireColumn.Hidden = True

End Sub

To do the same thing in OOoBasic takes a few more keystrokes:

sub hideSelectedCol

createUnoService("com.sun.star.frame.DispatchHelper"). ExecuteDispatch_ (ThisComponent.CurrentController.Frame,".uno:HideColumn", _ "", 0, Array()

end sub

VBA, with all its built in property and value selections right up front make it the quicker method simply because there is less typing, but not the better method. While OOoBasic requires creating a UnoService, this quickly becomes second nature to anyone with even a little programming experience. The more intricate control over the task you are writing the code to perform is clear in the way the service is created and executed as compared to merely changing the value of a member components property. OOoBasic is the better method. A crude comparison, but valid none the less: You have a two-hundred pound boulder that needs to be moved six-inches to the left. The VBA method (imho) is the equivalent of physically lifting the entire weight of the boulder, stepping left six-inches, then dropping the boulder into place. Boom, one fell sweep and you're done in an instant. The OooBasic method (imho) is the equivalent of estimating the weight of the boulder, measuring the distance to be moved, creating a fulcrum and lever, just the right length and height for the job, then gently nudging the boulder into perfect position with almost no effort. Coming up with the right fulcrum and lever is a few more steps, but that's the way most of us are going to move the boulder. Easier on the back and shows some creativity and finesse at the same time, and the fulcrum and lever can be re-used for similar tasks again and again.

Addressing some past complaints

The OOoBasic IDE stinks

This complaint stems from a couple of different sources. First, the original IDE in OpenOffice.org 1.0 for writing Basic applications, or macros, was lacking good debugging tools. Most of that has already been addressed in version 1.1 with more robust debugging features including watch and call windows, stops, breakpoints and procedure steps. Syntax highlighting would be a nice addition. I would look for even more improvements and features for the IDE in future versions. Second, OOoBasic doesnt have method and property options pop up after you type a member component name seperator. This is still not in the IDE but it may be added in a future version. OOoBasic is not VBA so it may be reasonable to expect that features like this are different. In the mean time, by having to become intimate with objects and procedures, youll garner better programming skills.

There is no macro recorder

This actually seemed to be the number 1 complaint in the original version. Version 1.1 has an excellent macro recorder. Macro recorders don't generally generate very efficient code (imho), but it can sure come in handy if youre wanting to see how a Calc or Writer procedure looks in code. Talking about inefficient code, remember our little example earlier where we were hiding the selected column of the active sheet ? Well, I did the exact same thing from the Calc desktop while recording it as a macro. Heres what it looks like:

sub hideSelectedCol

rem ----------------------------------------------------------------------

rem define variables

dim document as object

dim dispatcher as object

rem ----------------------------------------------------------------------

rem get access to the document

document = ThisComponent.CurrentController.Frame

dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, ".uno:HideColumn", "", 0, Array())

end sub

I have to admit, this is a more structured way of doing it, putting all the parts into variables to be used in a less complex statement is the preferred way of programming. The automatically generated comments are pretty good too. It is a bit long though, especially just to hide a column.....

Theres no Pivot Table Equivalent

Summary

While the bulk of my experience with OpenOffice.org has been using Calc, I also use Writer frequently as well as Draw. I have little experience with Impress yet but I plan to become an expert at it before too long and will share my experience with it here.

Calc and Writer are full featured powerhouse office applications with sophisticated programming features built in. The menus and customizable tool bars are easy to use and make program navigation a breeze.

With OpenOffice.org, you get a truly great set of office tools at the best price in the world. Yes, it really is free, and one thing you will NEVER hear from an OpenOffice.org user is that they got hit with a macro-virus.