Return String array from C++ DLL to VBA(Excel)

Question

I created a C++ DLL which take an empty array as VARIANT. I will run some sql query inside DLL and store output in empty array. When I tried this, excel crashes.

My VBA call :

Dim outArray(17) As Variant
bo = GetData_V(dbFilePath, id, inArray, outArray, counter)

and function is defined as

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

C++ Implementation :

        CComSafeArray<VARIANT> out_sa(nCount);
        HRESULT hr;
        for (LONG i = lowerBound; i <= upperBound; i++)
        {
            CComVariant variant = CComVariant(outputCustom[i]);
            hr = out_sa.SetAt(i,variant);
            if (FAILED(hr))
            {
                return false;
            }
        }
        CComVariant(out_sa).Detach(outputArray);

Where outputCustom is defined as

outputCustom = new char*[nCount+1];

and has string values.

When I try to run it, Excel crashes. I don't know how to return outputArray back to VBA.

your function definition does not show a return variable type — jsotola
– jsotola, Commented Nov 27, 2017 at 5:54
have you successfully written a dll that accepts a single integer and returns a single integer? — jsotola
– jsotola, Commented Nov 27, 2017 at 5:56
@jsotola when I tried to add a return type, it shows 'expected end of statement.' . I tried putting a fullstop but still giving error. Also I haven't tried returning a integer. — techie
– techie, Commented Nov 27, 2017 at 6:01
You have declared your function to return a Long (i.e. the & on the end of GetData_V&) - are you doing so? Also, your declaration is defining the parameters to be ByVal path As String, ByVal path As String, ByRef inputArr() As String, ByRef output As Variant, ByRef id As Integer (with path repeated?) but your call (i.e. dbFilePath, id, inArray, outArray, counter) doesn't seem to match that. — YowE3K
– YowE3K, Commented Nov 27, 2017 at 6:29
you have to be able to create and use a simplest possible dll before doing anything more complex. that way you have very minimal debugging in order to make it work. ... once it works, add more calling arguments... then finally add the return parameter — jsotola
– jsotola, Commented Nov 27, 2017 at 6:37

techie · Accepted Answer · 2017-11-27 12:56:49Z

1

So this is how I return String array from C++ DLL to VBA: I changed my VBA function declaration to this :

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()

C++ implementation : Changed return type of C++ function to SAFEARRAY* and modified the code as follow:

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

    delete [] outputCustom;

    return *outputArray;

answered Nov 27, 2017 at 12:56

techie

19113 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Return String array from C++ DLL to VBA(Excel)

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related