Wilmott Forums - Passing VBA (for excel) class to an ATL method

Forum Navigation:

login

FORUMS > Programming and Software Forum

Topic Title: Passing VBA (for excel) class to an ATL method
Created On Sat Aug 04, 12 01:36 PM

Topic View:

View thread in raw text format

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Sat Aug 04, 12 01:36 PM

User is offline

Hi all,

I would like to know how I could pass an instance of VBA (for excel) class to an ATL method (in MVS2010 c++ ATL project with COM reference)
- How should be designed the VBA class ?
- What should be done on the ATL side for this ?

Thank you very much.

Raph

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

Edited: Sat Aug 04, 12 at 03:43 PM by rockinsquat

mdsantos
Junior Member

Posts: 18
Joined: Jan 2007

Sat Aug 04, 12 07:00 PM

User is offline

Ignoring the obvious questions of why...(because I really don't want to know)

Your options are:
1. Pass raw ptr via VBA's hidden objPtr -> Access via COM API
2. Pass IUnknown ptr -> Access via COM API

These are both challenging because you would lack the type-info goodness to make life easier for you in C++ COM land.

Originally you were not open to other solutions, but I will mention one that's still close to your objective.
Remember that good COM citizens always pass interfaces, never classes.
So instead of attempting to pass your Excel VBA class directly -- define a COM interface in a separate typelib which your VBA class implements, and which your ATL class accepts as an input.
(This interface can be defined in your ATL project if its just a callback mechanism)
Now COM will do all the magic for you.

Note this is passing Excel VBA class instances -> ATL COM.
You will need to come up with more voodoo if you want to "instantiate" Excel VBA class from ATL COM since Excel VBA classes can only be made PublicNotCreatable meaning you need to expose an object factory from your VBA project.
(If you are going down that route, its best to extract your VBA classes to external COM projects)

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Sun Aug 05, 12 03:51 PM

User is offline

Thanks a lot mdsantos, your solution seems definitlively to be the good one. Expect that I'm quite a rookie concerning ATL/COM stuff, so that the following are not clear at all for me : - define a COM interface in a separate typelib which your VBA class implements, and which your ATL class accepts as an input.
- The interface can be defined in your ATL project if its just a callback mechanism
Do you have simple references/examples on this ? Thanks a lot

Raph

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

mdsantos
Junior Member

Posts: 18
Joined: Jan 2007

Mon Aug 06, 12 05:52 AM

User is offline

It's standard COM - so you should still be able to find references via Google.

Start off by defining an interface in your IDL.

interface IMyCallback : IDispatch{
HRESULT Execute([in] BSTR msg);
};

Your actual ATL Object would accept the interface as a parameter:

interface IMyATLObject : IDispatch{
HRESULT RegisterCallback([in] IMyCallback* cb);
};

Your C++ implementation can make use of the interface directly:

STDMETHODIMP CMyATLObject::RegisterCallback(IMyCallback* cb)
{
CComPtr<IMyCallback> p(cb);

// Accessing the interface is now straightforward
CComBSTR msg = "Msg from ATL";
p->Execute(msg);

return S_OK;
}

In Excel VBA-land, the VBA class that you want to pass should implement this interface.
(Obviously you need to reference the typelib *.tlb or *.dll for the interface definition in Project->References)

MyVBAClass.vba:

Implements IMyCallback

Private Sub IMyCallback_Execute(ByVal msg As String)
' This function will get called by ATL
MsgBox "Hello " & msg
End Sub

Now you can "pass an instance of VBA (for excel) class to an ATL method (in MVS2010 c++ ATL project with COM reference)"...

' Create an instance of your VBA class
Dim myVBA as new MyVBAClass

' Create an instance of the ATL COM object
Dim myATL as new MyATLProject.MyATLObject

' Can simply pass your Excel class to COM
myATL.RegisterCallback(myVBA)

' C++ will callback to your VBA class and execute the IMyCallback_Execute method.

Edited: Mon Aug 06, 12 at 05:53 AM by mdsantos

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Mon Aug 06, 12 09:15 AM

User is offline

Hi mdsantos,

Thank you very much for your very simple and enlightening example ! I just got one issue :

I've implemented the interface implementing

"Implements IMyCallback

Private Sub IMyCallback_Execute(ByVal msg As String)
' This function will get called by ATL
MsgBox "Hello " & msg
End Sub"

code in a VBA class module called "MyVBAClass" and

the sub code in a public sub and got an "Runtime error 438 Object doesn't support this property or method " on the line

myATL.RegisterCallback(myVBA)

At the beginning i have put

public:
STDMETHOD (RegisterCallback)(IMyCallback* cb) ;

in the MyATLObject.h file, and finally put

private:
STDMETHOD (RegisterCallback)(IMyCallback* cb) ;

instead, because of your

Private Sub IMyCallback_Execute(ByVal msg As String)

but that didn't solve the issue.

Would you have any idea ?

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Mon Aug 06, 12 09:38 AM

User is offline

Ok, sorry, I found the error, you and I forgot to put call :

Call myATL.RegisterCallback(myVBA)

Now it works perfectly, thanks a lot !

By the way, question on what you've called "more voodoo" :

"Note this is passing Excel VBA class instances -> ATL COM.
You will need to come up with more voodoo if you want to "instantiate" Excel VBA class from ATL COM since Excel VBA classes can only be made PublicNotCreatable meaning you need to expose an object factory from your VBA project.
(If you are going down that route, its best to extract your VBA classes to external COM projects)"

Do you have any references on this, of the following types :
- without extract VBA classes to external COM projects
- with extract VBA classes to external COM projects
?

An example would be perfect, of course, even if you've help me already quite a lot.

Thx

Raph

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

mdsantos
Junior Member

Posts: 18
Joined: Jan 2007

Mon Aug 06, 12 09:38 AM

User is offline

The RegisterCallback function shouldn't be confused with the Execute function.

Its a Runtime error - which probably means your VBA implementation is not correct.

Quote
I've implemented the interface implementing "Implements IMyCallback

In VBA, "Implements" is a keyword you place at the top of your class --- it should not be a comment.
When you add the line "Implements IMyCallback" in VBE -- it should have a dropdown with the Interface & Execute method listed. That will generate the correct callback handler.

mdsantos
Junior Member

Posts: 18
Joined: Jan 2007

Mon Aug 06, 12 09:52 AM

User is offline

(ah nice one on the missing call... )

Because of the restriction of Excel classes -- you can't actually instantiate them from outside of Excel.
What you can do is expose an ObjectFactory using the same method.
ie. VBA Class implements IFactory interface which exposes a CreateMyPrivateClassInstance method returning IMyCallback...

When I was referring to extracting I just meant pull out all the VBA code to a standalone COM component (or these days a .NET library)

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Mon Aug 06, 12 12:21 PM

User is offline

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

Edited: Tue Aug 07, 12 at 10:00 PM by rockinsquat

rockinsquat
Member

Posts: 63
Joined: Mar 2009

Mon Aug 06, 12 08:30 PM

User is offline

-------------------------
"The importance of money essentially flows from it being a link between the present and the future", wrote Keynes. So, where's the light cone, pal !?

Edited: Tue Aug 07, 12 at 10:00 PM by rockinsquat

View thread in raw text format

FORUMS > Programming and Software Forum

Forum Navigation:

© All material, including contents and design, copyright Wilmott Electronic Media Limited - FuseTalk 4.01 © 1999-2013 FuseTalk Inc. Terms & Conditions