Learn OpenOffice.org Spreadsheet Macro Programming: OOoBasic and Calc Automation, by Dr. Mark Alexander Bain
220 pages, December 2006
$36 (hard copy); $16 (ebook)
Note: This book review was conducted as an audio interview of macros expert John Dukovich, by Phil Shapiro, using Audacity, the free sound recording and editing software. The review is available in transcript form below and can also be accessed on YouTube. ( http://www.youtube.com/watch?v=NavOETFfsXc ) The following is a lightly edited transcript of the audio interview.
The audio interview itself is about ten minutes in duration. It took about an hour to create and required far less effort than sitting down to collaboratively write a book review. The interview was created in five short segments, where, in between each audio segment, the ensuing audio segment was planned. After the audio interview are some follow-up written comments by reviewer John Dukovich.
My name is John Dukovich. I'm with Green Moon Solutions, a small technology company in the Washington, DC, area. I've been working with Microsoft Office applications, basically since they came out, and I'm a heavy user of Excel macros and Visual Basic for Applications language. I do quite a few applications for clients and use macros in ways that a lot of people don't.
Instead of just manipulating spreadsheet information, there's a lot of behind-the-scenes calculations and user GUI interface kinds of things that I do. So I'm quite familiar with Excel macros. I shied away from OpenOffice Calc for quite a while because initially I heard the macro feature wasn't as good as Excel's. And years ago, there wasn't a good conversion between the two.
However, when I got my hands on this book, OpenOffice.org Spreadsheet Macro Programming, I was curious and hoped to find I was wrong, that this would open up new opportunities for clients and organizations that want to get away from Microsoft Office, clients who are already using OpenOffice, so I was really interested to see the level of capability that Calc had in its macro programming.
So with that background, I found the book to be very informative to someone like me. It almost felt as if it were written for me. But I tried to step back from that, because of the experience of a lot of my clients who are not programmers -- who don't have a lot of technical background and who use spreadsheets in the way a lot of people do, as databases and things like that. And so I tried to step back and from their point of view ask, “Would this book help me out if I were in that situation?” And for some people it would and it would at least give them an idea of the capabilities that are there. However, there are several times in the book where the author says, “Okay, here is how we do this and now we are going to move onto something else, so if you're interested in exploring this in more detail, go to this Website. Go to the OpenOffice.org Website, where they have detailed user guides and that type of thing.”
Phil Shapiro: “If someone uses OpenOffice 3, would this book, written for OpenOffice 2, still be applicable?”
John Dukovich: “That's a great question. I was wondering about that myself. I use OpenOffice 3, and I tested a lot of macros on both the Windows version and the Ubuntu version, and other than a few dialogue box differences, this book is quite applicable to version 3. The capabilities, the functions, the techniques -- everything is pretty much still the same. There are just a few differences in menu options and that type of thing. But it's nothing like going from Microsoft Office 2003 to 2007, which was a complete rewrite.
Phil Shapiro: Let me ask you this question: The Excel macro programming language is very powerful. What macro capabilities does OpenOffice have that you might have been surprised about?
John Dukovich: Well, one thing I was concerned about is the dialogue boxes, which Excel calls “user forms” -- where you set up the GUI, the interface where people can use drop-down menus and boxes and radio buttons and that type of thing to provide data input. Well, what I did was I had three different levels of Excel spreadsheets with macros in them. I have what I call a very basic one, an intermediate one, and a very, very complex one that I imported into OpenOffice Calc, and I was quite surprised -- in a good way -- that all the dialogue boxes came over perfectly. I didn't have to edit them one bit, and the functionality of the dialogue boxes was there, the macros that were tied to various buttons, everything in that regard worked.
So I was quite pleased. The thing that I did find, though, is that there are some functions that don't translate. So when I did my very-basic-level spreadsheet, everything transferred fine, the macros ran, everything was wonderful. In the intermediate one, I had to tweak a few things. I guess it's just the way Calc references certain spreadsheet objects that are different in Excel and that don't quite translate over. In the very complex one, I have some functions that I think will need a complete rewrite. In a more detailed programming sense, a lot of data structures are set up in that one that did not translate over -- and I didn't quite expect them to, as I was trying to test the limits of the thing, but overall I think that, for most users, if you're working in Excel and if you need to bring something into Calc, it's going to work quite well, which is also good news for students and nonprofit organizations that have smaller budgets or can't afford to buy Microsoft Office: If you want to use OpenOffice, I think you'll be in great shape -- and if you have to share documents with people using Microsoft Office, your files are going to translate quite well.
In terms of the writing style of the book, it was pretty easy to read. I enjoyed the use of a continuing story throughout the book of a detective agency and how they were using macros, and as the story went on, they had to do more and more complicated things. So the author would introduce a chapter with the next continuing part of the story and then talk about how you would implement those things in Calc. Now the interesting thing about that is that it left me wanting more. It seemed the author would introduce a concept and would show a very quick, pretty simple example of that, and then he would refer you to online material, not material that he had written, but to the OpenOffice.org Website, for more information. And I found myself several times saying, “Couldn't you have gone into more detail? Couldn't you have shown the next step? And I started thinking to myself, “People reading this book may not understand the power of macros. I think it would have helped for the author to discuss more about how macros are helpful and how they can help the common person and what are the most common uses of macros.”
Well, you've got long lists of data and you can use macros to sort through that data and you can use macros to pull out pieces of information. You can use macros to automate some charts and graphs -- for formatting -- and the author does discuss those things, but not in a lot of detail.
Something I use macros a lot for: Often I'll set up a workbook with an initial sheet where the user will type in different values of parameters. Say they want to do some sort of economic analysis; they can type in something for the time period, for the interest rate, what's the average cost of something -- so there is an initial sheet full of these values, and the macro then does real complex calculations behind that. The macro goes to the first sheet and grabs all those variable values, does the calculations behind the scenes, and then spits out on another sheet a table of values, or a summary table. The author of this book doesn't discuss that kind of process of input, churning of numbers, and then output, for which macros are extremely powerful, making complicated spreadsheet models accessible to more people. I would have liked to see more explanations along those lines, more examples.
Another observation in looking at this book is that maybe the author had been constrained to a certain page count, and that might have been why he could not have gone into greater depth or why he refers people to external sources for more information. So overall, this is a good book, it provides a good amount of information to get going with macros, but it could also be a launching pad for a reissue of this book in the digital format, something for the Kindle or iPad, where there is no page constraint, where additional authors or volunteers could be brought in to provide more material, provide more examples, link to more examples in hypertext format. So that's an opportunity, and since we have a new version of Calc out, that's a good opportunity for the author to update the book a little bit and then go into that digital publishing direction.
I'd like to give the publisher credit for putting out this book. They took a risk to put out a book that covers open-source software. Now all my friends and colleagues are quite aware of open source and use it; however, the general public is not as familiar with it, so the publisher didn't know who their audience might be or how big it might be, so they should get credit for taking that risk, and hopefully this book will get more people interested in OpenOffice. And even if someone doesn't view themselves as a “macro user,” it's still a good idea to purchase this book, get familiar with it, and make the purchase a vote for more books on open-source software. Use your dollars as your vote and show that there is support for this type of software. We need more books out there to help people figure out how to use open-source software and to see how beneficial it can be.
In our audio interview you asked whether a macro application created in Calc could run in Excel. The answer is a qualified yes. The code would have to be created in Calc using Excel's Visual Basic for Applications (VBA) language, not Calc's native OpenOffice Basic. That can be done -- Calc can handle VBA. One only need to place as the first line of code in a module the header "Option VBASupport 1." However, when you import into Excel, all of Calc's macro code is stripped out (at least in Excel 2007 it is). So one would need to import the Calc spreadsheet into Excel, and then copy and paste the VBA code into an Excel module. Macros that are coded in OpenOffice Basic Code (instead of VBA) will not function in Excel.
An interesting capability of Calc is that within a given spreadsheet file there can be a mix of OpenOffice Basic and Visual Basic code -- and the application will run just fine. So if you have already coded an application in OpenOffice Basic and a collaborator has worked on other pieces in VBA, the VBA code can be imported into the Calc file (there may need to be some tweaking, depending upon the complexity of the code). Now that's user friendly!
Previous blog posts: