It’s hard to admit me, but recently I’ve discovered new possibilities in Excel. I saying about XLAM add-ins, which work in background, I’ve found that they are much fasters then .xlsm code, .xlam might be a database for .xlsx files and it is good solution for team using code when .xlam file is saved in network folder.
Below I will show how to create and add to Excel XLAM add-in step by step. In other post will be showed how to build XLAM add-in as database for Excel files .xlsx.
1. Firstly some macro has to written in .xlsm file this in normal Excel file with macro handling. When the code will be finished .xlsm file has to be SAVED AS (F12). I suggest to use universal name in order to update code easily.
When VBA code has been changed/updated in original .xlsm file it has to be saved to overwrite .xlam file. When file has been overwritten VBA code from .xlam hasn’t to be add to Excel once again. What is important to left the same name of starting procedure. In order to make an update of add-in, .xlam file has to be turned off from add-ins (see point 3).
Next step is change extension to .xlam.
2. After clicking SAVE Excel will suggest default add-ins localization – left it.
For example network localization might be chosen – but while Excel wil be opening network .xlam file might be load quite long.
When .xlam file will be saved Excel options has to be opened (FILE/OPTIONS/Add-Ins) where Excel Add-ins might be chosen from drop down list. Next GO button should be clicked.
3. Just added .xlam file has to be chosen and then OK has to be click.
4. Stay in OPTIONS, or right mouse click on menu to add .xlam macro to menu as button.
5. New sheet visible in menu has to be created (if already crated in the past .xlam macro might be add there) with unique name – in my example is XLAM. Next group has to be created where event button from your project will be storage. From drop down list MACROS has to be chosen, then .xlam procedure can be find below. Choose your macro and click button ADD to add it to specific menu sheet and group.
In order to find .xlam procedure must contain Public attribute, Private will not be showed in macros list. It is important to use Public and Private attributes in order to avoid mess in Excel add-ins. When our project has more than one procedure there should be one Public starting procedure with roots to next Private procedures.
6. Name of menu sheet, grup and event button might be modify by RENAME button. Consider to change button icon for event button in your group.
From now .xlam macro is easy available from Excel menu. XLAM add-ins is useful when processing is needed from data imported from some external system for example SAP. When data processing is every day the same, thanks to XLAM add-in data processing will be execute in just exported .xlsx file. XLAM will save time on data copying and finding .xlsm file to perform macro. Finally XLAM add-in, it’s an alternative to deliver VBA code to final user.