VBA Add-in – Creating Add-ins in Excel

Last Updated on January 11, 2023 by token

How to write an Excel add-in using VBA?

Creating a simple add-in in Excel VBA is all about writing a good VBA code. In this article you will learn how to do an Excel add-in. The add-in will do the following action:

  • Opens the file saving window and inserts the current date into the file name

Below you can see a code with our program. Copy it to the module in a new Excel file. Save everything as an Excel Add-in (.xlam) . When saving to xlam, the folder for storing Excel add-ins should automatically be prompted. Most often it is in this location: C: \ Users \ YOUR_LOGIN \ AppData \ Roaming \ Microsoft \ Addins. Let’s set the name of the add-in as  my_addin.xlam .

Sub SaveAsWithDate ()
    Dim strFilename As String
    strFilename = ActiveWorkbook.Name
    Application.Dialogs (xlDialogSaveAs) .Show Format (Date, "yyyymmdd") & "_" & strFilename
End Sub

VBA Addons – How to write an Excel add-in

Installing your own VBA add-in to Excel

To install your own Excel add-in, go to the Developer tab. Under the Excel Add-ins icon, you can select your add-in as shown in the screenshot. Select My_Add-in and click OK.

Installing your own VBA Excel add-in

Now you need to install VBA Excel add-ins in tabs in the Excel menu. Go to File > Options > Customize ribbon. Now you should do the following:

  • On the left, set “Select command from” to Macros
  • On the right, create a new tab and automatically create a new group
  • Move your macro on the left to a new group on the right
  • On the right side, rename your macros by right clicking > Rename.
  • When renaming, set the icons for your macros
  • Also change the names for the group and cards to be more friendly
  • Swipe your add-in down the tabs to show at the end of your tabs
Custom Excel add-ins

Now you can use your own Excel add-ins.

Excel add-ins – Add your own add-in

This article is part of the VBA Excel course. The article was written after the reader was asked about writing add-ins. If you have similar questions about VBA, feel free to contact me.

Leave a comment

Your email address will not be published. Required fields are marked *

%d bloggers like this: