Excel VBA / DAO object model compatibility

Jan 8, 2012 at 10:46 PM

Hi Sebastian,

We had a go last on Friday at porting a small VBA add-in that also uses DAO for data access to VB.NET with Excel-DNA and the NetOffice assemblies. It went pretty well, with no nasty surprises.

We have some questions (we were using NetOffice version 1.3, not the latest sources):

* The Recordset.Update method in DAO takes two optional parameters. In the corresponding NetOffice method the parameters are required. (http://msdn.microsoft.com/en-us/library/bb221108(v=office.12).aspx).

* In Excel, the Worksheet.Range property is an indexed property which NetOffice exposes only as Worksheet.get_Range. I know that C# had issues with indexed properties before C# 4, but there should be no issue from VB.NET. For example Range.Value seems to work right.

* In VBA the DAO library brings a bunch of methods and constants into the global namespace, for example an OpenDatabase method. You can see these from the VBA Object Browser when selecting the DAO library and looking at <globals>. This allows one to call OpenDatabase directly from the VBA code, without having an object reference. In VB.NET you can do the same with static methods - by 'Importing' a .NET type (instead of a namespace), the static methods of that type become accessible in VB.NET without needing the type qualifier. So a static method 'OpenRecordset' on some DAO.Globals class would be directly available as from VBA.

Do you think it would be possible to make these <globals> exported from a NetOffice type which could bring them in directly into VB.NET? I'm not sure how the COM coclass / interface story works in the back. The Excel API has the same story, with a <globals> section containing ActiveSheet, ActiveCell etc.

* One trick was that to instantiate the DAO DBEngine object, we couldn't just use 'New DBEngine()' but actually had to add the ProgId: 'New DBEngine("DAO.DBEngine.36")'. I think this has to do with DAO not registering a version-independent ProgId.

* I know .NET does not support 'default properties', but in some cases it might make for very nice compatibility with VBA if one can simulate the default collections and their indexers. For example, on a DAO recordset one can say:

myRs!MyField = "New Value".

which actually means

myRS.Fields!MyField.Value = "New Value" - or equivalently  myRs.Fields("MyField").Value = "New Value"

Note that VB.NET still supports the "!" indexed property dereferencing.

I was wondering if it would make sense to generate an indexed property for the Recordset which shortcuts to the default collection's indexed property. Then one could at least write:

myRS!MyField.Value = "New Value".

(This is not a suggestion yet - just a thought on what might be possible. I think rewriting the VBA / VB.NET to add the default properties explicitly is fine, and is cool since it is compatible with with both VBA and VB.NET. )

 

We packed the Excel-DNA / NetOffice add-in with the new VB.NET assembly into a single-file .xll using ExcelDnaPack.exe. With the various NetOffice assemblies for Excel and the DaoApi.dll, the resulting packed .xll was about 750 KB.

 

As a general thought, I was thinking that the initial goal for NetOffice was probably compatibility with the Primary Interop Assemblies. But from my point of view, having NetOffice make the VBA -> VB.NET porting experience better is even more valuable. And these are not necessarily the same goals.

 

Thanks again for the great project!

-Govert 

Coordinator
Jan 8, 2012 at 11:50 PM

Hello Govert,

Yes i see these 2 parameters are optional, i have no idea at the moment why its not optional in NetOffice.
i need a day to see what happens.

worksheet has no enumerator, and thats the reason for my codegenerator creates no default property here.
i change them and make it better.

static methods ... shame on me, i never think about that, NetOffice supports code moduls as static classes but no other static methods.
i need some days to checkout the COM Type Libraries for that info und how i can perform a latebinding call without a specific instance.

thanks for your very good sugestions govert
i give you an update when its done.

*Sebastian

 

 

 

 

Coordinator
Jan 9, 2012 at 12:08 AM

first update:

the DAO COM Type Library tolds me these parameters are not optional but i see also now these parameters has default values(1 and false) and i think thats the reason you can use these parameters in VBA as optional. my codegenerator checks only for the optional flag currently and i have to change that.

*Sebastian

Coordinator
Jan 9, 2012 at 12:47 AM

second update:

>> In Excel, the Worksheet.Range property is an indexed property

Worksheet has no default property.  you can use the ITypeLib Viewer also know as OLE/COM Object Viewer and see Worksheet has no _Default member.
i change that at hand in my xml source file in this case and make Range to an indexer, as far known the interop assemblies has a special COM attribute/annotation for default members. tomorrow i take a look with the .NET Reflector and when its true, i put these attribute also to the default members in NetOffice.

*Sebastian

Jan 9, 2012 at 6:08 AM
Edited Jan 9, 2012 at 6:46 AM

I don't mean that Worksheet.Range should become an indexer or default property on Worksheet. It isn't that way in VBA.

Currently NetOffice Worksheet.Range is not exposed as a .NET property, only via "get_Range". I want this code to work in VB.NET:

Sub Test()
    Dim ws As Worksheet
    Dim r As Range
    Set ws = Application.ActiveSheet
    Set r = ws.Range("A1")
    r.Value = "XXX"
End Sub

And then with the "Globals" we can possibly figure out how to make this work:

Imports NetOffice.ExcelApi.Global

Sub Test()
    Dim ws As Worksheet
    Dim r As Range
    Set ws = ActiveSheet
    Set r = ws.Range("A1")
    r.Value = "XXX"
End Sub

-Govert

Coordinator
Jan 9, 2012 at 11:46 PM

hey govert!

i create 2 issues for Recordset.Update and avoid the "get_" prefix.
a release preview (release 1.4) with fixed issues is available in the download section.
please take a look an give me a response, its okay for the vba community.

as next step i will see what can i do with the static methods. i see the interop assemblies doesnt support them and the OLE Viewer dont show me a <global> namespace. i'm very confused...

*Sebastian

Jan 10, 2012 at 6:13 AM

Thanks Sebastian. I appended some comments to those issues, and will look out for the next version. It is certainly not urgent.

About <global>: I see in the COM type library (e.g. for Excel) that the coclass Global is marked with an "appobject" attribute. Maybe that is what tells VBA to hook up a Global object (with interface _Global) to the application object, and since it is in the same COM library, it knows what kind of Application object to hook up. But the "appobject" attribute seems to be the key.

So one possible approach is to have the a class with static members for everything in _Global and then have some static method to tell it the underlying "Application" object to work against. I think all the _Global methods just delegate the call to the Application object. _Global is also hidden (you could mark with EditorBrowsable(Never)).  

 

For the constants exposed to VBA through <global>, we'd just need a class (possible sealed and marked with [StandardModule] with all the enum values exported as static constants. I'm just worried it bloats the assemblies a lot if you do this. If that's a problem, what about having it in a separate helper assembly?

Jan 10, 2012 at 8:15 AM

Hi Sebastian,

OK, I've looked at the preview version 1.4. 

 

The fixes you make for Recordset.Update and Worksheet.Range("A1") work perfectly, thank you.

I noticed an issue with the IEnumerable<T> change, which I posted as a new discussion.

-Govert

 

(It's a bit confusing on Codeplex if you use the same 'Download' entry for different versions. Maybe you can make a new 'Download' entry for the next version instead of re-using and renaming the current release?)