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