vararg function with SAFEARRAY(VARIANT) parameter not callable from VBA

Question

I'm trying to define a function in a type library that takes a variable number of parameters and can be callable from VBA using ParamArray. The entry in the type library is below.

    [
    helpstring("Get value from a Lookup table by an exact key."),
    entry("UtilDll_LookupExact"),
    vararg
    ]
    HRESULT __stdcall LookupExact(
        [out] LPVARIANT Result, 
        [in] LPVARIANTARG Table, 
        [in] LONG VarIndex, 
        [in] SAFEARRAY(VARIANT) Key,
        [out, retval] VARIANT_BOOL *Found
        );

I can see this VBA's object browser as:

Function LookupExact(Result, Table, VarIndex As Long, ParamArray Key() As Variant) As Boolean

So, at least the object browser seems to understand Key as ParmArray () Variant. But when I call this function I get a compile error saying, "Compile Error: Function or Interface Marked as Restricted, or the Function Uses an Automation Type Not Supported in Visual Basic"

The closest question I found is below, but this case seems specific to managed code while I'm dealing with native code. ParamArray Not Working With COM

How can I make a function in native code that has the ParamArray parameter and can be accessed via VBA?

Afaik VBA requires the array to be passed by reference, not by value as this function requires. — Hans Passant

Fumito Hamamura Fumito Hamamura · Accepted Answer · 2013-09-08T15:06:21

I found the answer here: http://computer-programming-forum.com/71-visual-basic-vb/a064ce72acaeb9d8.htm

Apparently, the Key parameter in IDL should have been:

[in] SAFEARRAY(VARIANT)* Key,

And C++ function's arguments type should be

SAFEARRAY** Key

vararg function with SAFEARRAY(VARIANT) parameter not callable from VBA

1 Answers