Search code examples
excelvbaserverribbonexcel-addins

Creating custom Excel Ribbon Tab, which works in any workbook

I have surfing the net for months already and haven't really found a solution to the following task I would like to perform. Here is a deal.

I am writing a bunch of code in VBA, which basically creates a new worksheet in a workbook with a specific type of calculators (there are many) for job purposes. One sheet - one type of calculator/analysis.

What I want to accomplish is, that due to increasing amount of code - I would like to put everything on to the ribbon, so I can access a macro through that. However, the job is based on to the case-to-case analysis basis, so the each new project requires a new Excel workbook to be created, where I can choose the calculator I want and do the job.

In addition to that, it requires to be launched on all computers with Excel in the network, with ability for me to be able to modify/add a code to the macro, so that all PC's can stay up-to-date simultaneously.

To wrap-up shortly:

  • There is a bunch of VBA macros (which I'm constantly updating/adding);
  • I need to access those macros through the Ribbon in any new workbook (not the one macro are located) on a number of computers in the network;
  • There is a need to provide instant updates of the code for Ribbon and macro users.

SO, is there any solution, like - I create 2 files (one with Ribbon configuration, another with calculators) and drop them into the server folder? Each user access them once during the installation (basically locating the folder, where the addins are located), and if I need to modify something - I do it with those two files in the server folder and that's it.

If it's not real or pretty hard (for non-programmer) to instantly update all the users, the manual update can work out, but the minimum of being able to access the ribbon in each new workbook is a must.

Thank you in advance for help.

Solution

  • Thanks to all of you folks, who contributed on the question. Want to summarize the experience and provide the way I managed to go with it.

    1) Get your VBA code

    Let's have a code like this. It can be whatever you feel like. To do so, open VBA in the Developers tab or by pressing Alt+F11. Create a new Module, by right clicking on VBAProject > Insert > Module, name it sayMsg in the Properties window and enter the following code:

    Sub saySomething()

    MsgBox "What's up?"

End Sub

    As I said above - this module can contain anything, usually the functional part of your code, which is going to be called out in another module later.

    Let's create a new module the same way we created the first one and name it sayRibbon. This separate module contains a call function or so called "button", which runs our subroutine from sayMsg module. Copy > Paste the code below:

    Private Sub sayButtons(Control As IRibbonControl)

    Select Case Control.ID

        Case Is = "saySomething_Btn"
            Call saySomething
        Case Else

    End Select

End Sub

    Basically, what we have here is a Case named saySomething_Btn, which is the "button" itself, with its defined call function.

    Now save it as Excel Add-in file .xlam and close the program.

    Notice: when you choose .xlam from a drop down menu, you will automatically be located in default Microsoft > AddIns folder. In order to save it on your Desktop, first of all choose the file type, and then relocate the folder.

    2) XML map by Office RibbonX Editor

    The utility above provides you with the option to create a custom tab in the Excel ribbon. Follow the link for download. All installation and use instructions are also available by that link.

    • After you finish with an install, open OfficeRibbonXEditor.exe file.
    • File > Open your .xlam file. Now it appeared in the list below.
    • Right click > Insert Office 2010+ CustomUI Part (or Insert Office 2007 CustomUI Part - depends on the Office version you are running).
    • Copy > Paste the code below:

    Code

    <customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" xmlns:Q="sayRibbon">
<ribbon startFromScratch="false">
<tabs>
<tab idQ = "Q:rxTabUI" label="Say Something" insertAfterMso="TabView">
<group idQ="Q:rxGrpUI" label="Say">
<button id="saySomething_Btn" label="Say Something" onAction="sayButtons" />
</group>
</tab>
</tabs>
</ribbon>
</customUI>
    • Press Validate, in case of issues - the error message will appear (Debug if needed, but you shouldn't in this case).
    • Now Save and Close the Ribbon Editor. You can only save, when .xlam is not opened by Excel.

    3) Access the .xlam Add-In in any WorkBook

    The main purpose of such approach was to provide an easy access to the VBA code from any Workbook in Excel and from any machine in the corporate network without actually installing it separately on each individual computer.

    It doesn't really matter - do you want to get access only on your PC or local network, the installation process is the same.

    • Place .xlam file to any location of your choice (local folder or server).
    • Go to Excel > File > Options > Add-Ins.
    • Press Go... button below, Browse for the .xlam location and press OK.
    • Ensure the Add-In is marked in a list. Press OK.

    Notice: I would recommend to encrypt your VBA for security reasons, in case if you want to be the one, who actually can edit the code - to eliminate any issues, which may arise if VBA code isn't encrypted.

    I have checked the performance on my corporate network, the results are quite satisfying. All the changes you perform in the code are instantly updated among all users after they restart their Excel application.

    Don't forget to release the change notes and to keep at least couple of older versions available for people, in case of need or emergency.

    As long as the project will evolve, maybe more complex approaches could be used, however due to boundaries I am currently facing, this approach provides the best performance at the moment.