Excel Add-In - Alternative to ActiveMenuBar.Reset

Question

Problem: After an upgrade from Office 2010 to 2013, the Essbase Excel Add-in ("essexcln.xll", which Oracle ended support for in 2013) causes the focus to always return to a window with an active connection, when there is more than 1 window open. If the Essbase Add-In is loaded at startup, Excel also freezes. Note that Smartview has replaced this Add-In but for other reasons I need to continue using it. I can manually go to File > Options > Add-ins > Manage Excel Add-Ins and manually check/un-check when Essbase is causing these errors, but I'd rather do that with a quick keyboard shortcut.

Workaround: Create a custom add-in to quickly toggle the Essbase Add-In installed property to load and unload it. Maybe an add-in is overkill - but I don't really use a PERSONAL.XLSB, and I'd like this functionality to be available at all times.

Problem 2: When the Essbase add-in is unloaded, the "Add-in" Menu bar still shows a custom command: "About Oracle Essbase Spreadsheet Add-in." "Essexcln.xll" is notoriously buggy, and this "About Oracle Essbase Spreadsheet Add-in" can linger on even when the add-in is unchecked manually. My solution is to use ActiveMenuBar.Reset - only after I've unloaded the add-in. I don't want to reset if I've just toggled Installed to True.

Is there an alternative to ActiveMenuBar.Reset? This feels like a hack - kind of like using ActiveCell or ActiveSheet - but I don't want to manually check/un-check the Addin, which may or may not clear the "About Essbase..." anyway.

Notes: Yes, maybe it's not the most efficient to loop through Add-ins, but there's so few of them that I don't really care. I'd much rather avoid using ActiveMenuBar.

Sub Toggle_Essbase_AddIn()

Dim x As AddIn
Dim installed As Boolean

For Each x In Application.AddIns
    If x.Name = "essexcln.xll" Then
        ' Get initial installed status
        installed = x.installed
        ' Toggle
        x.installed = Not x.installed
        ' Reset menu bar if Essbase was initially installed
        If installed Then
            ActiveMenuBar.Reset
        End If

        Exit Sub
    End If
Next x

End Sub

Answer 1

As it stands, your code is incredibly inefficient, to the point that it's tough to explain why. Looking at the core of your procedure (after removing wasted space) we have:

Sub Toggle_Essbase_AddIn()
    Dim x As AddIn, installed As Boolean
    If x.Name = "essexcln.xll" Then
        installed = x.installed
        x.installed = Not x.installed
        If installed Then
            ActiveMenuBar.Reset
        End If
        Exit Sub
    End If
End Sub

First off, this line does absolutely nothing:

x.installed = Not x.installed

...since it's after the value of installed has been set.

I can only assume that your intention was to toggle the value that's being passed to variable installed?

It will save you a lot of confusion if you get a little more creative with your variable names, instead of risking a word that, for all intensive purposes, could be considered Reserved. Without picking apart semantics, it is best not to name your variables with words that VBA wants to use for other things, and this is a perfect example why. Even if VBA doesn't get confused, you might, and you have an almost infinite number of other, more meaningful names to pick from.

Assuming that the intention was to toggle the value that's being passed to variable installed then you could have gone:

If x.Name = "essexcln.xll" Then
    installed = not x.installed
    If installed Then
        ActiveMenuBar.Reset
    End If
    Exit Sub
End If

Preferably you should use Exit commands sparingly, or not at all -- only when there isn't an option to leave the loop/sub/if "naturally" but I'm not going into that now since it's irrelevant in a moment.

...or better yet:

installed = Not ( x.Name = "essexcln.xll" )
If installed Then
    ActiveMenuBar.Reset
End If

...or even shorter:

If Not ( x.Name = "essexcln.xll" ) then ActiveMenuBar.Reset

...so now your whole sub would be:

Sub Toggle_Essbase_AddIn()

    Dim x As AddIn
    Dim installed As Boolean

    For Each x In Application.AddIns
        If Not ( x.Name = "essexcln.xll" ) then ActiveMenuBar.Reset
    Next x

End Sub

...but you're still being unnecessarily inefficient.

There is no reason to loop through the AddIns. A quick Google of Application.Addins shows that it's easiest to refer to the Add-In by it's name.

I've never used Essex, but another quick Google to find the add-in documentation tells me that the name of the add-in is "Oracle Essex".

Therefore, one line replaces your entire procedure:

If Not AddIns("Oracle Essbase").Installed Then ActiveMenuBar.Reset

(or I'm not sure if it was intended to NOT be NOT because your code was unclear) - but this is far more efficient and does the equivalent of your code - as long as you're sure the add-in actually exists (not the same as installed, Google it), then this does the same thing as your entire procedure.

If you're not sure if the add-in exists, then Google saves the day again with a link to a Stack Overflow question.

Since I don't have the add-in and you haven't included any specific example of the problem, I can't say for sure if this answers the question completely, but I assure you that taking some extra time on one's own due diligence in coding will safe a lot of effort for you - and others - in the end.

Heads up, I had a lot to write here to get to "one line" so I can't guarantee there were no oversights, but the lesson here is more about research-before-coding.

I suggest you study the documentation about add-ins and toolbars at MSDN as well as documentation specific to the 3rd party add-in at Oracle (via the links above, and sub-links from those pages) and I am confident that with some effort your solution will become clear.

If not, I suggest you add more information to your question. ...and please, don't attempt to write any "add-ins for add-ins"!

Good Luck (and welcome to Stack Overflow!)

---

...and after all that, I notice that you added a link to an image instead of adding an image, so it would have been more noticeable. I can't tell without clicking the toolbar but I'm pretty sure that's not a menu bar command. That's a ribbon group, and it's empty.