Calling DLL function from Excel VBA doesn't return value

[AcesHigh]

I decided to convert my VBA XLAM add-in to VB6 COM DLL and did so. Most functions work well except those that involve passing excel objects.

As a simple example consider the following.

[vba]
Public Function dllReturnRowCount(rDataRange as Excel.Range) as Long
dllReturnRowCount = rDataRange.Rows.Count
End Function[/vba]

The above is a simple function defined in VB6 COM DLL named say MyDLL in a class named say myDClass. The project is compiled into a DLL and a reference to it is added from within Excel VBA project. The below is the VBA wrapper to the above DLL function defined in Excel VBA.
[vba]
Function ReturnRowCount(rDataRange as Range) as Long
Dim myDllFunc as MyDLL.myDClass
Set myDllFunc = New MyDLL.myDClass
ReturnRowCount = myDllFunc.dllReturnRowCount (rDataRange)
End Function[/vba]

When I call the above function and debug it from within VB6 and VBA both, I can see that the range object is passed to the DLL and the DLL function is able to operate on it and return the row count successfully (from within VB6 IDE). But once the function call returns back to VBA, the return value is empty and ReturnRowCount above returns zero even though dllReturnRowCount succeeded. I can get other functions that do not involve passing Excel objects to return values of types Date, String, Boolean etc. successfully.

So, the question is how do I get the above to work the way it should ? What am I doing wrong ?

Thanks for any help.

[Kenneth Hobs]

How did you reference the Excel object?

Here is a thread that might help some. It may not help fully if you are getting some functions to work that don't reference the Excel object. http://www.vbaexpress.com/forum/showthread.php?t=45316

[Simon Lloyd]

Also recently posted here

[AcesHigh]

How did you reference the Excel object?

Err, you mean how did I refer to the Excel object in DLL ? Well, the first code block in my post is the DLL function that resides in the DLL and the second code block is the function declared in Excel VBA. Am I understanding you right here ?

[Kenneth Hobs]

To set the Excel object reference, see my link where I explained Imports or better yet, see Chip Pearson's example. http://www.cpearson.com/excel/creati...nctionlib.aspx

[AcesHigh]

Thanks, I just checked out your link. Apparently, you are using .NET library and I am using VB6. So, there are differences in the way you would refer and access the Excel objects.

What I have done is add a reference to the Microsoft Excel 12.0 Object Library from within VB6 References dialog as I have MS Office 2007 installed. Things are working well from the DLL end. The problem is when the DLL function call returns, it doesn't seem to pass the return value over to the VBA engine (when an Excel object is referenced in the DLL function).

So, I am missing something here. May be it has to do with the way you access/refer the Excel objects or may be the problem is on the VBA end. Perhaps VB6 and Excel VBA 2007 are incompatible in some way that is causing this anomaly.

So, I need to learn the proper way to address this in VB6 way.

As an aside, if I use .NET and port the DLL to another machine would I need to have the .NET libraries installed on the other machine (I think yes, you would) and would it have to be the same version as the one used in the development of DLL ?

[Kenneth Hobs]

Yes, the vb.net framework files need to be the same version or newer. This is the same principle as the old vbrunxxx.dll's only there are many more files. vb.net is a good way to go. Many computers already have basic framework files installed already.

[SamT]

how about trying
[VBA]Function ReturnRowCount(rDataRange As Range) As Long
Dim myDllFunc As Object
Set myDllFunc = CreateObject( MyDLL.myDClass)
Dim Result As Variant
Result = myDllFunc.dllReturnRowCount (rDataRange)
ReturnRowCount = Result 'or CLng(Result)
End Function [/vba]

[AcesHigh]

how about trying
VBA:

Function ReturnRowCount(rDataRange As Range) As Long Dim myDllFunc As Object Set myDllFunc = CreateObject( MyDLL.myDClass) Dim Result As Variant Result = myDllFunc.dllReturnRowCount (rDataRange) ReturnRowCount = Result 'or CLng(Result) End Function

Thanks. I just tried that, didn't work. VBA is throwing an error that says "Compile Error: method or data member not found" on myDClass on the below line:
[VBA]Set myDllFunc = CreateObject ( myDLL.myDClass)[/VBA]

[Aflatoon]

You need quotes
[vba]Set myDllFunc = CreateObject("myDLL.myDClass")[/vba]