Difference between revisions of "VBA"

Revision as of 12:04, 20 November 2006

Visual Basic for Applications (VBA) is an implementation of Microsoft's Visual Basic which is built into all Microsoft Office applications.

Ooo-build contains a new VBA model which allows you to run macros from your Microsoft Office documents. This model is under heavy development by Novell, and others, and it is expected that many macros won't run for now.

How to enable the VBA model

The VBA model is enabled by default in the current ooo-build. It's enough to get, build, install and be able to run it.

How to disable the VBA model

Not Known. Please document the process for disabling this "feature". There are many people, particularly since the Novell/Microsoft deal, who are both wary of the potential for tainting Open Source software and wary of the possibility of allowing harmful code to run.

It would be far better not to have this code on a machine where it is not required and not run the potential risks, than have it on the machine with the possibility of having interlocks bypassed by malicious code which could then use it.

How can I help you?

We need help in a couple of areas - Upstreaming, understanding the VBA usage patterns, implementation and testing; see below.

Upstreaming

Ideally we would like this work to be integrated upstream so that its easier to work on, easier to maintain and benifits the maximum amount of users. We'll need your support to kick start this, imagine native support for VBA macros from OpenOffice, it would be great to see that barrier to OpenOffice migration lowered if not lifted entirely.

For the purposes of up-streaming the work really breaks down into a small set of changes to the core basic code - to support the various core features; and then a much larger set of UNO interfaces and implementations of a compatible Object Model.

Core Changes

Recently we have moved almost the entirety of the vba patches from ooo-build into issuezilla.

This is not an exhaustive list, but gives some of the most critical pieces to get up-stream.

Object Model bits

This patch set is far larger, and is bracketed under a single issue: 68883, the patches are in the ooo-build vba directory.

Understanding the VBA usage patterns

VBA model is huge and we need help in understanding the VBA usage patterns for prioritizing as we progress. It's important to receive as many Microsoft Office documents with macros as possible for analysis. We are interested in documents from developers and regular people too.

Dumping the VBA from your MS Office documents

libgsf

If you're worried about the sheet data you can use test-msvba from the libgsf. Download the latest test-msvba.c file and compile it with the following command:

gcc `pkg-config --libs --cflags libgsf-1` -o test-msvba test-msvba.c

Note: you need to have libgsf-devel package installed.

Now, you can dump the VBA from your documents with the following command:

find -name '*.xls' -exec sh -c 'test-msvba {} >> ~/excel-vba-stream-for-noel.bas' \;

pole

Or you can use POLE with GUI. POLE is a portable C++ library to access structured storage. It is designed to be compatible with Microsoft structured storage, also sometimes known as OLE Compound Document.

Exporting modules from the Microsoft Excel

Open your document and edit associated macros. You can see list of modules on the left side of your screen. Use the right mouse button and select Export from the context menu. The whole module will be saved as one huge *.bas.

Where to send

If you are going to send pure VBA streams, send them directly to [Noel dot Power at Novell dot com].

Implementation

The desired sequence

Add an extension to the IDL

The IDL lives in offapi/org/openoffice/vba/*, the IDL syntax can easily be gleaned from a few other IDL files. Two things are worth bearing in mind:

• it's important that the argument names are correct, so that the MsgBox title:="hello" syntax works correctly.
• for optional arguments, or variant types you need to use the any type

Having edited the IDL extension you need to:

• run: build && deliver in offapi (perhaps remove types.db first),
• run: build && deliver in the offuh toplevel directory. (This (re-)compiles C++ headers etc. for that interface).

Add the extension to the implementation

The impl. code lives in sc/source/ui/vba/ - find the right header & source files. Add the method (with the correct prototype) to the header - often it's easiest to cut/paste that from the compiled C++ headers in solenv/*/*/inc/org/openoffice/vba/

Then implement the method itself in the .cxx file. Run build debug=true

Add new constants to the IDL

VBA has alot of constants, we need help implementing similar constants in our api, implementing VBA constants is easy and would be a good place to start if you want to make an immediate contribution and learn about the project at the same time. To see the values and names for the VBA constants just open the "Object Browser" from the Visual Basic IDE, search for constants and you'll find 'em. Look in offapi/org/openoffice/vba/*/* to see whats missing (lots!!) Create a new IDL file for each "class" of constants, use offapi/org/openoffice/vba/Excel/XlCellType.idl as an example. The steps for building and adding the new constants is the same as described above for a extension, the only difference is that there are no source files to create :-)

Running it

You did use [linkoo] right ? - in which case - just re-run soffice.bin (killall -9 soffice.bin first to ensure there is no lurking factory process).

Now of course - you want to add a new test to exercise it to ooo-build/test/macro/*. And of course loop back to the begginning. Once you've got something you want to share - a simple cvs diff -u | tee /tmp/foo.diff in ooo-build/scratch will yield a patch you can mail to Noel / the ooo-build mailing list to get your code folded in. NB. it's important you sign the JCA first.

The scratch directory

 It's quite difficult to generate patches over already patched tree thus we have the ooo-build/scratch directory. Read the ooo-build/scratch/README file to understand how the scratch directory works.
 Then run scratch/place /root-of-working-copy-of-ooo-source which will install & link these directories back - making it easy to generate a diff with 'cvs diff -u' &/or commit to ooo-build.

Testing

Brainstorming

Maybe, it's a good idea to write test cases directly in the Microsoft Excel?

Accessing the VBA API from other languages

The VBA API is implemented by an UNO service, the service is accessable via a singleton. As such it is possible to use the API from any language that has an UNO binding. The main entry points to the API are certain global variables { Application, ActiveWorkbook and ActiveSheet } or methods { WorkSheets, WorkBooks } exported by the service. Using these globals as a starting point the rest of the api can be accessed.

Python

vba = uno.getComponentContext().getByName("/singletons/org.openoffice.vba.theGlobals")
sheet = vba.ActiveSheet
book = vba.ActiveWorkbook

Java

import org.openoffice.vba.*;
"
// getComponentCtxFromSomewhere is a fictional
// routine representing acquisition of the component context
// from e.g. remote ServiceManage if the code is a remote client
// or from the XScriptContext if this were a java ScriptingFramework script 

XComponentContext ctx = getComponentCtxFromSomewhere(); 
Object oGlobs = ctx.getValueByName(
                "/singletons/org.openoffice.vba.theGlobals");
XGlobals xGlobs = AnyConverter.toObject(
                new Type(XGlobals.class), oGlobs);
XWorkBook xWBook =  xGlobs.getActiveWorkbook();
XWorkSheet xSheet = xGlobs.getActiveWorkSheet();

OO Basic

Basic allows access to the global variables automatically just like VBA does, there is no need to create any services or access any singletons to use the API

msgbox "Name of the current document is " & ActiveWorkbook.Path