How to get IDispatch information from a variant property in a Delphi com object in VBA?

I have a COM object writt开发者_JAVA百科en in Delphi that has a property that returns a variant. Basically this property returns a value depending on the parameter I pass it. When I access the object from VBA (Excel for example), I can write something like :

MyObject.MyProperty("IntProperty") = 22

Now the property can also return an IDispatch object, which is stored in the variant. If I access the com object from Delphi, I write the following code to extract that IDispatch information

var
  Info : IMyInterface;

Info := IDispatch(TVarData(MyObject.MyProperty['InfoProperty']).VDispatch) as IMyInterface;

Info.foo := 10;
info.y   := 'test';
info.saveit;

Is it possible to extract that IDispatch information in VBA? I haven't figured out a way to do it yet.

To be 100% clear, the property is of type OLEVariant, and not IDispatch. I have properties that are of type IDispatch, and they work fine.

This is the declaration of get_MethodProperty

function get_MethodProperty(const aPropertyName: WideString):OLEVariant;

It would work if I declared it as

function get_MethodProperty(const aPropertyName: WideString):IDispatch;

But that is not what I want.

This is the VBA code, and it fails on the second line

Dim Info as Object
Set Info = MyObject.MethodProperty("InfoProperty")
Info.foo = 10
Info.y = "test"
call info.saveit

I'm not quite sure what you mean by "extract the IDispatch information". VBA should be able to use a variant containing an IDispatch just fine. Simple tests include:

1. If you call info.xxx from vba, does xxx show up in your GetIDsOfNames?
2. If you call VarType(info) from vba, is the result vbObject? (== varDispatch)

You can "extract" IDispatch in VBA like this

Dim info As Object
Set info = MyObject.MyProperty("IntProperty")
info.foo = 10
info.y = "test"
info.saveit

As far as I know if IDispatch is involved you are using late-binding and therefore I think something like

Set info = CreateObject('WhatEverYourLibraryIs')

is missing (maybe this microsoft link about Using early binding and late binding in Automation will help too)

I tried it in one of my automation servers and it worked fine in VBA (Excel). My implemetation in Delphi looks like this:

Get method in the main object:

function TApplication.Get_MethodProperty(const aPropertyName: WideString): OleVariant;
begin
  if aPropertyName = 'IntProperty' then begin
    result := 42;
  end else if aPropertyName = 'InfoProperty' then begin
    result := TInfoObject.Create as IDispatch;
  end;
end;

The TInfoObject declaration:

  TInfoObject = class(TAutoObject, IInfoObject)
  protected
    function Get_foo: Integer; safecall;
    procedure Set_foo(Value: Integer); safecall;
    function Get_y: WideString; safecall;
    procedure Set_y(const Value: WideString); safecall;
    procedure saveit; safecall;
  end;

The TInfoObject implmentation:

{ TInfoObject }

function TInfoObject.Get_foo: Integer;
begin
  result := 123;
end;

function TInfoObject.Get_y: WideString;
begin
  result := 'info';
end;

procedure TInfoObject.Set_foo(Value: Integer);
begin
  // NYI
end;

procedure TInfoObject.Set_y(const Value: WideString);
begin
  // NYI
end;

procedure TInfoObject.saveit;
begin
  ShowMessage('saveit');
end;

The VBA test code:

Dim Info As Object

Set Info = MyObject.MethodProperty("InfoProperty")

Info.foo = 10
Info.y = "test"

Call Info.saveit

If this doesn't work in your app can you please provide me with your VBA error message.