Step by step create Excel Add-in or Plugin example

Add-in

Excel Add-ins are know as template which can contain code (pre-compiled), can be called based on given callback or shortcut keys. Excel add-ins are separate files which can be attached to your current excel to extend the functionalities. These are well known for distributing the code as well.

Step 1: Create a new workbook and insert a module as shown below and put following code. You can write as many as procedures you wish to execute by your adding. I have written only one procedure to execute in this demo.

Code

Public Sub WriteMessage()
    'Declare sheet object
    Dim oSheet As Worksheet
    
    'bind sheet object
    Set oSheet = ActiveSheet
    
    'Write message
    oSheet.Range("A1:A5") = "Welcome to VBAOVERALL"
    
    'cleanup
    If Not oSheet Is Nothing Then
        Set oSheet = Nothing
    End If
End Sub

Step 2: Run the code to verify that there is not bug in the code, and as per above code the output should look like below:

Step 3: Assign a shortcut to your procedure and try the key to be double sure that your macro is executing:

1. Navigate to Developer Tab
2. Click on Macros button as shown below
3. Select the macro (“WriteMessage”)
4. Click on Options.. button
5. Make sure scope of the macro is selected as “All Open Workbooks” under macros in dropdown.
6. in Dialog supply a shortcut key which will be executed with the combination of CTRL as default. In this example I have assigned “m” so your macro will execute upon pressing CTRL + m key from the keyboard.
7. Say OK

Step 4: SaveAs file as xlam (I saved my addin as testing.xlam) format as shown below, Remember the location where you are saving this file is very important, however by default it saves “C:\Users\username\AppData\Roaming\Microsoft\AddIns“:

Step 5: Mark Addin property, Open VB Editor back and select ThisWorkbook object and locate IsAddin property under property window and make it True. As soon as you mark it True, you will notice that your workbook is gone invisible, means you are have done correct job as shown below, you can also supply password to protect your adding by adding password under password property:

Step 6: Open a new workbook, after closing above solution and select Developer tab and select Excel Add-ins button from Add-ins group as shown below:

Step 7: Click on Browse… button to locate your adding “testing.xlam” that you have just created as shown below, say OK and you notice the testing addin is available under the installed addin list (Make sure it is checked).

Verify

To verify your work Open multiple workbooks and on each workbook Press CTRL + m and the output would be as shown below:

Next >> How to break sheet password or break password for protected sheet

Share on Facebook

Tweet