Troubleshooting DLL function call from Excel Vba

Question

I'm trying to call a function from a DLL from VBA in Excel.

My Excel VBA macro looks like this:

Declare PtrSafe Function TestFunction1 Lib "mylib.dll" (ByVal k As Double) As Double

Public Function TestDll(k As Double) As Double
    Debug.Print ("Start")

    Dim r As Double
    r = TestFunction1(k)

    Debug.Print ("Got result of " + r)
    Debug.Print ("Done")

    TestDll = r
End Function

Now when I call it from an Excel cell with something like "=TestDll(3.0)", it doesn't work. I see the "Start" string in the immediate window, but nothing else. It's like an error is happening exactly when "TestFunction1" is being called. Excel displays "#VALUE!" in the cell.

I can also set a breakpoint in the debugger, but when I get to the TestFunction1 call, it just ends. There is no sort of error message I can find.

My question is, how do I debug this? I'm not getting any error message. It simply doesn't work. How can I figure out what is going wrong?

You are never assigning a value to r. And you are never declaring ret as Double. (Not sure whether those are causing your issues, but I suggest you fix them anyway.) — YowE3K
– YowE3K, Commented Sep 5, 2016 at 0:42
You can try adding the .dll file to the VBA project references and F2 to search for the method in the Object Explorer. Another thing that you can try is to use As Object instead of As Double. I don't think we can help you much without any information about mylib.dll and TestFunction1 — Slai
– Slai, Commented Sep 5, 2016 at 0:45
@YowE3K Sorry, I confused r with ret. They should have been the same. Fixed it. — carrie88
– carrie88, Commented Sep 5, 2016 at 0:51
This is a well-phrased question, but without the DLL and TestFunction1, it is not really something that anyone is likely able to assist with, beyond very high-level advice. Otherwise, the question's scope is limited to a very unique problem (i.e., limited to your specific DLL & function) and may not be very useful for the rest of SO community. — David Zemens
– David Zemens, Commented Sep 5, 2016 at 1:35

teddy2 · Accepted Answer · 2016-09-05 01:41:07Z

4

The variable which you are using in debug statement,has an error and hence the UDF fails. Rest is fine. Actually you need to convert r to string or use & for concatenation in your debug statement.

Edit: to include error handler.

Public Function TestDll(k As Double) As Double
    Debug.Print ("Start")

    Dim r       As Double

    '/ Add a error handler
    On Error GoTo errHandler
    '/ Assuming that your testfunction will return 10*parameter
    r = k * 10


    '/ The variable which you are returning,has a error and hence the UDF fails.
    '/ Rest is fine. Here you will get type mismatch error.
    Debug.Print ("Got result of " + r)

    '/ Actually you need to convert it to string or use `&` for concatenation
    Debug.Print ("Got result of " + CStr(r))

    '/ or
    Debug.Print ("Got result of " & r)


    Debug.Print ("Done")

    TestDll = r

errHandler:
    If Err.Number <> 0 Then
        '/ Error trapped and you get actual error desc and number.
        MsgBox Err.Description, vbCritical, Err.Number
    End If

End Function

edited Sep 5, 2016 at 1:41

answered Sep 5, 2016 at 1:27

teddy2

3904 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

David Zemens Over a year ago

THis is good advice regarding the & vs + for concatenation, the latter will yield a Type Mismatch error in this case. However, the root of the problem seems to be with the call to TestFunction1: when I get to the TestFunction1 call, it just ends.

carrie88 Over a year ago

Thanks for your answer. When I run in the debugger, execution doesn't go past " r = TestFunction1(k)".

teddy2 Over a year ago

see the edit to my answer. It will help you to trap the actual error. Post the error message and error number. May be some one could be able to help.

carrie88 Over a year ago

@teddy2 The "On Error" part is exactly what I needed! I have an actual error message to go on now, and hopefully I can get it from here. Thanks x1000! (I'd upvote you, but apparently I can't?)

teddy2 Over a year ago

great. dont worry about upvotes. As long as it works for you I am happy. :)

Collectives™ on Stack Overflow

Troubleshooting DLL function call from Excel Vba

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related