Hi all,

I would like to return an array of String variables from a C++ DLL to a VBA application. My understanding is that:

  1. To do so, the C++ function must return a SAFEARRAY of BSTR.
  2. Although Excel uses BSTR containing wide characters (wchar_t), VBA itself uses BSTR with byte characters (char).

Here is my attempt. I'm new at this, so any advice would be most welcome.

SAFEARRAY* __stdcall test( BSTR iPath, BSTR uPath, BSTR fPath)
     vector<string> cusips;
     string         tmpStr;
     char           deal[MAX_SIZE];

     // Read in the CUSIPs
     ifstream ifs( reinterpret_cast<LPSTR>(fPath) );
     while( getline(ifs, tmpStr) )


     . . .

     // Create the SAFEARRAY bounds.
     SAFEARRAYBOUND bounds[2];            // 2-D array
     bounds[0].lLbound   = 0;
     bounds[0].cElements = 2;             // cusip and deal
     bounds[1].lLbound   = 0;
     bounds[1].cElements = cusips.size(); // Number of cusips read in.

     // Create the SAFEARRAY
     SAFEARRAY *retArray;
     retArray = SafeArrayCreate( VT_BSTR, 2, bounds );
     assert( retArray != NULL );
     long indices[2];

     // Iterate over all the CUSIPs
     for( unsigned long i = 0; i < cusips.size(); ++i )
          const char *cusip = cusips[i].c_str();
          deal = someFunction( foo, bar );

          // Populate the SAFEARRAY elements
          indices[0] = 0;
          indices[1] = i;
          hResult = SafeArrayPutElement( retArray, indices, SysAllocStringByteLen(cusip, strlen(cusip)) );
          assert( hResult == S_OK );
          hResult = SafeArrayPutElement( retArray, indices, SysAllocStringByteLen(deal, strlen(deal)) );
          assert( hResult == S_OK );

     // Debugging / testing
     BSTR *tmpBstr;
     indices[0] = 0;
     indices[1] = 0;

     hResult = SafeArrayGetElement(retArray, indices, &tmpBstr);
     assert( hResult == S_OK );
     char *c = (LPSTR) tmpBstr;

     return retArray;

In the debugging / testing, I made sure that variable c held expected data, and it did. c pointed to the correct first CUSIP number, so everything looks good on the C++ side (to my inexpert eyes).

Now on the to the VBA side. Here's what the VBA code looks like:

Public Declare Function test Lib "C:\Projects\myDLL.dll" _
(ByVal iPath As String, ByVal uPath As String, ByVal fPath As String) As String()

Public Sub tests()
     Dim s() As String

     s = test4(wks.iPath, wks.uPath, wks.fPath)
     Debug.Print s(0,0)
End Sub

This code almost works. When the VBA code runs, s is indeed a 2-D String array with the correct bounds. Here is what s looks like:

s     String(0 to 1, 0 to 42)
s[0]  String[0 to 42]
s[1]  String[0 to 42]

The problem is that the Strings contain garbage. When I place a watch on s, here is what I see:

s(0,0)   = "????"
s(0,1)   = "????"
     . . .
s(0, 42) = "????"
s(1,0)   = "????"
s(1,1)   = "????"
     . . .
s(1, 42) = "????"

I'm so close.... yet so far. Can someone please help me out? Why do the String variables contain garbage when I've already made sure the underlying BSTR holds correct data?

Is this a problem with the width of the characters?

Thank you!

Edited by caffeine: n/a

6 Years
Discussion Span
Last Post by Michael_33

Figured it out.

The answer is character width of the string: OLECHARs versus byte characters.



Is the problem? I have a similar problem. Could you please post your solution?


This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.