VBA zh

From Apache OpenOffice Wiki
Revision as of 08:10, 15 March 2007 by Jiao.jianhua (Talk | contribs)

Jump to: navigation, search

en

简介

为了能够在OpenOffice.org办公套件中运行Excel和Word文档引入的VBA宏,Novell和Sun联合发起了 VBA VBA 解释器项目。

  • Noel Power ( co-lead )
  • Andreas Breagas ( co-lead )

Visual Basic for Applications (VBA) 是微软(Microsoft)内建在其办公套件中[wikipedia:Visual Basic|Visual Basic]]的实现。

原文: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 Visual Basic which is built into all Microsoft Office applications.


新闻

原文:

  • Update Wiki Page with latest info
  • 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 npower6 will contain the initial code drop

术语

helperapi - Sun公司慷慨捐赠。是其迁移工具的一部分,兼容微软VBA调用。 oovbaapi - Novell在ooo-build中首先实现VBA解释器解决方案,并引入了helperapi实现的对象。 Note: 在OpenOffice.org中运行微软办公套件文档宏,最新的VBA模型和补丁可以在Ooo-build中找到。该模型主要由Novell公司开发,并且在提交到openoffice的过程中会不断变化。可以预想,现在仍有很多宏不能运行。

原文:

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

状态

  • cws npower6 接近测试(QA)阶段。
  • cws npower7 已经创建。

任务和状态 ( 按优先级排序 )

任务 描述 问题号 计划的CVS分支(cws) 启动 集成的版本
迁移 Ooo-build 代码 将大量的idl文件和实现代码由ooo-build迁入 74096 npower6 yep n/a
引入VBA代码 设置模块参数,作为启动VBA代码的条件 64570 npower6 yep n/a
VBA常量 调整宏运行时环境,“有条件的”识别定义在oovbaapi中的VBA常量。 68897 npower7 yep n/a
支持Dim定义VBA对象 调整宏运行时环境,“有条件的”允许VBA兼容对象这样使用:Dim r1 as Range 68897 npower7 yep n/a
数组擦除 在VBA兼容模式下,队列擦除方式需要改变 70380 npower7 yep n/a
空变量 VBA兼容模式下的空对象变量操作需要改变 71827 npower7 yep n/a
'Like'运算符 为OOo创建'Like'运算符 73830 npower7 yep n/a
模块变量 在VBA兼容模式下,模块范围中变量的生存期,依赖的是包含宏的文档 n/a npower7? n/a n/a
索引数组的数组 索引数组的数组在VBA中非常普遍,如:myArray(1)(2) n/a n/a n/a n/a
支持控件事件 使表单(form)的事件处理器以controlname_xxx 形式松散帮定(loose bingding)到控件。例如:controlname_click 将会处理与之关联的事件调用。 n/a n/a n/a n/a


还有非常多的任务需要投入更多的工作(并分拆为小的任务加入到列表)

对于提高 标准 ooo-baisc兼容性的条目,可以像[70380]这样创建问题名单。

原文:

Task queue and status ( in order of priority )

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 Dim r1 as Range 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. 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

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]

如何启用VBA模型

当前的ooo-build默认启用VBA模型。在getbuildinstallbe able to run后,VBA就被启用了。

如果你想使用官方版OpenOffice.org,同时希望使用VBA功能。请提取出cws npower6,并编译创建。这样就包含了初始的oovbaapi功能。

原文: 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 npower6 which contains the initial code drop for the oovbaapi

如何关闭VBA模型

不同OpenOffice.org版本提供VBA的方式,决定了禁用的方法。 对于linux发行(或者Novell windows版)的OpenOffice.org套件,如果包含了VBA解释器,那么就可以导航到'Tools.Options.Load/Save.VBAProperties'页面反选'Load Excelbasic code to edit'即可。 如果使用的是你自己创建的包含cws-npower6的版本(混合的),那么VBA功能不能被直接关闭。但是,如果你创建的VBA解释器是作为OpenOffice.org扩展,那么可以通过扩展管理器(Extension Manager)关闭或者删除vbaapi.oxt来禁用VBA功能。

原文: 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 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.

获取帮助

我们在众多的方面需要帮助。下面将会介绍如何迁移helperapi对象,如何理解VBA使用模式,如何实现并测试VBA。

原文: We need help in a couple of areas, porting objects from the helperapi, understanding the VBA usage patterns, implementation and testing; see below.

迁移HelperApi对象

什么是 helperapi?

请查看 porting notes

请查看 porting example

To Do 添加需要迁移的对象列表

To Do 对已存在的对象增加方法(从helperapi迁移)


原文:What is the helperapi?

Have a look at the porting notes

Have a look at a porting example

To Do add list of objects that need porting

To Do add list of methods ( to be ported from helperapi ) to existing objects

理解VBA使用模式

VBA模型十分庞大。我们将理解VBA使用模式作为这个过程中的重点。大量含有VBA宏的微软办公套件的文档,对于研究开发很重要。我们乐于接受任何开发人员或者普通使用者所提供的文档。

原文: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.


从微软办公套件文档中提取出VBA宏

libgsf

如果你不喜欢包含数据页表的文档,可以使用test-msvba提取出VBA部分。下载libgsf和最新的test-msvba.c,使用如下命令进行编译:

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

Note: 必须安装libgsf-devel开发包.

现在,你可以使用下面的命令从文档中提取VBA宏:

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


原文: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

或者你可以使用带有图像界面的POLE。POLE是可以访问结构存储,是一个可迁移的C++库。其兼容微软存储结构,并识别一些OLE文档(OLE Compound Document)

原文:

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.

从微软电子表格输出宏

打开文档并编辑相关宏。在屏幕的左边有一个模块的列表。用鼠标右键打开上下文菜单,点击“输出”。这个模块就被存储为一个大的*.bas文件。

原文:

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

如果你准备提交纯VBA文件,请直接发送到[Noel.Power@novell.com]

原文:

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

实现

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/ - 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 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 ( 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
Personal tools