Variant array of String contains alternate spaces

Question

I created a C++ DLL, when called from VBA returns SAFEARRAY*.

My VBA Function :

Declare Function GetData_V Lib "xyz.dll" (ByVal path As String, ByVal id As String, ByRef inputArr() As String, ByRef output() As String) As Variant()

My C++ implementation :

SafeArrayLock(*outputArray); 
    for (LONG i = 0; i < countElements; i++)
    {
        CComBSTR bstr = CComBSTR(outputCustom[i]);
        SafeArrayPutElement(*outputArray, &i, bstr);
    }
    SafeArrayUnlock(*outputArray); 

    delete [] outputCustom;

    return *outputArray;

Where outputCustom is defined as

outputCustom = new char*[nCount+1];

In VBA when I check the output it shows string as :

1 0 . 9 4 4 9 d 0 
2 . 8 3 4 6 d 0 
0 . 0 1 1 8 d 0 
0 . 6 2 9 9 d 0 
0 . 6 2 9 9 d 0 
0 . 6 2 9 9 d 0 
2 5 . 0 d 0 
0 . 0 7 6 4 d 0 
1 0 . 9 4 4 9 d 0 
2 . 8 3 4 6 d 0 
0 . 0 1 1 8 d 0 

I debugged the code and checked the values of *outputArray in C++ but everything is fine there, but when I checked my array in VBA it got extra spaces in between each character of each strings.

Answer 1

VBA, like VB6, expects Unicode (wide-character) strings.

How to: Access DLLs in Excel -- Variant and String Arguments:

Excel works internally with wide-character Unicode strings. When a VBA user-defined function is declared as taking a String argument, Excel converts the supplied string to a byte-string in a locale-specific way. If you want your function to be passed a Unicode string, your VBA user-defined function should accept a Variant instead of a String argument. Your DLL function can then accept that Variant BSTR wide-character string from VBA.

The spaces you see are the zero byte high bytes of the 16 bit chars. You need to use wchar_t and/or convert to widechar before passing the strings to CComBSTRs constructor. You can use MultiByteToWideChar, mbstowcs or ATL and MFC String Conversion Macros to do it.

A good tip to see what VBA expect is to pass such object from VB to your DLL and inspect it there.

Eric’s Complete Guide To BSTR Semantics is also an interesting read.