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 .
'officeInside.Org
Sub SaveAsWithDate ()
Dim strFilename As String
strFilename = ActiveWorkbook.Name
Application.Dialogs (xlDialogSaveAs) .Show Format (Date, "yyyymmdd") & "_" & strFilename
End Sub
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.
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
Now you can use your own Excel add-ins.
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.
