Calling a DLL function

[Zenkai]

I am trying to write a DLL and have its functions accessible to Excel so that the code runs faster (right now, I have one module taking about 2 minutes to calculate). I wrote the following code in Visual Basic 6.0 Pro:

Public Function TimesFive(x As Double) As Double

TimesFive = x * 5

End Function

And compiled to a .dll called X5.dll
I wrote this bit of code into Excel 2000 to try to call it:

Declare Function TimesFive Lib "C:\X5.dll" _
(x As Double) As Double

Function CallX5(a As Double) As Double

CallX5 = TimesFive(a)

End Function

I also made a reference to the .DLL in the vba module. In the spreadsheet, I typed "1" into A1, and "=CallX5(A1)" into A2. But I get the #VALUE! error. Anyone have any ideas? I have tried inserting ByVal into all argument declarations, accessing the .DLL function directly from the spreadsheet, etc, but to no avail.

 

[RobBroekhuis]

When you set the reference, do you see the function in the object browser?
Rob

 

[Zenkai]

Yes, it's there.

 

[Zenkai]

But if I delete the function declaration (Declare Function TimesFive...), the object disappears from the library.

 

[RobBroekhuis]

The function should show up as a method/function when you select the dll in the object browser, not just because you declared it in your VBA code. I know nothing at all about compiled VB6, but is it possible that the proper compile switch/directive wasn't set to make your function accessible to other applications?
Rob

 

[Zenkai]

Rob - I think that may have been a part of it, which I have since fixed. I can now see the function in the object browser by only referencing the dll, and not declaring it. However, the function still won't work, saying it "has not been defined." So then I use the declare statement, and lo and behold, get the #VALUE! once more.

-Nick

 

[RobBroekhuis]

Rather than immediately using it as a custom worksheet function, have you played around with it in VBA code, or the immediate window, to see where the error may be occurring?
Rob

 

[Zenkai]

Yes, the error message is always "Sub or Function not defined," at least witin Excel. I don't really know how to test from within VB6, the debugger is a little different. But the code I'm using is not at all complicated, it's about the most straightforward thing I could think of.

This is so frustrating

 

[Zenkai]

Well, I found the answer for anyone who struggles with this in the future:

If the dll is ActiveX, you simply set a reference, and treat the DLL as an object. For example, if you had a function MyFunction in a class Class1 in a dll MyFuncs,

(after referencing MyFuncs.dll)

Sub Test()
Dim myObj As Class1
Set myObj = New MyFuncs.Class1
Dim a As Variant
a = myObj.MyFunction()
End Sub