=== Application Object ===
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|- style="background-color:#d6d6d6;font-weight:bold;"
| method/property name || helperapi idl:implementation || Porting stared by? || Status ||
|-
| NewWorkbook || XCalc.idl:CalcImpl || ||
|-
| Rows || XCalc.idl:CalcImpl || ||
|-
| Columns || XCalc.idl:CalcImpl || ||
|-
| Cells || XCalc.idl:CalcImpl || ||
|-
| ActivePrinter || XCalc.idl:CalcImpl || ||
|-
| Calculate || XCalc.idl:CalcImpl || ||
|-
| DisplayAlerts || XCalc.idl:CalcImpl || || is only a stub in helperapi
|-
| Name || XCalc.idl:CalcImpl || ||
|-
| Intersect || XCalc.idl:CalcImpl || ||
|-
| Run || XCalc.idl:CalcImpl || ||
|-
| Width || XCalc.idl:CalcImpl || ||
|-
| Visible || XCalc.idl:CalcImpl || ||
|-
| Height || XCalc.idl:CalcImpl || ||
|-
| Top || XCalc.idl:CalcImpl || ||
|-
| Left || XCalc.idl:CalcImpl || ||
|-
| Path || XCalc.idl:CalcImpl || ||
|-
| InchesToPoints || XCalc.idl:CalcImpl || ||
|-
| CentimetersToPoints || XCalc.idl:CalcImpl || ||
|-
| DefaultFilePath || XCalc.idl:CalcImpl || ||
|-
| Visible || XCalc.idl:CalcImpl || ||
|-
| LibraryPath|| XCalc.idl:CalcImpl || ||
|-
| TemplatesPath || XCalc.idl:CalcImpl || ||
|-
| DisplayCommentIndicator || XCalc.idl:CalcImpl || ||
|-
| Iteration || XCalc.idl:CalcImpl || ||
|-
| MaxChange || XCalc.idl:CalcImpl || ||
|-
| MaxIterations || XCalc.idl:CalcImpl || ||
|-
| InputBox || XCalc.idl:CalcImpl || ||
|-
| Names || XCalc.idl:CalcImpl || || needs Name & Names implementations
|-
| ConvertFormula || XCalc.idl:CalcImpl || ||
|-
| Assistant || XCalc.idl:CalcImpl || ||
|-
| CommandBars || XCalc.idl:CalcImpl || || needs the CommandBar implementation also
|}

VBA

2007-03-22T11:31:32Z

Npower: /* Porting Objects from the HelperApi */

[[VBA zh|中]]

