Novell’s Noel Power is the developer in charge of introducing Excel VBA interoperability into OpenOffice.org Calc. He says that the interoperability is achieved by:
- allowing Excel VBA macros to run natively within OpenOffice.org;
- providing a compatibility object model;
- continuously improving the compatibility model by identifying and implementing the most useful and widely used APIs;
- extending the symbols available to ooo-basic to include the compatibility API; and
- modifying the core ooo-basic runtime to handle Excel VBA syntax.
Noel says, “Novell is pushing this to the wider community. The source code is freely available from the OpenOffice.org Hackers Pages, and instructions on how to build it are on the OpenOffice.org build page. Additionally, we are pleased to say that we are also shipping the VBA interoperability feature with Novell’s branded OpenOffice.org versions for Windows.
If you want to build the new edition of OOo from scratch, the steps are fairly simple:
- Download the source code.
- Carry out the configuration for your system. This is the most labor-intensive part of the process, but the feedback you get is useful, and so it’s just a matter of deciding which additional files your system requires, or which parts of the installation that you want to disable.
- Download the actual OOo packages. (Don’t worry — once the configuration is completed you just need to type
./download). - Make the application.
However, you might not have to do all of the work yourself. “As far I can tell,” said Noel, “the VBA interoperability feature currently ships with following distro versions of OpenOffice.org”:
- Ark
- Debian
- DroplineGNOME
- Frugalware
- Gentoo
- Mandriva
- QiLinux
- Red Hat (FC6)
- Ubuntu
- SUSE and SUSE SLED
Unfortunately, there’s no easy way of identifying whether your distro supports the VBA interoperability apart from trying some VBA code. According to Noel, “You can use the ooo-basic organizer (Tools -> Macros -> Organize Macros -> OpenOffice.org Basic) and navigate to the document. If the document contains macros and they are not commented out, chances are the feature is enabled.”
If you’ve installed a supported distribution, or built Novell’s version of OpenOffice.org on your Linux box, or installed Novell’s OpenOffice.org for Windows, will any Excel macro work in Calc? “No,” Noel said, “support for VBA is not complete, but we think we cover a large portion of the common usage patterns. Those macros that we’ve come across mostly use a manageable subset of objects in the Excel API (such as the Range, Worksheet, Workbook, etc.). We have concentrated on supporting those objects, and the most commonly used method/properties of those objects.”
If you’ve got your own Excel spreadsheet to try, then open it up and see what happens. The macros should run just as if you’d opened the document in Excel. If you haven’t got Excel, Noel passed a good example on to me — hypocycloid-demo.xls. A hypocycloid (in case you’re interested) traces the path of a fixed point on a circle as it rolls around inside a larger circle (anyone remember Spirograph?). The spreadsheet will generate different hypocycloids for you. If you are using OpenOffice.org with VBA support, all of the buttons and sliders should work, changing the image displayed on the screen, despite the fact that all of the code is written using Excel VBA.
How would things differ if you’d loaded hypocycloid-demo.xls into a version of OpenOffice.org without VBA support? OpenOffice.org will open an Excel spreadsheet even if the spreadsheet contains VBA macros, but when you press the buttons nothing will happen — and most importantly, you won’t get any error messages.
Let’s look at the code in the OOo Basic Editor. Go to the OpenOffice.org menu and click on Tools -> Macros -> Organize Macros -> OpenOffice.org Basic.
Sub VBA_Library
Rem Sub openWorkbooks (iFiles())
Rem Dim wBook as Workbook
Rem Dim wList as String
Rem Dim iFile as String
Rem
Rem For Each iFile in iFiles
Rem Workbooks.Open iFile
Rem Next iFile
Rem
Rem For Each wBook In Workbooks
Rem wList = wList & wBook.Name & chr(13)
Rem Next wBook
Rem Worksheets("Sheet2").Range("A1") = Now()
Rem msgbox Workbooks.Count & " files open:" & chr(13) & chr(13) & wList
Rem End Sub
End Sub
You’ll see that standard OpenOffice.org handles VBA code in a module. The VBA code all gets turned into comments, and then is encapsulated into a new subroutine (which is given the same name as the module).
However, if you’re using OpenOffice.org with VBA support, you’ll see a different picture:
Option VBASupport 1
Sub openWorkbooks (iFiles())
Dim wBook as Workbook
Dim wList as String
Dim iFile as String
For Each iFile in iFiles
Workbooks.Open iFile
Next iFile
For Each wBook in Workbooks
wList = wList & wBook.Name & chr(13)
Next wBook
Worksheets("Sheet2").Range("A1")= Now()
msgbox Workbooks.Count &" files open:"& chr(13) & chr(13) & wList
End Sub
This version of OpenOffice.org Calc adds an extra line of code to any module that it loads and which contains VBA code:Option VBASupport 1
This line has no effect on standard OOo Basic code, but it ensures that the VBA interoperability is recognized correctly. If you prefer the VBA statements (or if they’re just what you’re more used to) then you must remember to add this line manually when you create your own VBA modules for Calc.
While all this works well, it is not yet a mainstream OOo solution. Noel says, “Sun does have a proprietary solution, but it has some drawbacks. This year at OOoCon I had some frank discussions with some of the Sun developers and there at least seems to be some willingness to align their solution and ours. The impression that I got was they felt that our approach was a better one (they do a translation phase, whereas we run the VBA natively). I also got the impression that they deemphasizing support for their solution. We hope to increase the pace of our upstreaming efforts and aim to have the initial effort completed in the next couple of months.
“At the moment the situation is still that the interoperability feature is only available from the popular distros that use ooo-build. However, quite a few steps have been made in getting closer to the goal of integrating all of this work into the main OpenOffice.org source. A fair proportion of the core basic runtime modifications have already been folded into the ‘vanilla’ OpenOffice.org; the compatibility API and some of the other hooks are not. I am, however, in close contact with the developers at Sun and we are working together to try and get all of the feature in. As this is a complicated feature, and touches many parts of the code, this is not a trivial task, it will take time”.
Your can learn more about using Excel VBA with OpenOffice.org Calc in my book Learn OpenOffice.org Spreadsheet Macro Programming: OOoBasic and Calc automation.
