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?

manuell · Answer 1 · 2014-03-10T11:06:33.013

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)

edited Mar 10 '14 at 11:06

answered Mar 10 '14 at 10:01

manuell

7,528
5
31
58

This is wrong. VB(A) converts strings to ASCII according to the current system code page when calling to `Declare`d APIs. The only case when it is not true is when the APIs come from a type library. However, Excel might pass something else, but exposing `Declare`d functions directly to Excel sheet engine is something I'd never even think about. – GSerg Mar 10 '14 at 10:51
@GSerg You may be right, I am a C++ guy, not a VBA one. But when a xxxA function displays only the first character, it's because the passed pointer is a wchar_t* one, not a char*. – manuell Mar 10 '14 at 11:03
The only thing that is *wrong* with your answer is `VB pass`. Replacing it with `Excel pass` makes the answer (likely) correct. – GSerg Mar 10 '14 at 11:05
Here is another wield things: now the function call can display the word in the right way. However the sub now displays grabled characteristics
I want to make it my weekend homework - btw is there any good reference book re excel VBA and dll? – Allan D Mar 12 '14 at 14:23
@AllanD I may help again, but I don't understand your new problem. Update your question with the code you use now, and explain more clearly what doesn't work. – manuell Mar 12 '14 at 19:32

score 2 · Answer 2 · edited May 23 '17 at 11:48

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.

VBA call same C++ dll return different results

2 Answers2

Linked