Excel VBA script crashes when calling a DDL function written in C, which returns a string BSTR

Question

I'm using Code::Blocks to write a DLL in C which I intend to use in a Winbatch script but for the moment I'm testing it using Excel VBA. The moment the VBA script runs the DLL getVersion() function Excel crashes. Searching on the internet for several days: I did not find a proper solution.

The C code is like this

#define MQTTPUB_VERSION "V3.1.1Test"
DLL_EXPORT BSTR __stdcall WINAPI getVersion(void)
{
  return MQTTPUB_VERSION ;
}

The VBA code is like this

Public Declare Function mqttPubMsg Lib "mqttPubMsg.dll" _
 (ByVal MQTT_ADDRESS As String, ByVal MQTT_CLIENTID As String, ByVal MQTT_TOPIC As String, ByVal MQTT_PAYLOAD As String) As Long
Public Declare Function getVersion Lib "mqttPubMsg.dll" () As String
Sub Test_DDL_mqttPubMsg()
'
' to test mqttPubMsg.DLL used in Visual Basic (VBA)
'
Dim DLLVersion As String * 35
Dim WorkDir As String
DLLVersion = Space(35)
WorkDir = ThisWorkbook.Path
ChDir WorkDir

If Dir(WorkDir & "\mqttPubMsg.dll", vbDirectory) = vbNullString Then
   MsgBox "DLL not found"
Else
  On Error GoTo DLLError
  DLLVersion = getVersion() 'Excel crashes on executing this statement
End If
MsgBox ("Version DDL: " & DLLVersion)
Exit Sub
DLLError:
  MsgBox ("DDL error")
End Sub

A C program calling the DLL and the getVersion() function works OK.

What could be the cause of this runtime error and how to solve it.

Thanks in advance.

score 1 · Accepted Answer · answered Mar 16 '16 at 16:20

I haven't done Windows programming recently and don't have a readily available development environment to experiment in, but here are some thoughts.

The most likely problem here is that getVersion() returns BSTR, which is also (probably) expected by the calling VBA code when the return type is declared as String. Now, BSTR is supposed to be preceded in memory by a 4-byte length prefix, see https://msdn.microsoft.com/en-us/library/windows/desktop/ms221069%28v=vs.85%29.aspx. getVersion() just returns a pointer to "V", the 1st character of the string, and when VBA tries to interpret the preceding 4 bytes, before the "V", as length, it's a disaster...

Here are some possible solutions that I see:

Construct a BSTR using the SysAllocString() function in getVersion() as recommended in the above referenced article.
Pass the DLLVersion string to getVersion() by reference and see if you can somehow populate it inside getVersion(). Remember that you are dealing with Unicode here.

A few other things to play with out of curiosity:

Verify that a DLL function can actually be called from VBA; let getVersion() return void and see if just calling that function crashes Excel. If it does, then the problem is deeper.
Make getVersion() return an integer and see if you can get it in your VBA code.
Make getVersion() take an integer as a parameter, pass it an integer from VBA, and see if the C code can get the correct value.
Have a large enough global char array in your C code, put the (Unicode!) version string starting at offset 4 in the array, and populate the 1st 4 bytes with an (little-endian, I think) integer indicating the length of the Unicode string excluding the NULL terminator. Make getVersion() return the pointer to buf + 4, where buf is the global char array.

Sorry my memory is not fresh on this and I cannot provide a more specific advice, but hopefully this helps.

Many thanks for your advice, I will try it while also waiting for some other tips. — JanWillemT, Mar 17 '16 at 08:39

score 0 · Answer 2 · edited May 23 '17 at 12:01

The following solution is for a 64-bit system with Office 2013 installed.

C code:

To get the C code working for me, I had to change the declaration slightly, and use a .def file to make sure the DLL exported the right function name.

#define MQTTPUB_VERSION L"V3.1.1Test"  // notice the L - it casts better to BSTR
extern "C" BSTR __stdcall WINAPI getVersion(void)
{
  return MQTTPUB_VERSION;
}

VBA code:

On the VBA side, my approach was to

Get the address of the target string by declaring the return type of getVersion as LongLong instead of string.
Allocate an empty string with the same amount of characters

Copy memory from the source address to the destination string address.

    Private Declare PtrSafe Sub CopyMemUC Lib "kernel32" Alias "RtlMoveMemory" _
        (ByVal Destination As LongLong, ByVal Source As LongLong, ByVal Length As Long)
    Private Declare PtrSafe Function wcslen Lib "msvcrt" _
        (ByVal Source As LongLong) As Long
    Private Declare PtrSafe Function _
        getVersionAddress Lib "D:\codeblocks\xltest\bin\Debug\mqttPubMsg.dll" _
        Alias "getVersion" _
        () As LongLong

    Function getVersion()
        Dim sAddress As LongLong, sTemp As String, sLen
        sAddress = getVersionAddress
        sLen = wcslen(sAddress)  ' Length (2 bytes per character)
        sTemp = String(sLen, " ")  ' Allocates memory
        CopyMemUC ByVal StrPtr(sTemp), getVersionAddress, sLen * 2
        getVersion = sTemp
    End Function

Testing if everything works:

Evaluating ?getVersion in the immediate window returns the string without crashing:

On 32 bit systems, or VBA version < 7:

Remember to remove the PtrSafe keywords and change all LongLong occurrences to Long. Alternatively, use compiler checking - an example can be found here: how to make VBA code compatible for office 2010 - 64 bit version and older office versions

Excel VBA script crashes when calling a DDL function written in C, which returns a string BSTR

2 Answers2