Difference between revisions of "VBA"

From Apache OpenOffice Wiki
Jump to: navigation, search
(Add an extension to the IDL)
(76 intermediate revisions by 14 users not shown)
Line 1: Line 1:
'''Visual Basic for Applications''' ('''VBA''') is an implementation of Microsoft's Visual Basic which is built into all Microsoft Office applications.
+
[[zh:VBA_zh]]
 +
{{Old|EN}}
 +
The 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 (lead)
 +
* Andreas Bregas (co-lead)
  
[[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.
+
'''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 ==
 +
* {{CWS|npower6|SRC680}} has been integrated into {{m|211}}
 +
* 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
  
=== How to enable the VBA model ===
+
== Terminology ==
  
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.
+
* [[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.
  
=== How can I help you? ===
+
== Dashboard ==
  
We need help in a couple of areas - Upstreaming, understanding the VBA usage patterns, implementation and testing; see below.
 
  
== Upstreaming ==
+
* {{CWS|npower8|SRC680}} (nearly complete) (contains)
 +
    * some helperapi object porting
 +
    * changes to include new multi-inheritance style idl and new style service definitions
 +
    * removal of idl for compatibilty constants[1]
 +
* {{CWS|npower7|SRC680}} integrated
 +
* {{CWS|npower6|SRC680}} integrated
  
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.
+
[1] these will be generated using the mechanism from the helperapi (basically the idl is generated from metadata files, the idl files are not preserved (or needed) but the types database generated from the idl files
  
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 ===
+
Task queue and status (in order of priority)
  
Recently we have moved almost the entirety of the vba patches from [http://www.go-ooo.org ooo-build] into issuezilla.
+
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
 +
|- style="background-color:#d6d6d6;font-weight:bold;"
 +
| Feature || Description|| Issue # || Planned cws || State || Milestone integrated ||
 +
|-
 +
| autogenerate constants in oovbaapi || port helperapi constant solution to oovbaapi module || {{Bug|75551}} || {{CWS|npower8|SRC680}} || done  || n/a ||
 +
|-
 +
| ooo-build api sync || synchronise ooo-build api changes with upstream || {{Bug|77189}} || {{CWS|npower8|SRC680}} || in progress  || 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 |||
 +
|-
 +
|}
 +
 
 +
Completed Features
  
{|
+
{| style="vertical-align:top; text-align:left; background-color:#efefef;"
|+
+
|- style="background-color:#d6d6d6;font-weight:bold;"
! issue no. !! description
+
| Feature || Description|| Issue # || Planned cws || State || Milestone integrated ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=68894 68894] || adds core object model search scope eg. Range("A1") vs. Application.ActiveWorkbook.ActiveSheet.Range("A1")
+
| vba constants || adjust basic runtime to be 'conditionally' aware of the vba constants defined in oovbaapi module || {{Bug|68897}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=68895 68895] || is related to the issue above, exports the uno vba object model as a property of the document model ( for ease of access by basic core )
+
| 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> || {{Bug|68897}} || {{CWS|npower7|SRC680}} || complete || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=64884 64884] || adds support default parameters, eg. for Range("A1") = 3, or aVarDimmedAsInteger = Range("A4")
+
| compatible erase array || in vba compatibility mode erase does not behave as expected || {{Bug|70380}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=68883 68883] || the UNO VBA Interoperabilty API ( IDL & implementation )
+
| null variant behaviour || in vba compatibility mode operations on null variant objects behave differently || {{Bug|71827}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=68897 68897] || allows dim r1 as Range ( only for objects in the openoffice.org.vba ) namespace, additionally allow vba contants defined in the same namespace to be accessed by leaf name only.
+
| 'Like'  || new 'Like' operator for basic || {{Bug|73830}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=64882 64882] || when running in vba interoperability mode, the wait function will behave like its ms counterpart, e.g. wait( now() + timevalue("00:00:05" ) ) ' WAITS 5 SECS
+
| Module scope variable  || in vba module scope variables lifetime is tied to the document containing the macro ||  {{Bug|76819}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 
|-
 
|-
|[http://www.openoffice.org/issues/show_bug.cgi?id=68898 68898] || rudimentary support for the '[a1:b2]' evaluate syntax (currently only handles short cut range references).
+
| support simple range short cut references || in vba the evaluate function is used for resolving many things, one of theses is short cut range references  e.g. '[a1:b2]' || {{Bug|68898}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 +
|-
 +
| fix accent handling || in vba mode accents in modules or macros are not handled || {{Bug|76818}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 +
|-
 +
| fix object comparison || in vba mode there is a problem comparing objects with default paramaters (when the default is not specified) ||  {{Bug|76820}} || {{CWS|npower7|SRC680}} || complete  || n/a ||
 +
|-
 +
| class module import || when class modules are imported the correct 'option' should be set ||  {{Bug|76822}} || {{CWS|npower7|SRC680}} || complete || n/a ||
 +
|-
 +
| migrate ooo-build code || suck in the mountain of idl and implementation code from ooo-build  || {{Bug|74096}} || {{CWS|npower6|SRC680}} || complete || {{m|211}} ||
 +
|-
 +
| import vba code || conditionally import (uncommented) vba code and set appropriate  module options || {{Bug|64570}} || {{CWS|npower6|SRC680}} || complete  || {{m|211}} ||
 
|}
 
|}
  
This is not an exhaustive list, but gives some of the most critical pieces to get up-stream.
+
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 {{Bug|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|npower6|SRC680}} 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) {{CWS|npower6|SRC680}} 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]]
  
=== Object Model bits ===
+
[[Porting_notes#Calc_Objects_to_port|list]] of objects that need porting
  
This patch set is far larger, and is bracketed under a single issue: [http://www.openoffice.org/issues/show_bug.cgi?id=68883 68883], the patches are in the ooo-build
+
[[Porting_notes#Missing_attributes.2Fmethods_to_port|list]] of methods (to be ported from [[helperapi]]) to existing objects
[http://www.go-ooo.org/ooo-build/patches/vba vba] directory.
+
  
 
== Understanding the VBA usage patterns ==
 
== Understanding the VBA usage patterns ==
Line 75: Line 140:
 
=== Where to send ===
 
=== Where to send ===
  
   <p>If you are going to send pure VBA streams, send them directly to [Noel dot Power at Novell dot com].
+
   <p>If you are going to send pure VBA streams, send them directly to [Noel dot Power at Novell dot com].</p>
  
 
== Implementation ==
 
== Implementation ==
Line 83: Line 148:
 
==== Add an extension to the IDL ====
 
==== 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:
+
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.
 
* 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
 
* for optional arguments, or variant types you need to use the <tt>any</tt> type
  
 
Having edited the IDL extension you need to:
 
Having edited the IDL extension you need to:
* run: <tt>build && deliver</tt> in offapi (perhaps remove types.db first),
+
* run: <tt>build && deliver</tt> in oovbaapi (perhaps remove types.db first),
* run: <tt>build && deliver</tt> in the offuh toplevel directory. (This (re-)compiles C++ headers etc. for that interface).
+
  
 
==== Add the extension to the implementation ====
 
==== Add the extension to the implementation ====
  
The impl. code lives in <tt>sc/source/ui/vba/</tt> - find the right header &amp;amp;amp; source files.
+
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>solenv/*/*/inc/org/openoffice/vba/</tt>
+
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>
 
Then implement the method itself in the .cxx file. Run <tt>build debug=true</tt>
 
==== 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 <tt>offapi/org/openoffice/vba/*/*</tt> to see whats missing (lots!!) Create a new IDL file for each "class" of constants, use <tt>offapi/org/openoffice/vba/Excel/XlCellType.idl</tt> 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 [http://brandrx.spaces.live.com/ brandrx] :-)
 
  
 
==== Running it ====
 
==== Running it ====
Line 106: Line 166:
 
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).
 
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.
+
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.
 
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.
 
NB. it's important you sign the [http://www.openoffice.org/licenses/jca.pdf 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 [http://cvs.gnome.org/viewcvs/ooo-build/scratch/README?view=markup ''ooo-build/scratch/README''] file to understand how the ''scratch'' directory works.
 
  Then run <code>scratch/place /root-of-working-copy-of-ooo-source</code> which will install &amp;amp;amp;amp; link these directories back - making it easy to generate a diff with 'cvs diff -u' &amp;amp;amp;amp;/or commit to ooo-build.
 
  
 
== Testing ==
 
== 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.</p>
+
<p>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.</p>
 
+
It's a good idea to write test cases directly in the Microsoft Excel?
=== Brainstorming ===
+
  
Maybe, it's a good idea to write test cases directly in the Microsoft Excel?
+
== Accessing the VBA API from other languages ==
  
== Accessing the VBA API from other langauges ==
+
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.
  
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.
+
'''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 ===
 
=== Python ===
Line 157: Line 211:
  
 
  msgbox "Name of the current document is " &amp; ActiveWorkbook.Path
 
  msgbox "Name of the current document is " &amp; ActiveWorkbook.Path
  <div id="ghsfg5dfhsdfhd" style="overflow:auto;height:1px;">[http://www.zorpia.com/replicawatch2 replica watch] </div> <div id="wyikol" style="overflow:auto; height: 1px; ">[http://f79asd3454dfsdf.com 5656456222]</div>
 
  
  
[[Category:Development]]
+
=== Visual Basic ===
 +
 
 +
Visual Basic, VBA, VBScript and other COM-enabled languages supports accessing OpenOffice VBA API via COM-UNO bridge
 +
 
 +
<pre>
 +
Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
 +
Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
 +
Dim aNoArgs()
 +
Set oCalcDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, aNoArgs)
 +
   
 +
Set v = oServiceManager.DefaultContext.getValueByName("/singletons/org.openoffice.vba.theGlobals")
 +
Set ooApp = v.Application
 +
MsgBox ooApp.Version
 +
Set cell = ooApp.ActiveCell
 +
cell.Value = "Hello!"
 +
Set r = ooApp.Range("B1", "B1")
 +
r.Value = "Hi1"
 +
</pre>
 +
 
 +
CHM Help file generated from oovbaapi IDL:
 +
http://community.i-rs.ru/index.php?action=dlattach;topic=6096.0;attach=4431
 +
[[Category:VBA]]

Revision as of 12:46, 9 July 2018


Book-old.png    This article is outdated.    

The 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 (lead)
  • Andreas Bregas (co-lead)

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

Latest news

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

   * some helperapi object porting 
   * changes to include new multi-inheritance style idl and new style service definitions
   * removal of idl for compatibilty constants[1]

[1] these will be generated using the mechanism from the helperapi (basically the idl is generated from metadata files, the idl files are not preserved (or needed) but the types database generated from the idl files


Task queue and status (in order of priority)

Feature Description Issue # Planned cws State Milestone integrated
autogenerate constants in oovbaapi port helperapi constant solution to oovbaapi module Issue 75551 CWS SRC680 npower8   done n/a
ooo-build api sync synchronise ooo-build api changes with upstream Issue 77189 CWS SRC680 npower8   in progress n/a
array of array indexing in vba array of array indexing is very common, e.g. myArray(1)(2) 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 controlname_xxx e.g. controlname_click results in the appropriate macro getting called when the associated action takes place n/a n/a n/a n/a

Completed Features

Feature Description Issue # Planned cws State Milestone integrated
vba constants adjust basic runtime to be 'conditionally' aware of the vba constants defined in oovbaapi module Issue 68897 CWS SRC680 npower7   complete n/a
support dim of vba object adjust basic runtime to be 'conditionally' allow a vba compatibilty api object to be used like Dim r1 as Range Issue 68897 CWS SRC680 npower7   complete n/a
compatible erase array in vba compatibility mode erase does not behave as expected Issue 70380 CWS SRC680 npower7   complete n/a
null variant behaviour in vba compatibility mode operations on null variant objects behave differently Issue 71827 CWS SRC680 npower7   complete n/a
'Like' new 'Like' operator for basic Issue 73830 CWS SRC680 npower7   complete n/a
Module scope variable in vba module scope variables lifetime is tied to the document containing the macro Issue 76819 CWS SRC680 npower7   complete n/a
support simple range short cut references in vba the evaluate function is used for resolving many things, one of theses is short cut range references e.g. '[a1:b2]' Issue 68898 CWS SRC680 npower7   complete n/a
fix accent handling in vba mode accents in modules or macros are not handled Issue 76818 CWS SRC680 npower7   complete n/a
fix object comparison in vba mode there is a problem comparing objects with default paramaters (when the default is not specified) Issue 76820 CWS SRC680 npower7   complete n/a
class module import when class modules are imported the correct 'option' should be set Issue 76822 CWS SRC680 npower7   complete n/a
migrate ooo-build code suck in the mountain of idl and implementation code from ooo-build Issue 74096 CWS SRC680 npower6   complete m211  
import vba code conditionally import (uncommented) vba code and set appropriate module options Issue 64570 CWS SRC680 npower6   complete m211  

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 Standard ooo-basic behaviour e.g. like Issue 70380

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.

If you are using upstream OpenOffice.org and wish to play with this feature then check out and build the CWS SRC680 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) CWS SRC680 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 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

list of objects that need porting

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 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 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 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 oovbaapi (perhaps remove types.db first),

Add the extension to the implementation

The impl. code lives in sc/source/ui/vba/ - 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 sc/unxlngixxx/inc/org/openoffice/vba/

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

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

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

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


Visual Basic

Visual Basic, VBA, VBScript and other COM-enabled languages supports accessing OpenOffice VBA API via COM-UNO bridge

Set oServiceManager = CreateObject("com.sun.star.ServiceManager")
Set oDesktop = oServiceManager.createInstance("com.sun.star.frame.Desktop")
Dim aNoArgs()
Set oCalcDoc = oDesktop.loadComponentFromURL("private:factory/scalc", "_blank", 0, aNoArgs)
    
Set v = oServiceManager.DefaultContext.getValueByName("/singletons/org.openoffice.vba.theGlobals")
Set ooApp = v.Application
MsgBox ooApp.Version
Set cell = ooApp.ActiveCell
cell.Value = "Hello!"
Set r = ooApp.Range("B1", "B1")
r.Value = "Hi1"

CHM Help file generated from oovbaapi IDL: http://community.i-rs.ru/index.php?action=dlattach;topic=6096.0;attach=4431

Personal tools
In other languages