Accessing a nested structure in C++ from VBA

Question

Problem

I have a library written in C++ and compiled as a DLL using Visual Studio 2010. The DLL has multiple exported functions. The exported functions are accessed from Excel using Declare Function.

I am trying to implement a new feature in the program which requires nested structures in the C++ portion which are then accessed from VBA. The C++ code looks like this.

First Structure

struct Parameter {
    double value;   
    char* label;     
    char* description;
    char* units;  
};

Second Structure

This structure is used to build another structure as follows:

struct Output {
    Parameter field_1;
    Parameter field_2;
    Parameter field_3;
};

There are couple of ways that I am thinking about accessing the structure from VBA. One of them is from a void function such as this.

void Function1(Output* output_function1);

The other is a function that returns the Output structure, such as this.

Output Function2();

The internals of the two functions above do not matter at this point. I have verified that both implementations work as intended in the C++ code.

Issue

I am unable to access these two structures from VBA using either Function1 or Function2.

I declared two custom types in VBA.

Type Parameter
    Value as Double
    Label as String
    Description as String
    Units as String
End Type

Type Output
    Field1 as Parameter
    Field2 as Parameter
    Field3 as Parameter
End Type

For Function1 I declared the exported function as follows.

Declare Sub Function1 Lib "C:\Path\to\library.dll" (ByRef OutputStruct as Output)

and for Function2, the following.

Declare Sub Function2 Lib "C:\Path\to\library.dll" () as Output

Function2 crashes Excel and Function1 gives me the error vba byref argument type mismatch.

What am I doing wrong here? What is the correct approach?

Answer 1

Function1 will work fine if you enclose definitions of your structures in #pragma pack(4) ... #pragma pack() directives.

https://msdn.microsoft.com/en-US/en-en/library/office/bb687915.aspx

C:

#pragma pack(4)
struct Parameter{
    double value;   
    char* label;     
    char* description;
    char* units;  
};

struct Output{
    struct Parameter field_1;
    struct Parameter field_2;
    struct Parameter field_3;
};
#pragma pack()


static char Buffer [4096];

static void Append_Field_Values (struct Parameter* field)
{
    static char b [1024];
    sprintf (b, "%f %s %s %s\n", field -> value, field ->label, field -> description, field -> units);
    strcat (Buffer, b);
}

void _stdcall Function1(struct Output* output_function1)
{
    Buffer [0] = 0;
    Append_Field_Values (&output_function1 -> field_1);
    Append_Field_Values (&output_function1 -> field_2);
    Append_Field_Values (&output_function1 -> field_3);
    MessageBox (0, Buffer, "FUNCTION1", 0);
}

VB:

Type Parameter
    Value As Double
    Label As String
    Description As String
    Units As String
End Type

Type Output
    Field1 As Parameter
    Field2 As Parameter
    Field3 As Parameter
End Type

Declare Sub Function1 Lib "E:\Serge\StackOverflow\NestedStructures\Debug\NestedStructures.dll" (ByRef OutputStruct As Output)

Dim Out1 As Output

Sub Test1()
    Out1.Field1.Value = 3.14
    Out1.Field1.Label = "ARINC 429"
    Out1.Field2.Units = "Miles and Inches"
    Out1.Field3.Description = "Read MSDN"

    Call Function1(Out1)
End Sub

Answer 2

You can't pass VBA Types as arguments. (I know. I know. VBA....)

What you'll need to do is pass in/out the individual properties. Something like this (I don't know C++ so bear with me.)

void Function1(Output* Field1, Output* Field2, Output* Field3)

And then on the VBA side

Declare PtrSafe Sub Function1 Lib "C:\Path\to\library.dll" (ByRef Field1 As LongPtr, ByRef Field2 As LongPtr, ByRef Field3 As LongPtr)

Or whatever data type is appropriate. This MSDN doc on the Declare statement might help as well.

Answer 3

Don't nest Types when you're trying to call into a DLL. Instead make a composite Type that contains all the members:

Type Output
    Value1 as Double
    Label1 as String
    Description1 as String
    Units1 as String
    Value2 as Double
    Label2 as String
    Description2 as String
    Units2 as String
    Value3 as Double
    Label3 as String
    Description3 as String
    Units3 as String
End Type

Unfortunately, I don't think you can put a String in a Type the way you want and have it be compatible with C. You'll have to just put a Long in there to hold the pointer and use accessor functions to convert between the Long and a String