How to create your own add-in for Microsoft Excel

Contents

Even if you do not know how to program, there are many places (books, websites, forums) where you can find ready-made VBA macro code for a huge number of typical tasks in Excel. In my experience, most users sooner or later collect their personal collection of macros to automate routine processes, whether it’s translating formulas into values, displaying sums in words, or summing cells by color. And here the problem arises – the macro code in Visual Basic needs to be stored somewhere in order to be used later in work.

The easiest option is to save the macro code directly in the working file by going to the Visual Basic editor using the keyboard shortcut Alt+F11 and adding a new empty module via the menu Insert – Module:

However, there are several disadvantages with this method:

If there are a lot of working files, and a macro is needed everywhere, such as a macro for converting formulas into values, then you will have to copy the code in every book.
Must not be forgotten save file in macro-enabled format (xlsm) or in binary book format (xlsb).
When opening such a file macro protection will each time issue a warning that needs to be acknowledged (well, or disable protection completely, which may not always be desirable).

A more elegant solution would be to create your own add-in (Excel Add-in) – a separate file of a special format (xlam) containing all your “favorite” macros. The advantages of this approach:

It will be enough connect add-on once in Excel – and you can use its VBA procedures and functions in any file on this computer. Resaving your working files in xlsm- and xlsb-formats, thus, is not required, because. the source code will not be stored in them, but in the add-in file.
Protection you won’t be bothered by macros, either. add-ons are, by definition, trusted sources.
Can do separate tab on the Excel ribbon with nice buttons to run add-in macros.
The add-in is a separate file. His easy to carry from computer to computer, share it with colleagues or even sell it 😉

Let’s walk through the entire process of creating your own Microsoft Excel add-in step by step.

Step 1. Create an add-in file

Open Microsoft Excel with a blank workbook and save it under any suitable name (for example MyExcelAddin) in add-in format with the command File – Save As or keys F12, specifying the file type Excel Add-in:

Please note that by default Excel stores add-ins in the C:UsersYour_nameAppDataRoamingMicrosoftAddIns folder, but, in principle, you can specify any other folder that is convenient for you.

Step 2. We connect the created add-in

Now the add-in we created in the last step MyExcelAddin must be connected to Excel. To do this, go to the menu File – Options – Add-ons (File — Options — Add-Ins), click on the button About (Go) at the bottom of the window. In the window that opens, click the button Review (Browse) and specify the location of our add-in file.

If you did everything right, then our MyExcelAddin should appear in the list of available add-ons:

Step 3. Add macros to the add-in

Our add-in is connected to Excel and works successfully, but there is not a single macro in it yet. Let’s fill it up. To do this, open the Visual Basic editor with the keyboard shortcut Alt+F11 or by button Visual Basic tab developer (Developer). If tabs developer not visible, it can be displayed through File – Options – Ribbon Setup (File — Options — Customize Ribbon).

There should be a window in the upper left corner of the editor Project (if it is not visible, then turn it on through the menu View — Project Explorer):

This window displays all open workbooks and running Microsoft Excel add-ins, including ours. VBAProject (MyExcelAddin.xlam) Select it with the mouse and add a new module to it via the menu Insert – Module. In this module, we will store the VBA code of our add-in macros.

You can either type the code from scratch (if you know how to program), or copy it from somewhere ready-made (which is much easier). Let’s, for testing, enter the code of a simple but useful macro into the added empty module:

After entering the code, do not forget to click on the save button (diskette) in the upper left corner.

Our macro FormulasToValues, as you can easily imagine, converts formulas to values in a preselected range. Sometimes these macros are also called procedures. To run it, you need to select cells with formulas and open a special dialog box Macros from the tab developer (Developer — Macros) or keyboard shortcut Alt+F8. Normally, this window shows available macros from all open workbooks, but add-in macros are not visible here. Despite this, we can enter the name of our procedure in the field macro name (Macro name)and then click the button Run (run) – and our macro will work:

Here you can also assign a keyboard shortcut to quickly launch a macro – the button is responsible for this Parameters (Options) in the previous window Macro:

