The ABC Auditing Company decides to migrate to OpenOffice.org. The firm gets a copy of OOo installed on every machine and trains its employees to work with documents in ODF (Open Document Format), as well as with older documents that were prepared in the MS Office format, in OOo. Everything seems to be going smoothly.
Suddenly, however, it’s noted that a few important Excel (spreadsheet) documents with macros are not functioning properly in OOo. They may need a developer to rewrite VBA code in OOBasic. This may take some time and money, as going back to the old environment is not an option any longer. The manager now wishes he had a copy of OOo that could handle VBA also. Thankfully, this is not a dream and is possible with some versions of OOo that support VBA. ABC Auditing should have chosen the right version when it decided to migrate to OOo.
VBA macros
Visual Basic for Applications (VBA) is an implementation of Microsoft’s Visual Basic integrated into Microsoft Office applications. You may wonder about the need for such a discussion in a FOSS article. Well, many organisations are migrating to OOo nowadays, but a lot of their old documents are in the MS Office format. When an MS Office document is opened in OOo, the VBA macros present in them are unusable under most circumstances. So VBA macros have to be rewritten in OOBasic to avail the same functionality in OOo. This becomes a major issue in migrating to OOo. To overcome this, recent versions of OOo come with support of VBA interoperability. The Go-oo flavour of OOo is a better choice for this purpose.
Some background on Go-oo
Go-oo is Novell’s effort, which is an addition to Sun’s core. Some pending issues in the upstream have been fixed and it comes with some additional functionality. Here are a few advantages that the Go-oo people claim, when compared to other flavours of OOo.
- Quicker start-up
- VBA support and better Excel interoperability.
- A quick starter in the system tray.
For a complete list of the advantages, please visit go-oo.org/discover.
So, thinking of getting a copy of Go-oo? You will find that pre-built binaries for Linux, Mac and Windows are available from go-oo.mirrorbrain.org/stable.
At present, Go-oo support for VBA is to a much greater extent for Excel spreadsheets compared to only a little for Word documents. There’s no VBA support for Power Point presentations, yet. You need to build Go-oo from sources to do some hacks or add VBA bits. The rest of this article explains how to build Go-oo and how to contribute by adding VBA properties and objects.
VBA support in OOo
The latest versions of upstream OOo and many versions of Go-oo come with VBA support. When an Excel document is opened in OOo, the VBA code used for macros is loaded as it is, with an additional line at the beginning—Option VBASupport 1. This tells the macro handler of OOo to process the code as per VBA specifications instead of default OOBasic.
To enable/disable VBA Model, go to Tools→Options→Load/Save→VBA Properties. For each type of document, opt for load/save options. If the option ‘Executable code’ is not selected for Excel documents, the VBA code in Excel is loaded as comments when opened in OOo, just like in older versions that don’t have VBA support.
OOo architecture
The OOo source is divided into modules. The advantage of this modular design is that each module can be rebuilt independently if you want to update the concerned libraries to reflect changes. For example, if a Calc related bug needs to be fixed, only the sc module should be rebuilt.
Around 200 modules exist in the current source code—at a location like ooo-build/build/ooo3??-m??. For example, the source tag ooo310-m15 means OOo 3.1.0 version, with milestone 15.
Some of the important modules are:
- sc is for Calc
- sd is for Draw and Impress
- sw is for Writer
- chart2 is for Calc charts
- formula is for math
- oovbaapi is for VBA support
- sfx2 is a framework for Calc, Writer, Impress, etc.
- vcl is the UI Core
It is not advisable to change modules in the bottom or middle layers like vcl, svx, etc. This is because changing these modules may lead to many side effects. Always try to fix issues by changing the top layer.
About ooo-build
Unlike the upstream OOo, one should obtain a copy of ooo-build first, as it is a wrapper for Sun’s core. It contains many patches that are pending for upstream approval (though, of course, accepted by the Go-oo team) and additions to the core. On configuring ooo-build, a download script will retrieve the required sources. When you run make inside ooo-build the sources are extracted, patches are applied and then it prepares for an actual build. Thus, compared to the core, Go-oo enables faster integration of hacks by contributors and additional features like VBA.
Once a patch is approved by upstream at a later time, then only it’s removed from ooo-build and merged into core.
Building Go-oo
The build takes around 8-10 hours on a machine with a typical configuration—and could take more or less time depending on hardware configuration. On a machine with an AMD Athlon 1700+ processor and 512 MB RAM, it takes around 10 hours. On a Core 2 Duo 2GHz processor with 3 GB RAM it takes around 4.5 hours.
Create two files—t1 and t2, before and after a build in order to figure out the time taken.
touch t1 ; make ; touch t2
The difference between time stamps of the t1 and t2 files will tell you the time taken for the build.
You can interrupt the build process in the middle if you’re running out of disk space. Free up some disk space and then run make again to resume the build process. If some missing packages are reported in the middle (which happens very rarely), install them and run make again to resume.
Requirements
- A 1.5 GHz or higher processor
- 512 MB RAM; 1 GB or higher recommended to minimise build time
- 10 GB free disk space (just for build, excluding OS requirements), 5 GB or more space for further hacks and if you plan to add debug support for some modules.
- A distro with a comfortable package manager
- Internet connectivity on Linux recommended
Step-by-step procedure
Step 1 – Check out ooo-build: You can check out the ooo-build repository through version controls like SVN, Git, etc:
svn co svn://svn.gnome.org/svn/ooo-build/trunk ooo-build
or:
git clone git://anongit.freedesktop.org/git/ooo-build/ooo-build
You can also manually download the latest ooo-build tarball from download.go-oo.org. However, using a version control is always advisable.
Step 2 – Configure:
./autogen.sh --with-distro=YOURDISTRO --with-gcc-speedup=ccache,icecream
Here you have to specify your distribution name with the --with-distro option. To see a list of supported distros, run the script once without this option. Use the nearest match if a specific distro is not listed.
The ccache,icecream option helps to accelerate the build process. The build time will substantially reduce if you add those with the --with-gcc-speedup option. Use --with-icecream-bindir=/usr/bin if you get an error like “icecream’s gcc not found”. If you haven’t used version control and downloaded the tarball directly, use the configure script instead of autogen.sh.
./configure --with-distro=YOURDISTRO --with-gcc-speedup=ccache,icecream
Step 3 – ./download: This will download the required sources directly using wget. Please note that the resume option is not available, as the download script won’t use wget with resume option. If your Internet connection is slow or not available you have to download the required sources manually using a different machine and copy to src/ directory. Apply your own logic to find out the required sources. Use the --with-tag option during configuration to use different sources other than the default.
Step 4 – make: This will start the build process, starting with extracting source files, applying patches, and then going on to the actual build (configuring and compiling each module). Wait until the actual build starts, as many missing packages will be reported at this phase. For example, if an error like ‘cups.h not found’ appears, install the cups-devel package to fix it. Once a few modules (boost, stlport, etc) start compiling, you can take a break until the build completes.
Step 5 – bin/ooinstall -l <path-to-install>: It installs OOo with links from the built sources—that’s why it’s called a ‘linkoo’ build. You can also use the make install command to install your OOo build. However, after every hack you have to run make install again in order to reflect the changes. So the special script ooinstall can be used instead to reflect changes without a reinstall every time.
Step 6 – cd <path-to-install>/program
Step 7 – source ./ooenv: This will prepare a shell to run OOo.
Step 8 – ./soffice.bin: You can also add options like -writer, -calc, or -impress, to run specific components directly. The soffice command takes the file name as the argument, which is loaded on start-up.
Hacking Go-oo
Now that the soffice instance is running properly from the built source, let’s do a small hack. Go to the vcl/ directory [ooo-build/build/ooo3??-m??/vcl/] and edit source/window/menu.cxx by changing the following line (around line number 1800):
pData->aText = rStr;
to:
pData->aText = String(rStr).Reverse();
Run the following commands to build the vcl module:
. ../LinuxX86Env.Set.sh ## dot space dot dot slash LinuxX86Env.Set.sh build
You can use build debug=true instead to add debugging support for a specific module.
We’ll use the deliver command on completion of the build to ensure changes are made when, by chance, make install is run at a later time.
Now, make sure no previous instance is running by closing OOo manually or with the command:
killall -9 soffice.bin
Re-run soffice.bin from the program directory to see the changes. Notice anything different? Yes, all the menu item characters are now in the reverse order (Figure 2). Undo the above change and build again to revert back to a sensible OOo install.
Pick up any other issue from qa.openoffice.org and hack on it.
Anyway, that was just an example—although, not at all useful. This article mainly focuses on implementing missing VBA properties and objects.
Adding VBA bits
As mentioned earlier, OOo is not fully interoperable with VBA, which is still under heavy development by Sun, Novell and others. So contributions are expected to improve interoperability.
ooo-build/test/macro/missing_vbafeatures.ods will give you an updated list of missing VBA features.
To add any VBA object or property, we need to modify two modules:
- oovbaapi module — idl files, interface for objects
- sc module — hxx, cxx files, actual implementation
Here is a small example on how a property is added to a VBA object.
Pick up any missing property from missing_vbafeatures.ods, say Application.Sheets. Implementation for the Application Worksheets property is already available, so linking the Sheets property to the Worksheets property can fix the issue. Actually, Sheet differs from Worksheet in the sense that a sheet can be a worksheet or a chart. For the moment, let’s consider both as the same.
Go to oovbaapi/ directory, and add the following line to ooo/vba/excel/XApplication.idl just before the line that reads “any Worksheets( [in] any aIndex );”:
any Sheets( [in] any aIndex );
Now build and deliver the oovbaapi module. Next, go to the sc module, open the source/ui/vbaapplication.hxx file and add the prototype for Sheets:
virtual css::uno::Any SAL_CALL Sheets( const css::uno::Any& aIndex ) throw (css::uno::RuntimeException);
…which is similar to the Worksheets function.
Go to source/ui/vbaapplication.cxx and add the definition for the above function:
uno::Any SAL_CALL ScVbaApplication::Sheets( const uno::Any&aIndex ) throw (uno::RuntimeException)
{
return getActiveWorkbook()->Worksheet(aIndex);
}
The ooo-build/test/macro/ directory contains many xls and ods files to test VBA features. To test this hack, open any available Excel document and add the following VBA code in an existing or new module:
MsgBox Application.Sheets(1).Name Application.Sheets(2).Name="Hello"
To add a new VBA Object, say Sample, create XSample.idl in oovbaapi/ooo/vba/excel/. Add the concerned entry in makefile.mk, then build and deliver in oovbaapi. Next, create vbasample.hxx and vbasample.cxx in sc/source/ui/vba/ with an addition of vbasample.o to makefile.mk, followed by build and deliver in sc.
HelperAPI
HelperAPI is a parallel solution to support VBA in Star Office, which is Sun’s proprietary offering based on OOo. But with the agreement of Sun and Novell to work together on VBA, Sun has donated HelperAPI and made it open now. HelperAPI is implemented in Java, whereas oovbaapi are C++ based. But still, understanding HelperAPI can help one to implement VBA better and avoid working from scratch.
So you now have the basic idea on adding the VBA property. I hope you can now start building Go-oo and add some other missing features. Patches can be sent to Noel Power (noel dot power at novell dot com), leader of the VBA Interoperability Project.
Acknowledgements
I would like to thank Noel Power for his constant mentoring support in the VBA project; Shreyas Srinivasan, former Go-oo team member, for the initial support and mentorship on the OOo project without which I couldn’t have imagined myself with Ooo; and Michael Meeks, leader of the Go-oo team, for the kind support towards OOo contributions.
References
- go-oo.org
- wiki.services.openoffice.org/wiki/VBA
- api.openoffice.org/source/browse/api/helperapi
- #go-oo channel or irc.freenode.net
- blogs.sun.com/GullFOSS/entry/sun_and_novell_work_together
