Apache OpenOffice (AOO) Bugzilla – Issue 111144
Can't call Sheet module's function by Sheet object in VBA
Last modified: 2017-05-20 09:32:01 UTC
Can't call Sheet module's function by Sheet object, such as: Sheets(2).Hello "World", "Hello" is a function defined in module Sheet2. Please try the attached sample file.
Created attachment 69105 [details] Test sample for this issue
yep, known problem.... Currently I am looking at this issue and think it can be fixed by a cheap and nasty hack, lets see if it works
got some small success accessing ( from basic->apiobject->module-method ) at least for simple methods ( no support for params yet ), trying to get the same thing working for properties, then will support properties and after that need to clean up the patch a little
>then will support properties and after that .... of course I mean "then will support params.." e.g. methods with paramaters
adding a temporary patch, this patch allows the Worksheet api object to expose the methods, properties and variables of the associated document object via uno. This is sort of an inside out solution which allows the api object to access & expose the associated basic module data. This means when you access a document api object ( like the activesheet ) in a macro and you can also access any data or procedure in document sheet basic module associated with that api object ( see attached document for an example ). * Note: this patch only address the Worksheet object, the same technique will work ( not done yet ) * Note: I am working on the real solution which would allow the Module itself ( including access to the api object ) to be exported as an uno object.
>* Note: this patch only address the Worksheet object... should read Note: this patch only address the Worksheet object, the same applies to the Workbook object
noel, thank you very much for your fix for this issue, I tried, it works well. And I did a little changes based on your fix(vbadocmodule.hxx and vbadocmodule.cxx), please take a look at my changes: 1. Remove the variables from vbadocmodule.hxx: SbMethodRef mpMethod SbPropertyRef mpProperty And add two method: SbMethodRef getMethod( const rtl::OUString& aName ) throw (com::sun::star::uno::RuntimeException) SbPropertyRef getProperty( const rtl::OUString& aName ) throw (com::sun::star::uno::RuntimeException) 2. hasMethod(...) and hasProperty(...) will call getMethod(...) and getProperty(...). Please take a look at the changes, thanks!
Created attachment 69178 [details] Did a little change based on noel's fix
the changes look good to me, I think my attempt at some cheap caching of the method(s)/property(s) was a little optimistic anyway. On the other hand I don't intend to keep this implementation, like I said we really need to make the module itself be able to be represented as an uno object ( and I am currently working on that )
Created attachment 69416 [details] the old patch
Created attachment 69417 [details] new version of the patch ( assumes the previous version doesn't exist )
still it looks like there is far too much accuire/release of the new object, I suspect that every single object that has a Parent object in its ctor needs the signature changed e.g. * old AnApiObject( const uno::Reference< XHelperInterface >&, ... ) * new AnApiObject( const uno::WeakReference< XHelperInterface >&, ... ) hopefully that will ensure that the attempting to convert WeakReference to Reference only be attempted when GetParent() is called ( additionally internally GetParent() shouldn't be called for objects whose parent is a Document Reference ( because these objects should always be in scope and ideally we should be able just use the WeakReference directly )
Created attachment 69421 [details] more missing places to hook in a sheet module as parent
Created attachment 69425 [details] more missing places where parent for range needs to be set ( and replacement of getParent calls )
Created attachment 69434 [details] next version ( includes fall back for access of sheets in other workbook )
Created attachment 69435 [details] this time the real updated patch ( with sheets fallback )
Created attachment 69447 [details] new iteration with code to create the document modules in a new workbook ( from api )
last patch is as they say... "getting there", still some duplication of code that I am not happy about, additionally need to check if the acuire/release storm can be calmed by using WeakReference in ctors.
Created attachment 69448 [details] add one last missing set of parent for a range
Created attachment 69521 [details] more tweaks ( here fix some erroneous project name handling ) - all of this untested of course ;-)
Make me owner...
-> OOo 3.3, FIXED
.
Verified in CWS mib17