How to write VBA macros in Open Office

Open Office is a great office suite that has its own object model and support for javascript, python and starBasic for macro scripting. I am far more comfortable coding with these dynamic language rather than with VBA and the horrible Microsoft Excel object model.

Unfortunately many clients still use Excel on their workstation machines. The need to provide spreadsheet reports to these clients and of course being the family member everyone sends their power-user programming condrums too :( I needed a way to test VBA macros without having to get access to a windows machine. Luckily, a while ago, Novell released to the open source community, for better or for worse, its VBAAPI compatibility layer for Open Office.

Today I had the time so I decided to test out the vba functionality. So I launch Open Office, went to the macro ide and started some test code,finished and click the run button. Nothing happened. Consulting the oracle of google finally relvealed the issue. To run VB macros you need to put the application into VBA compatibility mode by including the line

Option VBASupport 1

So your macro would look something like:

Option VBASupport 1
Sub Main
Dim range As Range
range = ActiveWorkbook.WorkSheets("Sheet1").Range("A1:B3")
range.Sort Key1:=Range("A1"), Order1:=xlAscending,Header:=xlGuess,OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
End Sub

The reason this was so difficult to find, I think, is that most people just import their macros and the line is then automatically inserted in the VBA code. The Novell VBAAPI does not support the entire range of VBA apis yet. They targeted the most commonly used ones first but there is on going effort to extend the coverage.

With VBA support there really is no reason for businesses to switch to using Open Office as now all those spreadsheets with macros will just work.

 

 

Comments

I have a question about using VBA in OpenOffice.
I can open excel spreadsheet in calc and a simple test VBA macro will run.
However, when I open a new workbook in calc and create a macro with the same code, it does not run.

What am I doing wrong?

Não funciona para macros mais complexas, apenas para uso em macros mais simples...