invoke Excel "Run" with Args using GetIDsOfNames/Invoke

[jj_p]

Hi,

I keep going my investigation for calling back a user-defined function in
VBA from a Com Add-In.

It is possible to execute the "Run" excel interface to call the VBA
function. My code works fine when I don't have arguments (equivalent of vba:
Run "VBAProject.MyModule.MyVbaFuncWith0Arg").

But the following does not work (equivalent of vba:
Run "VBAProject.MyModule.MyVbaFuncWith1Arg" "MyStringArgument")

---- VBA code (in module: MyVbaModule)

Public Function MyVbaFuncWith1Arg(ByVal cVal As String) As Integer
Sheets("Sheet1").Cells(1, 1).Value = cVal
End Function

---- C++ Code
{
//Get the Excel interface
IDispatch *pExcelDisp = NULL;
HRESULT hr;
CLSID clsid;
IUnknown *pUnk;

hr = CLSIDFromProgID(L"Excel.Application", &clsid);
if(FAILED(hr))
...

hr = GetActiveObject(clsid, NULL, &pUnk);
if(FAILED(hr))
...

//
hr = pUnk->QueryInterface(IID_IDispatch,(void**)&pExcelDisp);
if(FAILED(hr))
...

//Get the interface of Run

//
DISPID dispid = 0;
wchar_t *ucName = L"Run";
hr = pExcelDisp->GetIDsOfNames(IID_NULL, &ucName, 1,LOCALE_SYSTEM_DEFAULT,
&dispid);
if(FAILED(hr))
...

//Run the user define command
DISPPARAMS Params = {NULL,NULL,0,0};
VARIANTARG Command[2];
VARIANT VbaFuncName;
VARIANT VbaArg;
VARIANT VbaReturnVal;

VariantInit(&VbaFuncName);
VariantInit(&VbaArg);
VariantInit(&VbaReturnVal);

USES_CONVERSION;

VbaFuncName.vt = VT_BSTR;
VbaFuncName.bstrVal = A2BSTR("VBAProject.MyModule.MyVbaFuncWith1Arg");

VbaArg.vt = VT_BSTR;
VbaArg.bstrVal = A2BSTR("MyStringArgument");

Command[0] = VbaFuncName;
Command[1] = VbaArg;

Params.rgvarg = Command;
Params.cArgs = 2;

// Call the Calculate method
hr = pExcelDisp->Invoke(dispid, IID_NULL, LOCALE_SYSTEM_DEFAULT,
DISPATCH_METHOD, &Params, &VbaReturnVal, NULL, NULL);
if(FAILED(hr))
...
}

With a VBA function with 0 arguments, it works fine (without VbaArg and with
Params.cArgs = 1). With 2 args the "invoke" returns "0x80020009 Exception
occurred"


I don't see what is the problem... If anyone knows

Jj

 

[jj_p]

Actually the answer lies on the order of the VARIANTARG...
It should be:

Command[1] = VbaFuncName;
Command[0] = VbaArg;

And works fine...