Marshaling an object from .NET to VBA

[Bamboozled]

Hi Govert,
I'm trying to copy an object (actually, one of the hierarchical
dictionaries that I mentioned a month or so ago) from .NET to VBA.
When I say "copy", I'm not actually bothered whether it is a copy or
simply accessing the same object in memory. The purpose is to be able
to use a viewer tool which is (currently) written in VBA.

I'm just trying to make things work with a test harness at present. It
looks like this:
VBA (with #nums to aid discussion):
Public Sub testhdict()
Dim hdo, hd As HDictCompatibilityClass

#1 Set hdo = Application.Run("hdict_hdictobj")
#2 hdo = Application.Run("hdict_hdictobj")

#3 Set hd = Application.Run("hdict_hdict")
#4 hd = Application.Run("hdict_hdict")
End Sub

C#


[ExcelFunction(Description = "Returns HDICT as object")]
public static object hdict_hdictobj() {
HierarchicalDictLib.HDictCompatibilityClass cls = new
HDictCompatibilityClass();
return cls;
}

//[ExcelCommand(Description = "Returns HDICT as Hdict")]
public static HDict hdict_hdict() {
HDictCompatibilityClass dc = new
HDictCompatibilityClass();
return dc;
}

When I run this, I get
#1 Type Mismatch
#2 Doesn't throw an error, but hdo = Error 2015
#3 Run-time error 1004, Cannot run the macro 'hdict_hdict'. The macro
may not be available in this workbook, or all macros may be disabled.
#4 Same as #3

I'm sure that what I'm trying to do is not impossible, though clearly
the way I'm going about it is wrong. As you can see, I have commented
out the ExcelCommand line in the second example. I have tried that not
commented, and as ExcelFunction also.

I suspect that at least part of the problem is returning an object
other than Object from C# - because it must be possible for the result
to be shown in a cell, the only acceptable returns are numbers,
strings, or error objects (I guess).

Anyway, can you suggest how I might get the object, or a facsimile of
it, into VBA?

Thanks,
Bruce.

[Govert van Drimmelen]

Hi Bruce,

Your diagnosis is correct. The C API only allows us a limited set of
datatypes that a function can return.

You might like to investigate the COM Server support that I added last
year. This allows your Excel-DNA add-in to expose some COM objects
that can be instantiated and used directly from VBA. There is not much
documentation, but you can try the samples in the distribution as a
start, and then search for "ComServer" here and on the CodePlex
discussions.

-Govert

[Bamboozled]

Thanks very much Govert.

> > Bruce.- Hide quoted text -
>
> - Show quoted text -