How to pass array to VBA from a C++ DLL?

Question

I have a C++ DLL; I am trying to call an exported function in VBA that will return an array. To avoid having to deal with SAFEARRAY as a return type, I've chosen to pass an array into the function by reference and have the function modify this array's values. But the VBA implementation doesn't return the modified values, even though I'm using ByRef to pass in the array.

Header:

namespace myProject
{
    class FileOperator
    {
    public:
        static __declspec(dllexport) void __stdcall testArray(long* docArray);
    };
}

Implementation:

#include "stdafx.h"
#include "myProject.h"

using namespace std;

namespace myProject
{
    void __stdcall FileOperator::testArray(long* docArray)
    {
        docArray[0]=303;
        docArray[1]=909;
    }
}

Console app test:

#include "stdafx.h"
#include <iostream>
#include "../myProject/myProject.h"

using namespace std;

int main()
{
    long docArray[2]={0};
    myProject::FileOperator::testArray(docArray);
    cout << docArray[0] << "\n" << docArray[1];
}

VBA test:

Private Declare Sub testArray Lib "C:\pathToDLL\myProject.dll" _
                            Alias "?testArray@FileOperator@myProject@@SGXPAJ@Z" _
                            (ByRef docArray() As Long)


Public Sub test()

Dim docArray(1) As Long

docArray(0) = 0
docArray(1) = 0

testArray docArray
For Each x In docArray
    Debug.Print x
Next

End Sub

The C++ console app outputs:

303
909

The VBA app outputs:

0
0

Why aren't the array changes being saved once the testArray() function exits? Shouldn't the ByRef keyword make this possible?

MSDN has an article on pretty much this exact topic: http://support.microsoft.com/kb/207931 — Zac Howland, Oct 28 '13 at 21:04
@ZacHowland: Yes, I read the MSDN article, but I was hoping to build the function without having to get into using SAFEARRAY. [This Tek-Tips article](http://www.tek-tips.com/viewthread.cfm?qid=1455970) suggested that this is possible. — sigil, Oct 28 '13 at 21:15

score 3 · Accepted Answer · answered Oct 28 '13 at 20:59

ByRef docArray() makes the expected sense when calling inside VB(A) - it allows you to replace the array stored in the variable with another array (as well as change the elements of the passed array without replacing the whole array).

For declared functions, use ByRef docArray As Long, and then pass docArray(LBound(docArray)) as the argument (the pointer to the first element).

How to pass array to VBA from a C++ DLL?

1 Answers1

Linked