VBA call same C++ dll return different results

Question

I have a test dll function that reads a string and display it:

int _stdcall test(LPSTR myString) {
MessageBoxA(NULL, (LPCSTR)myString, "test", MB_OK);
return 0;}

The excel VBA declaration is

Declare Function test Lib "test.dll" (ByVal text As String) As Long

There is a sub that calls the function. It reads an excel cell with value as "abcde" and is able to display a correct result. The sub code is:

sub testCode()
    test (cells(1,1).value)
end sub

However when call the dll function using excel formula =test(A1), the message box only display the first char "a" of the string.

I had spent entire weekend reading BSTR, etc, still not be able to solve this. What is going on here?

Answer 1

Declare your imported function as private:

Private Declare Function test_internal Lib "test.dll" Alias "test" (ByVal text As String) As Long

and create a wrapper function for Excel to use:

Public Function Test (ByVal text As String) As Long
  Test = test_internal(text)
End Functin

Because apparently, while VB(A) converts string arguments to ASCII using the current system codepage when calling to Declared APIs, the Excel engine does not.

On a side note, you also might want to remove parentheses.

Answer 2

Excel pass a BSTR to your C++ code. Each character is coded with 2 bytes, the second one being 0 for common characters. That explains why you see only the first one, as MessageBoxA expects char* null terminated strings. Use

MessageBoxW

Try with that code

int _stdcall test( const wchar_t * pString ) {
   MessageBoxW( NULL, pString, L"test", MB_OK ); // note the L prefix for literral
   return 0;
}

Alternatively, you could translate the BSTR pointer to a char* one, using some ATL macros, or raw Win32 APIs as WideCharToMultiByte (more tricky, mush simpler to use ATL)