Hi,
I am developing an Excel add-in using XLL (Excel C API). In XLL along
with Excel C API I am using Excel's COM interfaces also. Now I am
stuck in one issue with the COM interfaces. I want to add a menu bar
to the Excel using COM interfaces, I tried the following code but
could not able to add the Menu Bar to the Excel.

Void AddMenuBar()
{

//excelApp is the global variable and is the object of "_Application"
//class. Assume that at this point this variable has been initialized
//with valid value.
LPDISPATCH pCommandBars = excelApp.GetCommandBars();

//After successful addition of menu bar this will have the IDispatch *
//for "MenuBar" class.
VARIANT vtMenuBar;
VariantInit(&vtMenuBar);

//Argument number 1, name of the menubar.
VARIANT vtParam1;
VariantInit(&vtParam1);
vtParam1.vt = VT_BSTR;
vtParam1.bstrVal = _bstr_t("MyComMenu").copy();

//Argument number 2, position of the menubar.
VARIANT vtParam2;
VariantInit(&vtParam2);
vtParam2.vt = VT_INT;
vtParam2.intVal = 2; //add to the right of the doc.

//Argument number 3, Boolean value which specifies whether the command
bar object // is menu bar or not
VARIANT vtParam3;
VariantInit(&vtParam3);
vtParam3.vt = VT_BOOL;
vtParam3.boolVal = VARIANT_TRUE;

//Argument number 4, Boolean value which should be TRUE I guess
VARIANT vtParam4;
VariantInit(&vtParam4);
vtParam4.vt = VT_BOOL;
vtParam4.boolVal = VARIANT_TRUE;

//Number of arguments to the Add method of "CommandBars" class
const int numArgs = 4;

VARIANT vtArg[numArgs];
vtArg[0].vt = VT_VARIANT;
vtArg[0].pvarVal = &vtParam1;

vtArg[1].vt = VT_VARIANT;
vtArg[1].pvarVal = &vtParam2;

vtArg[2].vt = VT_VARIANT;
vtArg[2].pvarVal = &vtParam3;

vtArg[3].vt = VT_VARIANT;
vtArg[3].pvarVal = &vtParam4;

DISPID dwDispID;
DISPPARAMS dispparams = {NULL, NULL, 0, 0};
dispparams.rgvarg = rgVarParams;
dispparams.cArgs = numArgs;

HRESULT hr = pDisp-GetIDsOfNames(IID_NULL, L"Add",
1,LOCALE_USER_DEFAULT,
&dwDispID);

if(SUCCEEDED(hr)) {
hr = pDisp-Invoke(dwDispID, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_METHOD, &dispparams, &vtMenuBar,
NULL, NULL);

if (FAILED(hr)) {
hr = pDisp-Invoke(dwDispID, IID_NULL,
LOCALE_USER_DEFAULT,
DISPATCH_METHOD| DISPATCH_PROPERTYGET,
&dispparams,
&vtMenuBar, NULL, NULL);
}

}

//Do any clean ups here.

} //AddMenuBar ends here.

Can any one please point out the problem in my code, is there anything
which I am missing or is there any thing which I did in wrong way. Why
the above code fails to add the menu bar to the Excel?

Any help will be much appreciated.

Thanks & Regards,
Mousam Dubey

On Oct 11, 7:30 pm, Mousam wrote:
Hi,
I am developing an Excel add-in using XLL (Excel C API). In XLL along
with Excel C API I am using Excel's COM interfaces also. Now I am
stuck in one issue with the COM interfaces. I want to add a menu bar
to the Excel using COM interfaces, I tried the following code but
could not able to add the Menu Bar to the Excel.