[http://vba.openoffice.org VBA interoperability] project is a joint Novell & Sun incubator project whose aim is to deliver a way to run Excel and Word macros natively in imported documents.
* Noel Power ( co-lead )
* Andreas Breagas ( co-lead )

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

== Latest news ==

* Update Wiki Page with latest info
* [http://blogs.sun.com/GullFOSS/entry/sun_and_novell_work_together Novell and Sun to work together] on the VBA incubator project
* I am delighted that Andreas Bregas now joins me to co-lead the vba incubator project
* cws [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fnpower6 npower6] will contain the initial code drop

== Terminology ==

* [[helperapi]] - The compatibility API generously donated by Sun that was part of their migration toolkit
* [[oovbaapi]] - the ooo-build vba interoperability solution originally implemented by Novell + object implementations from the helperapi. Note: [[Ooo-build]] contains the latest VBA model and patches which allows you to run macros from your Microsoft Office documents. This model is under heavy development by Novell and others and the changes are continuously ported to openoffice. It is expected that many macros won't run for now.

== Dashboard ==

* cws [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fnpower6 npower6] approaching 'Ready For QA status'
* cws [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fnpower7 npower7] created

Task queue and status ( in order of priority )

{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|- style="background-color:#d6d6d6;font-weight:bold;"
| Feature || Description|| Issue # || Planned cws || Stared || Milestone integrated ||
|-
| migrate ooo-build code || suck in the mountain of idl and implementation code from ooo-build || 74096 || npower6 || yep || n/a ||
|-
| import vba code || conditionally import ( uncommented ) vba code and set appropriate module options || 64570 || npower6 || yep || n/a ||
|-
| vba constants || adjust basic runtime to be 'conditionally' aware of the vba constants defined in oovbaapi module || 68897 || npower7 || yep || n/a ||
|-
| support dim of vba object || adjust basic runtime to be 'conditionally' allow a vba compatibilty api object to be used like <tt>Dim r1 as Range</tt> || 68897 || npower7 || yep || n/a ||
|-
| compatible erase array || in vba compatibility mode erase does not behave as expected || 70380 || npower7 || yep || n/a ||
|-
| null variant behaviour || in vba compatibility mode operations on null variant objects behave differently || 71827 || npower7 || yep || n/a ||
|-
| 'Like' || new 'Like' operator for basic || 73830 || npower7 || yep || n/a ||
|-
| Module scope variable || in vba module scope variables lifetime is tied to the document containing the macro || n/a || npower7? || n/a || n/a ||
|-
| array of array indexing || in vba array of array indexing is very common, e.g. <tt>myArray(1)(2)</tt>|| n/a || n/a || n/a || n/a ||
|-
| toolbox control event support || these controls support a loose binding where the presence of a macro event handler of the form <tt>controlname_xxx e.g. controlname_click</tt> results in the appropriate macro getting called when the associated action takes place || n/a || n/a || n/a || n/a |||
|--
| port helperapi constants || helperapi constants are autogenerated from metadata files, they are more complete than those already present in the oovbaapi module added in npower6 || 75551 || n/a || n/a || n/a ||

|}

Broader scope tasks that need more investigation ( and splitting into smaller tasks ) (to be added)

create list of items that may need promoting from vba compatibility to <tt>Standard</tt> ooo-basic behaviour e.g. like issue [[http://www.openoffice.org/issues/show_bug.cgi?id=70380 70380]]

== How to enable the VBA model ==

The VBA model is enabled by default in the current ooo-build. It's enough to [[Getting It|get]], [[Building with ooobuild|build]], [[Installing|install]], and [[Running|be able to run]] it.

If you are using upstream OpenOffice.org and wish to play with this feature then check out and build the cws [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fnpower6 npower6] which contains the initial code drop for the oovbaapi

== How to disable the VBA model ==

Depending on how your version of OpenOffice.org delivers the VBA feature to you, there are a number of ways to disable it.

If you have an Openoffice.org built by a linux distro ( or a windows one from Novell ) that has the vba interop feature then all you need do is navigate to the 'Tools.Options.Load/Save.VBAProperties' page and uncheck the 'Load Excelbasic code to edit' option.

If you are using an own build ( incorporating ) [http://eis.services.openoffice.org/EIS2/cws.ShowCWS?Path=SRC680%2Fnpower6 cws-npower6] the above method will always work. But if you built the vba interop feature as an office extension then you can of course disable or delete the vbaapi.oxt extension using the Extension Manager.

== How can I help you? ==

We need help in a couple of areas, [[Porting notes|porting]] objects from the [[helperapi]], understanding the VBA usage patterns, implementation and testing; see below.

== Porting Objects from the HelperApi ==

What is the [[helperapi]]?

Have a look at the [[porting notes]]

Have a look at a [[porting example]]

[[Porting_notes#Calc_Objects_to_port|list]] of objects that need porting

[[Porting_notes#Missing_attributes.2Fmethods_to_port|list]] of methods ( to be ported from [[helperapi]] ) to existing objects

== 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 [http://freshmeat.net/projects/libgsf/ libgsf]. Download the latest [http://cvs.gnome.org/viewcvs/libgsf/tests/test-msvba.c 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 [http://pole.berlios.de/ 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 oovbaapi/org/openoffice/*, 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 <tt>MsgBox title:="hello"</tt> syntax works correctly.
* for optional arguments, or variant types you need to use the <tt>any</tt> type

Having edited the IDL extension you need to:
* run: <tt>build && deliver</tt> in oovbaapi (perhaps remove types.db first),

==== Add the extension to the implementation ====

The impl. code lives in <tt>sc/source/ui/vba/</tt> - create the right header and 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 <tt>sc/unxlngixxx/inc/org/openoffice/vba/</tt>

Then implement the method itself in the .cxx file. Run <tt>build debug=true</tt>

==== 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 beginning.
Once you've got something you want to share - a simple <tt>cvs diff -u | tee /tmp/foo.diff</tt> 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 [http://www.openoffice.org/licenses/jca.pdf JCA] first.

== Testing ==

It's extremely important to test the work which is already done. You can use test cases from the ''ooo-build/test/macro'' directory, you can extend them or you can write your own test cases.
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 accessible 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.

'''NOTE:''' Beaware the idl is not stable, ( nor won't be for the forseeable future be 'published' ) it WILL change, so if you are using a language that has a binary dependency on the interface ( like c++ & java ) then consider yourselves warned.

=== Python ===

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

=== Java ===

<pre>
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();
</pre>

=== 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

[[Category:Development]]

Porting notes

2007-03-22T11:27:20Z

Npower: /* Application Object */

=== Things to watch out for ===
* The mapping of the vba constants is different. Take for example the <tt>xlGuess</tt> constant, in VBA it's fully qualified name is <tt>Excel.XlYesNoGuess.xlGuess</tt>, in the helperapi it's <tt>com.sun.star.helper.constant.XlYesNoGuess.xlGuess</tt> and in oovbaapi it's <tt>org.openoffice.excel.XlYesNoGuess.xlGuess</tt>
* All objects in the helperapi extend [http://api.openoffice.org/source/browse/*checkout*/api/helperapi/impl/com/sun/star/helper/HelperInterfaceAdaptor.java?rev=1.1 HelperInterfaceAdaptor], nothing similar (yet) exists in oovbaapi so this can be ignored in the implementation
* There are a quite few helper classes in the helperapi project e.g. <tt>RangeHelper.java</tt> they shouldn't be confused with the actual implementation objects we wish to port.
* classes and idl files of the same name can exist in multiple namespaces and this can be confusing. Because the helperapi was written with both the word and excel api(s) in mind there can be classes that share a common implementation and interfaces e.g.
** <tt>com/sun/star/helper/calc/XShape.idl</tt>
** <tt>com/sun/star/helper/common/XShape.idl</tt>
** <tt>com/sun/star/helper/writer/XShape.idl</tt>
* Collections, these are handled differently see implementing a vba [[Porting notes#Collection|Collection]] in oovbaapi
* Every helperapi idl method defines [http://api.openoffice.org/source/browse/*checkout*/api/udkapi/com/sun/star/script/BasicErrorException.idl BasicErrorException] which allows and api method to transfer an error code and associated string paramater to basic.

=== Hints for porting idl ===
==== Objects with Default Properties ====
This is something not handled by the helperapi but something you should be aware of when implementing a vba compatibility object. What is a default property, an example probably explains the concept better.

Range("a1") = "text"

is a short cut for

Range("a1").Value = "text"

<tt>Value</tt> is a default property in the example above. We can provide similar behavior in basic with a compatibility api object ensuring the object implements [http://api.openoffice.org/source/browse/*checkout*/api/udkapi/com/sun/star/script/XDefaultProperty.idl XDefaultProperty.idl ]

==== Objects with Default Method ====

Similar to above some vba objects have a default method e.g. the <tt>Collection</tt> object

set col = Sheets
msgbox col(1).Name

is a short cut for

set col = Sheets
msgbox col.Item(1).Name

<tt>Item</tt> is a default method in the example above. We can provide similar behavior in basic with a compatibility api object ensuring the object implements [http://api.openoffice.org/source/browse/*checkout*/api/udkapi/com/sun/star/script/XDefaultMethod.idl XDefaultMethod.idl ]

==== Porting Collection objects ====
Generally all collection objects have a <tt>Count</tt> and an <tt>Item</tt> method and additionally the behaviour mentioned above. Also Collection objects can be used in a <tt>For Each</tt> loop. To cater for that a helper implementation object <tt>ScVbaCollectionBaseImpl</tt> provides the necessary information to the basic runtime to allow it to provide the classic vba <tt>Collection</tt> class syntax support. It also requires to be initialised by an XIndexAccess implementation. Additionally the class inheriting from <tt>ScVbaCollectionBaseImpl</tt> must provide the translation between the object iterated ( via XIndexAccess ) and the corrosponding compatibilty object returned to openoffice basic via the the <tt>createCollectionObject</tt> method.

A example of a ported <tt>Collection</tt> object can be found in the [[Porting example]] section

=== Calc Objects to port ===
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|- style="background-color:#d6d6d6;font-weight:bold;"
| Object || helperapi implementation name || Porting stared by || Status || Notes
|-
| XAutoFilter.idl || [http://api.openoffice.org/source/browse/*checkout*/api/helperapi/impl/com/sun/star/helper/calc/AutoFilterImpl.java AutoFilterImpl] || || || there is a partial (untested) implementation in the porting example section, would be nice for some one to finish
|-
| XFilter.idl || [http://api.openoffice.org/source/browse/*checkout*/api/helperapi/impl/com/sun/star/helper/calc/FilterImpl.java FilterImpl.java] || || || see above.
|-
| XFilters.idl || [http://api.openoffice.org/source/browse/*checkout*/api/helperapi/impl/com/sun/star/helper/calc/FiltersImpl.java FiltersImpl.java] || || || see above.
|-
| XAxes.idl || xxxx || || ||
|-
| XAxis.idl || xxxx || || ||
|-
| XAxisTitle.idl || xxxx || || ||
|-
| XCalc.idl || xxxx || || || This is not a 'real' object but in a collection of global method/attributes that probably should be part of the XApplication.idl interface
|-
| XCharts.idl || xxxx || || ||
|-
| XChartTitle.idl || xxxx || || ||
|-
| XDataLabel.idl || xxxx || || ||
|-
| XDataLabels.idl || xxxx || || ||
|-
| XFormatCondition.idl || xxxx || || ||
|-
| XFormatConditions.idl || xxxx || || ||
|-
| XFormat.idl || xxxx || || ||
|-
| XHPageBreak.idl || xxxx || || ||
|-
| XHPageBreaks.idl || xxxx || || ||
|-
| XName.idl || xxxx || || ||
|-
| XNames.idl || xxxx || || ||
|-
| XPageBreak.idl || xxxx || || ||
|-
|[http://api.openoffice.org/source/browse/api/helperapi/com/sun/star/helper/calc/XPageSetup.idl XPageSetup.idl] || [http://api.openoffice.org/source/browse/api/helperapi/impl/com/sun/star/helper/calc/PageSetupImpl.java PageSetupImpl] || zhang ||
|-
|[http://api.openoffice.org/source/browse/api/helperapi/com/sun/star/helper/calc/XPane.idl XPane.idl] || [http://api.openoffice.org/source/browse/api/helperapi/impl/com/sun/star/helper/calc/PaneImpl.java PaneImpl] || Jiao.Jianhua || calc complete||
|-
| XPoint.idl || xxxx || ||
|-
| XPoints.idl || xxxx || ||
|-
| XProtection.idl || xxxx || ||
|-
| XQueryTable.idl || xxxx || ||
|-
| XQueryTables.idl || xxxx || ||
|-
| XShape.idl || xxxx || jiao.jianhua ||
|-
| XShapes.idl || xxxx || ||
|-
| XStyle.idl || xxxx || ||
|-
| XStyles.idl || xxxx || ||
|-
| XTitle.idl || xxxx || ||
|-
| XVPageBreak.idl || xxxx || ||
|-
| XVPageBreaks.idl|| xxxx || ||
|}

=== Common Objects to port ===
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|- style="background-color:#d6d6d6;font-weight:bold;"
| Object || helperapi implementation name || Porting stared by? || Status ||
|-
| XAutoFilter.idl || xxxx || ||
|-
| XPictureFormat || xxxx || ||
|-
| XColorFormat || xxxx || ||
|-
| XPosSize || xxxx || Jiao.Jianhua || implemented in org.openoffice.XControl ||
|-
| XWindow || xxxx || ||
|-
| XFileSearch || xxxx || ||
|-
| XWrapFormat || xxxx || ||
|-
| XFillFormat || xxxx || ||
|-
| XShapeRange || xxxx || ||
|-
| XFoundFiles || xxxx || ||
|-
| XLineFormat || xxxx || ||
|-
| XTextFrame || xxxx || ||
|}

=== Missing attributes/methods to port ===
==== Application Object ====
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|- style="background-color:#d6d6d6;font-weight:bold;"
| method/property name || helperapi idl:implementation || Porting stared by? || Status ||
|-
| NewWorkbook || XCalc.idl:CalcImpl || ||
|-
| Rows || XCalc.idl:CalcImpl || ||
|-
| Columns || XCalc.idl:CalcImpl || ||
|-
| Cells || XCalc.idl:CalcImpl || ||
|-
| ActivePrinter || XCalc.idl:CalcImpl || ||
|-
| Calculate || XCalc.idl:CalcImpl || ||
|-
| DisplayAlerts || XCalc.idl:CalcImpl || || is only a stub in helperapi
|-
| Name || XCalc.idl:CalcImpl || ||
|-
| Intersect || XCalc.idl:CalcImpl || ||
|-
| Run || XCalc.idl:CalcImpl || ||
|-
| Width || XCalc.idl:CalcImpl || ||
|-
| Visible || XCalc.idl:CalcImpl || ||
|-
| Height || XCalc.idl:CalcImpl || ||
|-
| Top || XCalc.idl:CalcImpl || ||
|-
| Left || XCalc.idl:CalcImpl || ||
|-
| Path || XCalc.idl:CalcImpl || ||
|-
| InchesToPoints || XCalc.idl:CalcImpl || ||
|-
| CentimetersToPoints || XCalc.idl:CalcImpl || ||
|-
| DefaultFilePath || XCalc.idl:CalcImpl || ||
|-
| Visible || XCalc.idl:CalcImpl || ||
|-
| LibraryPath|| XCalc.idl:CalcImpl || ||
|-
| TemplatesPath || XCalc.idl:CalcImpl || ||
|-
| DisplayCommentIndicator || XCalc.idl:CalcImpl || ||
|-
| Iteration || XCalc.idl:CalcImpl || ||
|-
| MaxChange || XCalc.idl:CalcImpl || ||
|-
| MaxIterations || XCalc.idl:CalcImpl || ||
|-
| InputBox || XCalc.idl:CalcImpl || ||
|-
| Names || XCalc.idl:CalcImpl || || needs Name & Names implementations
|-
| ConvertFormula || XCalc.idl:CalcImpl || ||
|-
| Assistant || XCalc.idl:CalcImpl || ||
|-
| CommandBars || XCalc.idl:CalcImpl || || needs the CommandBar implementation also
|}

Porting notes

2007-03-22T11:10:06Z

Npower: /* Application Object */