When assigning keys, keep in mind that they are case sensitive and keyboard layout sensitive. So if you assign a combination like Ctrl+Й, then, in fact, in the future you will have to make sure that you have the layout turned on and press additionally Shiftto get the capital letter.

For convenience, we can also add a button for our macro to the quick access toolbar in the upper left corner of the window. To do this, select File – Options – Quick Access Toolbar (File — Options — Customize Quick Access Toolbar), and then in the drop-down list at the top of the window the option Macros. After that our macro FormulasToValues can be placed on the panel with the button Add (Add) and select an icon for it with the button Change (Edit):

Step 4. Add functions to the add-in

But macro-procedures, there are also function macros or as they are called UDF (User Defined Function = user defined function). Let’s create a separate module in our add-on (menu command Insert – Module) and paste the code of the following function there:

It is easy to see that this function is needed to extract VAT from the amount including VAT. Not Newton’s binomial, of course, but it will do for us as an example to show the basic principles.

Note that the syntax of a function is different from a procedure:

construction is used Function …. End Function instead Sub … End Sub
after the name of the function, its arguments are indicated in brackets
in the body of the function, the necessary calculations are performed and then the result is assigned to a variable with the name of the function

Also note that this function is not needed, and it is impossible to run like the previous macro procedure through the dialog box Macros and button Run. Such a macro function should be used as a standard worksheet function (SUM, IF, VLOOKUP…), i.e. just enter in any cell, specifying the value of the amount with VAT as an argument:

… or enter through the standard dialog box for inserting a function (button fx in the formula bar), selecting a category User Defined (User Defined):

The only unpleasant moment here is the absence of the usual description of the function at the bottom of the window. To add it you will have to do the following:

Open the Visual Basic Editor with a keyboard shortcut Alt+F11
Select the add-in in the Project panel and press the key F2to open the Object Browser window
Select your add-in project from the drop-down list at the top of the window
Right-click on the function that appears and select command Properties.
Enter a description of the function in the window Description
Save the add-in file and restart excel.

After restarting, the function should display the description we entered:

Step 5. Create an add-on tab in the interface

The final, though not mandatory, but pleasant touch will be the creation of a separate tab with a button to run our macro, which will appear in the Excel interface after connecting our add-in.

Information about the tabs that are displayed by default is contained within the book and must be formatted in a special XML code. The easiest way to write and edit such code is with the help of special programs – XML editors. One of the most convenient (and free) is Maxim Novikov’s program Ribbon XML Editor.

The algorithm for working with it is as follows:

Close all Excel windows so that there is no file conflict when we edit the add-in XML code.
Launch the Ribbon XML Editor program and open our MyExcelAddin.xlam file in it
With button tabs in the upper left corner, add the code snippet for the new tab:
You need to put in empty quotes id our tab and group (any unique identifiers), and in label – the names of our tab and a group of buttons on it:
With button button on the left panel, add a blank code for the button and add tags to it:
— label is the text on the button
— imageMso — this is the conditional name of the image on the button. I used a red button icon called AnimationCustomAddExitDialog. The names of all available buttons (and there are several hundred of them!) can be found on a large number of sites on the Internet if you search for the keywords “imageMso”. For starters, you can go here.
— onAction – this is the name of the callback procedure – a special short macro that will run our main macro FormulasToValues. You can call this procedure whatever you like. We will add it a little later.
You can check the correctness of everything done using the button with a green check mark on the top of the toolbar. In the same place, click on the button with a floppy disk to save all changes.
Close the Ribbon XML Editor
Open Excel, go to the Visual Basic editor and add a callback procedure to our macro KillFormulasso that it runs our main macro for replacing formulas with values.
We save the changes and, returning to Excel, check the result:

That’s all – the add-in is ready to use. Fill it with your own procedures and functions, add beautiful buttons – and it will become much easier to use macros in your work.

What are macros, how to use them in your work, where to get macro code in Visual Basic.
How to make a splash screen when opening a workbook in Excel
What is a Personal Macro Book and how to use it