Void AddMenuBar()
{

//excelApp is the global variable and is the object of "_Application"
//class. Assume that at this point this variable has been initialized
//with valid value.
LPDISPATCH pCommandBars = excelApp.GetCommandBars();

//After successful addition of menu bar this will have the IDispatch *
//for "MenuBar" class.
VARIANT vtMenuBar;
VariantInit(&vtMenuBar);

//Argument number 1, name of the menubar.
VARIANT vtParam1;
VariantInit(&vtParam1);
vtParam1.vt = VT_BSTR;
vtParam1.bstrVal = _bstr_t("MyComMenu").copy();

//Argument number 2, position of the menubar.
VARIANT vtParam2;
VariantInit(&vtParam2);
vtParam2.vt = VT_INT;
vtParam2.intVal = 2; //add to the right of the doc.

//Argument number 3, Boolean value which specifies whether the command
bar object // is menu bar or not
VARIANT vtParam3;
VariantInit(&vtParam3);
vtParam3.vt = VT_BOOL;
vtParam3.boolVal = VARIANT_TRUE;

//Argument number 4, Boolean value which should be TRUE I guess
VARIANT vtParam4;
VariantInit(&vtParam4);
vtParam4.vt = VT_BOOL;
vtParam4.boolVal = VARIANT_TRUE;

//Number of arguments to the Add method of "CommandBars" class
const int numArgs = 4;

VARIANT vtArg[numArgs];
vtArg[0].vt = VT_VARIANT;
vtArg[0].pvarVal = &vtParam1;

vtArg[1].vt = VT_VARIANT;
vtArg[1].pvarVal = &vtParam2;

vtArg[2].vt = VT_VARIANT;
vtArg[2].pvarVal = &vtParam3;

vtArg[3].vt = VT_VARIANT;
vtArg[3].pvarVal = &vtParam4;

DISPID dwDispID;
DISPPARAMS dispparams = {NULL, NULL, 0, 0};
dispparams.rgvarg = rgVarParams;
dispparams.cArgs = numArgs;

HRESULT hr = pDisp-GetIDsOfNames(IID_NULL, L"Add",
1,LOCALE_USER_DEFAULT,
&dwDispID);

if(SUCCEEDED(hr)) {
hr = pDisp-Invoke(dwDispID, IID_NULL, LOCALE_USER_DEFAULT,
DISPATCH_METHOD, &dispparams, &vtMenuBar,
NULL, NULL);

if (FAILED(hr)) {
hr = pDisp-Invoke(dwDispID, IID_NULL,
LOCALE_USER_DEFAULT,
DISPATCH_METHOD| DISPATCH_PROPERTYGET,
&dispparams,
&vtMenuBar, NULL, NULL);
}

}

//Do any clean ups here.

} //AddMenuBar ends here.

Can any one please point out the problem in my code, is there anything
which I am missing or is there any thing which I did in wrong way. Why
the above code fails to add the menu bar to the Excel?

Any help will be much appreciated.

Thanks & Regards,
Mousam Dubey

Hi,
I found the problem, the solution is as follows.

Following code should be changed as

Problematic code:
VARIANT vtArg[numArgs];
vtArg[0].vt = VT_VARIANT;
vtArg[0].pvarVal = &vtParam1;

vtArg[1].vt = VT_VARIANT;
vtArg[1].pvarVal = &vtParam2;

vtArg[2].vt = VT_VARIANT;
vtArg[2].pvarVal = &vtParam3;

vtArg[3].vt = VT_VARIANT;
vtArg[3].pvarVal = &vtParam4;


Corrected code:

VARIANT vtArg[numArgs];
vtArg[0].vt = VT_BYREF | VT_VARIANT; //VT_VARIANT must be used with
VT_BYREF
vtArg[0].pvarVal = &vtParam4;

vtArg[1].vt = VT_BYREF | VT_VARIANT; //VT_VARIANT must be used with
VT_BYREF
vtArg[1].pvarVal = &vtParam3;

vtArg[2].vt = VT_BYREF | VT_VARIANT; //VT_VARIANT must be used with
VT_BYREF
vtArg[2].pvarVal = &vtParam2;

vtArg[3].vt = VT_BYREF | VT_VARIANT; //VT_VARIANT must be used with
VT_BYREF
vtArg[3].pvarVal = &vtParam1;

Actually arguments in dispparams.rgvarg are stored in reverse order.
Therefore we need to store the first argument at the highest index in
the array.
For details read the help of Invoke function of IDispatch interface.

Thanks & Regards,
Mousam Dubey