Returning COM objects to VBA from Visual Foxpro

I am getting the following message when trying to return a new object to VBA from my Visual Foxpro COM server. "Run-time error '-2147417851 (80010105)':

Method 'ReturnObject' of object 'Itestclass' failed"

If I remove the "Dim ... As" line the error goes away but then I lose intellisense for the COM object.

This is the VBA code:

Sub Test()

'' Removing the following line gets rid of the error but loses intellisense for the COM object
Dim objTest As testcom.TestClass

Set objTest = CreateObject("TestCOM.TestClass")
Set objNew = objTest.ReturnObject   '' This is the line that causes the error

End Sub

Open in new window


I have created a link to the TestCOM type library in Tools > References

Here is the Visual Foxpro (VFP) code: The COM server is being built as an out of process EXE. If I build it as an inprocess .DLL then the VBA code causes Excel to crash.

DEFINE CLASS ObjectToReturn AS SESSION OLEPUBLIC

ENDDEFINE

DEFINE CLASS TestClass AS SESSION OLEPUBLIC

FUNCTION ReturnObject

    RETURN CREATEOBJECT("ObjectToReturn")

ENDFUNC

ENDDEFINE

Open in new window


I have tried changing the RETURN CREATEOBJECT("ObjectToReturn") to RETURN CREATEOBJECT("CUSTOM") but the problem persists.

Please advise how I can get rid of this error without losing the intellisense for the COM object in VBA. Thanks

I have tried creating a public Collection in TestClass and using that and I have tried using SYS(3097) to keep a reference to the object in VFP but the problem perists.
LVL 3
CaltorAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Olaf DoschkeSoftware DeveloperCommented:
I did try your example with VFP9 and a VBA macro in Word 2007.

I think this has to do with early vs late binding. If you specify testcom.testclass you use early binding, but VFP doesn't support this and so while intellisense works usage of testclass crashes. There's nothing you can change to make both intellisense and usage work.

All you can do in VB(A) is being unspecific about the object:
Dim objTest As Object

Bye, Olaf.
0
CaltorAuthor Commented:
Hi Olaf

I take it you encountered the same problem?

However "Visual FoxPro COM servers support both early (vtable) binding and the existing late binding (IDispatch) interface (together known as dual-interface support). " http://msdn.microsoft.com/en-us/library/aa979131%28v=vs.71%29.aspx
Thanks
0
Olaf DoschkeSoftware DeveloperCommented:
OK, and it continues: "While Visual FoxPro servers support both interfaces, the one used is determined by the client." I think there is something going on in VB or the VB IDE that handles the instances wrong. DIM variable As TestCOM.TestClass suggests defining early binding. Then useing CreateObject() on the VB side means late binding.

So your code is mixing the two modes. I found the differing strategies described here: http://www.online-excel.de/excel/singsel_vba.php?f=85

Sub EarlyBinding() vs Sub Latebinding().

In consequence you should use new instead of CreateObject():

'early binding
Dim objTest As testcom.TestClass

Set objTest = new TestCOM.TestClass

Open in new window


- OR -

'late binding
Dim objTest As Object

Set objTest = CreateObjecT("TestCOM.TestClass")

Open in new window


Bye, Olaf.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

CaltorAuthor Commented:
I've changed the code to use the New keyword for early binding in VBA as you suggest but I still get the same error though.
0
Olaf DoschkeSoftware DeveloperCommented:
I'm out of ideas of what you could do, despite of creating objects diretly, eg:# instead of calling ReturnObject() do
Set objNew = CreateObject("TestCom.ObjectToReturn") or Set objNew = New TestCom.ObjectToReturn, instead of letting Foxpro create the objects.

Bye, Olaf.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
CaltorAuthor Commented:
I have gone with that option to create the objects from VBA and pass them a AddLine() method of the VFP COM server. The AddLine method then adds them to an array property of the main class. I wanted to use a collection instead but this doesn't seem to work.
0
Olaf DoschkeSoftware DeveloperCommented:
The collection object of foxpro surely does not translate / marshall to COM clients as a collection or array. Even arrays are problematic, as languages differ in 0- or 1-based arrays.

But foxpro can indeed return array via
LOCAL ARRAY laArr[2]
laArr[1]="one"
laArr[2]="two"
Return @laArr

So you might try that, but if you are already having troubles with single object references, it makes no sense if that would work.

Bye, Olaf.
0
CaltorAuthor Commented:
Fortunately it doesn't need to return an array or collection. The basic process is:
VBA client creates main PurchaseOrder (TestClass) object
VBA client creates child PurchaseOrderLine (ObjectToReturn) object
VBA client sets properties of PurchaseOrderLine
VBA client calls PurchaseOrder.AddLine method and passes PurchaseOrderLine
PurchaseOrder.AddLine method adds received object to array property
Later on the PurchaseOrder object is passed to a controlling object to update the relevant tables etc
Thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FoxPro

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.