Click here to Skip to main content
15,944,837 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have created a DLL callback through a delegate function.
It works fine when the client/user code is C#, or VB.NET.
I can't get it to compile in Excel VBA. I have this problem both in Excel 2003 and Excel 2007.
The DLL references setting is ok, because when I take the reference out the compiler errors out on RemoteProcess.
What am I missing?
The snippets below are the dll code, the working code, and the failing vba code.

The remoteprocess class is compiled as dll:
Public Class RemoteProcess
    Public Delegate Sub RemoteProcessResultMessage(ByVal sMsg As String)
    Public Sub runIt(ByVal callback As RemoteProcessResultMessage)
        callback("Hello World!")
    End Sub
End Class

The VB Client code, which works well:
Imports RemoteProcess
Imports System.Runtime.InteropServices
Public Class Form1
    Private Sub btnStart_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnStart.Click
        Dim rpResponse As RemoteProcess.RemoteProcess.RemoteProcessResultMessage
        rpResponse = New RemoteProcess.RemoteProcess.RemoteProcessResultMessage(AddressOf ServerResponse)
        Dim rp As RemoteProcess.RemoteProcess
        rp = New RemoteProcess.RemoteProcess
    End Sub
    Private Sub ServerResponse(ByVal sMessage As String)
        MsgBox("Received response: " & sMessage)
    End Sub
End Class

The Excel VBA Client code, which does not compile.
Microsoft Visual Basic
Compile error:
Expected: end of statement
OK Help
The highlight is on the open parenthesis in the constructor.
It looks like the AddressOf parameter is not expected, but we know it's needed
Public Sub RunRemote()
    Dim rpResponse As RemoteProcess.RemoteProcess.RemoteProcessResultMessage
    set rpResponse = New RemoteProcess.RemoteProcess.RemoteProcessResultMessage(AddressOf ServerResponse)
    Dim rp As RemoteProcess
    rp = New RemoteProcess.RemoteProcess
    rp.RunIt (rpResponse)
End Sub
Private Sub ServerResponse(ByVal sMessage As String)
    MsgBox ("Received response: " & sMessage)
End Sub
Updated 3-Jun-10 4:44am
Maciej Los 8-Apr-11 12:54pm    
Did you read my post? Have you compile your dll in __stdcall calling convention?
"When using AddressOf to pass a procedure pointer among procedures within Basic, the parameter of the called procedure must be typed As Long"

1 solution

from VBA help:
AddressOf Operator

A unary operator that causes the address of the procedure it precedes to be passed to an API procedure that expects a function pointer at that position in the argument list.

AddressOf procedurename

The required procedurename specifies the procedure whose address is to be passed. It must represent a procedure in a standard module module in the project in which the call is made.

When a procedure name appears in an argument list, usually the procedure is evaluated, and the address of the procedure’s return value is passed. AddressOf permits the address of the procedure to be passed to a Windows API function in a dynamic-link library (DLL), rather passing the procedure's return value. The API function can then use the address to call the Basic procedure, a process known as a callback. The AddressOf operator appears only in the call to the API procedure.

Although you can use AddressOf to pass procedure pointers among Basic procedures, you can't call a function through such a pointer from within Basic. This means, for example, that a class written in Basic can't make a callback to its controller using such a pointer. When using AddressOf to pass a procedure pointer among procedures within Basic, the parameter of the called procedure must be typed As Long.

Warning Using AddressOf may cause unpredictable results if you don't completely understand the concept of function callbacks. You must understand how the Basic portion of the callback works, and also the code of the DLL into which you are passing your function address. Debugging such interactions is difficult since the program runs in the same process as the development environment. In some cases, systematic debugging may not be possible.

Note You can create your own call-back function prototypes in DLLs compiled with Microsoft Visual C++ (or similar tools). To work with AddressOf, your prototype must use the __stdcall calling convention. The default calling convention (__cdecl) will not work with AddressOf.

Since the caller of a callback is not within your program, it is important that an error in the callback procedure not be propagated back to the caller. You can accomplish this by placing the On Error Resume Next statement at the beginning of the callback procedure.
Share this answer

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900