Why is CommandBarsObj null?

Add-in Express™ Support Service
That's what is more important than anything else

Why is CommandBarsObj null?
 
OliverM


Guest


I am trying to reproduce the following sample https://www.add-in-express.com/creating-addins-blog/2011/12/05/excel-calculation-mode-event/
But method 
private void AddinModule_OnSendMessage(object sender, ADXSendMessageEventArgs e)
alway fails on line 
this.CommandBarsObj.GetType().InvokeMember("ExecuteMso",
                    System.Reflection.BindingFlags.InvokeMethod,
                    null, this.CommandBarsObj,
                    new object[] { "CalculationOptionsAutomatically" });


The reason is that this.CommandBarsObj evaluates to null. But why it is null I do not understand.

Can you please advise?

Kind regards
Oliver
Posted 03 Sep, 2015 09:05:28 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Oliver,

I've reproduced the issue. We will look into this tomorrow. A workaround is to use the object returned via ExcelApp.Commandbars; this implies releasing it after use. Try this code: 

private void AddinModule_OnSendMessage(object sender, ADXSendMessageEventArgs e)
{
    if (e.Message == WM_OptionAutomatic)
    {
        System.Diagnostics.Debug.Print("AddinModule_OnSendMessage. WM_OptionAutomatic");
        cancelAction = false;
        object commandBarsObj = ExcelApp.CommandBars;
        commandBarsObj.GetType().InvokeMember("ExecuteMso", 
            System.Reflection.BindingFlags.InvokeMethod, 
            null, commandBarsObj, 
            new object[] { "CalculationOptionsAutomatically" });
        Marshal.ReleaseComObject(commandBarsObj);
        CheckCalculationMode();
        cancelAction = true;
    }
    else if (e.Message == WM_OptionSemiAutomatic)
    {
        System.Diagnostics.Debug.Print("AddinModule_OnSendMessage. WM_OptionSemiAutomatic");
        cancelAction = false;
        object commandBarsObj = ExcelApp.CommandBars;
        commandBarsObj.GetType().InvokeMember("ExecuteMso",
            System.Reflection.BindingFlags.InvokeMethod,
            null, commandBarsObj,
            new object[] { "CalculationOptionsAutomaticallyExceptDataTables" });
        Marshal.ReleaseComObject(commandBarsObj);
        CheckCalculationMode();
        cancelAction = true;
    }
    else if (e.Message == WM_OptionManual)
    {
        System.Diagnostics.Debug.Print("AddinModule_OnSendMessage. WM_OptionManual");
        cancelAction = false;
        object commandBarsObj = ExcelApp.CommandBars;
        commandBarsObj.GetType().InvokeMember("ExecuteMso",
            System.Reflection.BindingFlags.InvokeMethod,
            null, commandBarsObj,
            new object[] { "CalculationOptionsManuallly" });
        Marshal.ReleaseComObject(commandBarsObj);
        CheckCalculationMode();
        cancelAction = true;
    }
    else if (e.Message == WM_RibbonOptions)
    {
        System.Diagnostics.Debug.Print("AddinModule_OnSendMessage. WM_RibbonOptions");
        cancelAction = false;
        object commandBarsObj = ExcelApp.CommandBars;
        commandBarsObj.GetType().InvokeMember("ExecuteMso", System.Reflection.BindingFlags.InvokeMethod, null, commandBarsObj, new object[] { "ApplicationOptionsDialog" });
        Marshal.ReleaseComObject(commandBarsObj);
        CheckCalculationMode();
        cancelAction = true;
    }
    else if (e.Message == WM_CommandbarOptions)
    {
        System.Diagnostics.Debug.Print("AddinModule_OnSendMessage. WM_CommandbarOptions");
        Office.CommandBars cmBars = ExcelApp.CommandBars;
        Office.CommandBarButton btnToolsOptions = cmBars.FindControl(Office.MsoControlType.msoControlButton, 522, Type.Missing, Type.Missing) as Office.CommandBarButton;
        if (btnToolsOptions != null)
        {
            cancelAction = false;
            btnToolsOptions.Execute();
            CheckCalculationMode();
            cancelAction = true;
            Marshal.ReleaseComObject(btnToolsOptions);
        }
        if (cmBars != null) Marshal.ReleaseComObject(cmBars);
    }
}



Andrei Smolin
Add-in Express Team Leader
Posted 03 Sep, 2015 09:32:33 Top
OliverM


Guest


Yep, that did the trick!

Appreciate your quick response, Andrei.
Posted 03 Sep, 2015 12:12:22 Top
nwein




Posts: 577
 Joined: 2011-03-28
I'm just curious as to the circumstances that would cause the CommanadBarsObj to be null. I have the original code but haven't seen a scenario where the latter object is null...
I'm targeting Excel 2010 32-bit if it matters.
Posted 03 Sep, 2015 15:00:34 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
You are welcome, Oliver!

Nir,

The issue occurs in Excel 2013. I will investigate it and post my results here.


Andrei Smolin
Add-in Express Team Leader
Posted 04 Sep, 2015 07:00:56 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
We confirm that this is a bug in Add-in Express. It is now filed under #7863 in our bug-tracking database. When the bug is fixed, you'll find that number in whatsnew.txt.


Andrei Smolin
Add-in Express Team Leader
Posted 04 Sep, 2015 07:16:24 Top
Andrei Smolin


Add-in Express team


Posts: 18830
 Joined: 2006-05-11
Hello Nir,

We've published Beta 2 of Add-in Express version 8 that fixes this bug. Could you please confirm that it works for you?


Andrei Smolin
Add-in Express Team Leader
Posted 16 Sep, 2015 06:53:15 Top