Writing and then Calling a C++ DLL from Excel VBA

Question

I am using Visual Studio 2010 to write a 32 bit DLL in C++ which I intend to call from Excel VBA.

The C++ cpp file contains this:

    #include "windows.h"
    #include "stdafx.h"

    int _stdcall fnGetData (const LPCSTR someText) {
        MessageBoxA(0, someText, "wizzle wuzzle", MB_OK | MB_ICONINFORMATION);
        return 42;
    }

While the DEF file has this:

    LIBRARY getpdata
    EXPORTS
    GetData=fnGetData

The excel VBA is this:

    Option Explicit
    Private Declare Function GetData Lib "getpdata.dll" (ByRef someText As String) As Integer
    Public Sub PassSomeText()
    Dim ret As Integer, someText As String
    someText = "Hello from my Excel Subroutine."
    ret = GetData(someText)
    End Sub

The code compiles fine, and produces a DLL. But when I call the function in the DLL from Excel VBA, the text in the messagebox is garbled nonsense: Result of MessageBoxA cpp call: d³ô³ or other garbled nonsense

I am probably seeing the text representation of the pointer? How do I get the text value which Excel VBA passed in?

WizzleWuzzle WizzleWuzzle · Accepted Answer · 2015-04-07T18:04:49

Figured it out just as I was about to post my question. The answer is to change ByRef to ByVal in the VBA function declaration:

    Private Declare Function GetData Lib "getpdata.dll" (ByVal someText As String) As Integer

Writing and then Calling a C++ DLL from Excel VBA

1 Answers