<rant>
Has anyone else found writing OpenOffice.org macros an exercise in hair-tearing frustration?
It gives you a choice of four different languages to write them in, which would be great if it didn’t make the documentation four times as long and complicated, and if at least one of them actually worked reliably and was easy to use.
The “documentation”, api.openoffice.org, is a maze of twisty little classes, all equally useless. How do I get, munge and then replace the current selection? It’s hardly an edge use case. Why do I need to work out if my XComponent can be UNO.QueryInterfaced to an XDocument, which doesn’t have a getCurrentSelection method anyway? XModel does, but it doesn’t have a setCurrentSelection() method, and all I could find about that little anomaly was a sarky mailing list message saying that the getCurrentSelection() method on XModel was a misfeature and I should use XComponent. “That’s what it’s there for.” Well, gee, thanks. Except that it doesn’t have any methods relating to the selection at all.
Searching doesn’t work. Each page has about three search boxes, two of which are the wrong one but you don’t know which two. Some take you off into the wilds of other websites and wikis which contain content which seems tantalisingly related to what you are doing but in fact is (as far as I can now tell) about OpenOffice.org’s C++ internal interfaces.
The “Macro Management” screen is an exercise in how not to design a UI to “manage” anything, with an utterly unnecessary and imposed three-level hierarchy of “Modules” and so on, where clicking the wrong button dismisses the dialog and makes you painfully reopen it from its home nested four levels deep in the menus and renavigate using a widget the size of a postage stamp back to the relevant place in the complex tree to try hitting the other button to see if it works any better.
What were they thinking? I bet VB is far easier than this.
</rant>
Yeah, but there’s more. I had to do some extensive, repetitive reformatting with Excel, but using the macro recorder was a total nightmare. I read the documentation carefully and recorded keystrokes carefully so it would run correctly starting from anywhere within the spreadsheet, etc. I worked on that for hours, but could not get consistent or correct results, even when I divided the problem into little pieces. Sometimes it would shift correctly to another worksheet, sometimes it would simply die. Sometimes it would scramble data, and sometimes it would work through correctly almost to the end. The problems were endless. Debugging was going to be a huge, uncertain chore, and I risked destroying all the data.
I took it home and recorded a long, complicated macro on OpenOffice that did the job perfectly on the first try. No debugging whatsoever, and I had never even used that spreadsheet before! There was just one catch: I wasn’t able to assign a shortcut, it took about 6 or 8 keystrokes or mousestrokes to run the macro, and I had to run it about 100 times to finish the job. Oh, well.
Spreadsheet macros are garbage. Stay away from software that doesn’t work.
Yes, VB is a million times easier.
I couldn’t believe how awful OpenOffice macros were when I first tried to do them. The APIs appear to simply be wrapping some internal objects. By contrast the VBA interfaces in MSOffice seem to have actually been designed for people writing macros (*gasp*). I’ve written several medium-sized applications using VBA and Excel (10k plus LOC), and many smaller ones, and while frustrating at times, (mainly due to obscure Excel bugs that I manage to tickle), it is actually do-able. The integrated help is brilliant (provided you remembered to install it), the language is adequate, the IDE is very capable — even in Office 97, you had a full debugger, extensive autocomplete, F1 for context sensitive help, optional static typing, etc. Even the IDE is fully and easily scriptable, so I was able to write an Excel Add-In that exported/imported workbook code so I could keep my code under VCS.
So when I first tried to write macros in OpenOffice, I just couldn’t bring myself to continue for more than about 5 minutes.
Luke
I was really pleased the other day at how the importing of excel macros has improved in OpenOffice.
I had a spreadsheet I wrote for my wife, who is a teacher, that applied a simple switch function to grades (they couldn’t go 1,2,3 or even a,b,c but 1c,1b,1a,2c, etc). It worked fine in excel as a custom function but I just counldn’t get it to work in OpenOffice, even if i wrote it from scratch. Having said that I tried it with the beta of OpenOffice 3 the other day and it worked perfectly!
Then again, I realise I’m not trying to do anything as complicated as you are.
Kind regards
Tim
I was really pleased the other day at how the importing of excel macros has improved in OpenOffice.
I had a spreadsheet I wrote for my wife, who is a teacher, that applied a simple switch function to grades (they couldn’t go 1,2,3 or even a,b,c but 1c,1b,1a,2c, etc). It worked fine in excel as a custom function but I just counldn’t get it to work in OpenOffice, even if i wrote it from scratch. Having said that I tried it with the beta of OpenOffice 3 the other day and it worked perfectly!
Then again, I realise I’m not trying to do anything as complicated as you are.
Kind regards
Tim