OpenOffice.org Calc adds support for Excel VBA
As an Excel user, you may have looked at OpenOffice.org and found that it doesn’t support Visual Basic for Applications (VBA), the Microsoft Office macro language. If you’ve spent years building hundreds of Excel macros, the fear of losing them all could keep you locked in to Office. If so, it’s time to look again; Novell has taken OpenOffice.org’s source code and create a version of its own that supports Excel VBA.
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.
How do I make vba code compatible with libre office
I have recently migrated to pclinuxos from windows and seem to like it. The only problem I am facing is that libreoffice, the default spreadsheet package is not compatible with excel macros. Below is the vba code I have:
Option VBASupport Sub DeleteToLeft() Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeft End Sub Function SinceLastWash() Application.Volatile WashCount = 0 WearCount = 0 CurrentRow = Application.ThisCell.Row For i = 3 To 35 If Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "a" Then WearCount = WearCount + 1 End If If Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "q" Then WashCount = WashCount + 1 WearCount = 0 End If Next i SinceLastWash = WearCount End Function Function testhis() testhis = Application.ThisCell.Row End Function Is there a way to convert this code to make it compatible with libreoffice or do I have to learn an altogether new language like python? Learning python would not be a problem but is not a solution to my problem as I have many work related files in excel which have a lot of vba code and it is not possible for me to use open office/libreoffice at work. I just want to add that the function SinceLastWash gives the correct value in some cells where I use it and in others gives an error, #NAME? Thanks
4 Answers 4
With a few exceptions, Microsoft Office and LibreOffice cannot run the same macro code. Microsoft Office uses VBA (Visual Basic for Applications) code, and LibreOffice uses Basic code based on the LibreOffice API (Application Program Interface) environment. Although the programming language is the same, the objects and methods are different.
The most recent versions of LibreOffice can run some Excel Visual Basic scripts if you enable this feature at LibreOffice — PreferencesTools — Options — Load/Save — VBA Properties.
In reality, you would most likely need to sit down with the LibreOffice API and rewrite the functionality.
A distinguishing feature of Libre Office, the IBM fork of Open Office, the Sun version of StarWriter, was that it had some support for VBA, and ran «some Excel Visual Basic Scripts». The online help file illustrates the discontinuity of history for people shifting from Apache Open Office to Libre Office.
The VBA support was initially developed by Novell, but they worked together with Sun, and it shipped in OOo 3.0 (though still not complete anywhere)
You must translate the portions that manipulate the document to use the UNO API. Sadly, this can be tricky depending on what your macro does. Basic statements work directly. Modifying a document generally does not.
Range(Cells(CurrentRow, i), Cells(CurrentRow, i)).Value = "a" The Cells command returns a specific cell based on a row and column. So, you need the current row. Here is some craziness to get the active cell:
Sub RetrieveTheActiveCell() Dim oOldSelection 'The original selection of cell ranges Dim oRanges 'A blank range created by the document Dim oActiveCell 'The current active cell Dim oConv 'The cell address conversion service Dim oDoc oDoc = ThisComponent REM store the current selection oOldSelection = oDoc.CurrentSelection REM Create an empty SheetCellRanges service and then select it. REM This leaves ONLY the active cell selected. oRanges = oDoc.createInstance("com.sun.star.sheet.SheetCellRanges") oDoc.CurrentController.Select(oRanges) REM Get the active cell! oActiveCell = oDoc.CurrentSelection oConv = oDoc.createInstance("com.sun.star.table.CellAddressConversion") oConv.Address = oActiveCell.getCellAddress Print oConv.UserInterfaceRepresentation print oConv.PersistentRepresentation REM Restore the old selection, but lose the previously active cell oDoc.CurrentController.Select(oOldSelection) End Sub When you have the active cell, you get the cell address, and from that, you have the row. You do not need to use the range at all, since you only care about a single cell, so, you get the active sheet and then get a particular cell from the sheet.
Something like this: ThisComponent.getCurrentController().getActiveSheet().getCellByPosition(nCol, nRow).getString() = «a»
I don’t feel like figuring out what this does
Selection.SpecialCells(xlBlanks).Delete shift:=xlToLeft