In this post, I'm assuming you are using a Windows device and that you have some familiarity with writing VBA code and with the VBE (i.e. the VBA development environment). If not, I suggest you read my What is VBA? post first.
Additionally, the type of AddIn we are creating in this post is (of course) a VBA-enabled AddIn … the type of AddIn that has been around for years. Not an 'Office AddIn' that uses JavaScript and HTML.
What is an Excel AddIn and what would you use one for?
An AddIn is just a special kind of Excel Workbook. It provides additional functionality and features that don't exist by default within Excel, plus it can add to (or otherwise change) the standard Excel user-interface.
An AddIn is 'hidden' meaning that it does not show up as a normal Workbook does - for example, an AddIn does not provide any selectable Sheets in the Excel UI.
Once an AddIn is 'loaded' it is always available … hence any functionality it provides is always available no matter which Workbook is loaded in the Excel user interface (or even if no Workbook is loaded in the Excel UI).
The functionality provided by an AddIn is typically accessed in one of a number of ways, for example:
The AddIn can listen for 'events' taking place in Excel and within other Workbooks and respond to those events – for example, it can listen for Workbooks being opened or it can listen for the selected Sheet being changed … and can then run VBA code in response to those events
The AddIn may add controls to the Excel 'ribbon' menu … when those controls are used (buttons being clicked etc) then the AddIn functionality can be used (i.e. VBA code in the AddIn can run)
The AddIn may add items to context menus (for example the in-cell context menu that you access by right-clicking on a cell) … as with a 'ribbon' item, when the context menu item is selected then the AddIn functionality can be used (i.e. VBA code in the AddIn can run)
The AddIn may add 'user defined functions' that you can use in formulas in the same way as normal 'built-in' functions
So what can the VBA code (i.e. the code that runs in response to something being clicked or an 'event' taking place) do? … in other words what functionality and features can be added to Excel? … that's really up to you as, really, the only limit is your skill as a developer (or the skill of the developer that you contract to do the work for you!)
How to create an AddIn?
Add a normal, new (i.e. blank), Workbook
Then click File > Save As > Browse
Change 'Save As Type' to 'Excel Add-in (*.xlam)' … NOT to 'Excel 97-2003 Add-in(.xla)' … that's the older version
Change the filename to something memorable such as 'My AddIn.xlam'
Take note of where you are saving the AddIn … Excel will normally have changed the folder to your AddIns folder (which is normally at C:\Users\%username%\AppData\Roaming\Microsoft\AddIns where %username% is your user name) which is a handy place … as you are creating an AddIn
Click 'Save'
Now … don't panic … if you notice that the AddIn has disappeared from the Excel user interface. That's part of the point of an AddIn (as I noted above: that it has no user interface) and I'll show you how to make it visible again (not that you normally will need to do that) shortly.
Next, we need to load the AddIn into Excel as newly created AddIns are not loaded by default … so, still in Excel (not the VBE): File > Options > Add-ins > Manage > Excel Add-ins > Go … then, in the list of 'Add-ins Available' put a check (tick) next to your AddIn (if you followed my naming then it will be called 'My AddIn') then click OK. Nothing much will appear to have happened (because an AddIn has no user interface) but your AddIn should now be loaded into Excel … which we'll confirm next). If you have problems with this step (or if you subsequently share your AddIn with other users) you may want to read my Installing and updating Add-ins post.
Next, go into the VBE (if you don't know how to do this, read my What is VBA? post) and you will find your AddIn in the Project window … by default it will have the Project name 'VBAProject' (you can change this if you want) but you can tell it is your AddIn as the filename will be given, in parenthesis, following that (so if you followed my naming it will be called 'My AddIn.xlam').
Click the '+' to expand the the AddIn Project, then the '+' to expand 'Microsoft Excel Objects' and then select / highlight the ThisWorkbook Module … to be absolutely sure that the ThisWorkbook Module for the AddIn is selected, double click it and ensure that, in the title bar of the VBE window, you see the file name of the AddIn e.g. it will say something like 'Microsoft Visual Basic for Applications – My AddIn.xlam – [ThisWorkbook (Code)]'.
Now, with ThisWorkbook highlighted, check that in the Properties window, the 'IsAddin' property is set to True (if not, set it to True then Save).
Congratulations … you've now created an AddIn!
I mentioned above about making the AddIn visible in Excel. To do that (if you really need to do so) temporarily switch the 'IsAddin' property back to False. Have a look in Excel and you will see the AddIn. Just remember to then switch the 'IsAddin' property back to back to True.
A key thing to know when you are developing VBA code within an AddIn: the VBE and/or Excel won't automatically save the AddIn and won't even warn you to save changes when closing the VBE or Excel … so keep saving it regularly … and note that when you click the Save button in the VBE, only the active Project is saved … if you have multiple Projects in the VBE and have clicked into another one, only that other one will be saved. So … with the AddIn 'active' in the VBE, click Save.
Handling events with an AddIn
Now you have an AddIn, what to do with it? That really depends on what functionality you want to add to Excel.
Lets listen for some events.
Add a new Class Module (must be a Class Module) and name it CApp. Add the following code:
Note that throughout I have commented code for your understanding. Next add a standard Module and name it App. Add the following code:
What is all this?! So the Auto_Open() procedure within the App Module is run by automatically by Excel itself when My AddIn is loaded (typically when Excel starts but also when the AddIn is first loaded as you did, above, in the 'How to create an AddIn?' section when you put a check (tick) next to your AddIn in the 'Add-ins Available' list).
Auto_Open() creates an instance of the CApp Class. When an instance of a Class is created, its Class_Initialize() event handler procedure runs and, in our code, this sets the xlApp variable to be the Excel application itself. And note that the declaration for the xlApp variable includes the VBA keyword WithEvents which then means that we can actually listen to the Excel application's events.
Finally, the xlApp_WorkbookActivate() procedure (note the name … it is exactly the name of the xlApp variable followed by an underscore followed by the exact name of the event we want to listen to) means we specifically want to listen to 'Workbook activation events'. This event handler procedure will be run by the Excel application when this event occurs which will be when, for example, a Workbook is opened or when the user switches between multiple already-open Workbooks.
I've included Auto_Close() for your information although it doesn't really do anything useful here. Like Auto_Open(), it is run automatically by Excel itself but this time when My AddIn is unloaded (typically when Excel is closing but also if the AddIn is manually unloaded … i.e. remove the check from the AddIn using the list of 'Add-ins Available').
I've included more info on Auto_Open() and Auto_Close() in the Other information on the 'auto' events section at the end of this post.
I'd recommend at this point to compile your code to check for errors (in the VBE menu: Debug > Compile … if any errors are identified then check that you have copied all of the above code correctly, that you have renamed your Modules correctly and that you have used the right type of Modules: App is a normal (or 'standard') Module; CApp is a Class Module.
And then save (remember: the VBE and/or Excel won't automatically save the AddIn).
And then close and re-open Excel. Open a Workbook. Open another. Switch between them a few times. Maybe open another Workbook … if you fancy. Then open the VBE and look in the Immediate window for what has been printed there. I saw the following (obviously what you see will depend on a number of factors but particularly the names of the Workbooks you open and the number of times you switch between them):
01/05/2024 10:48:57 Loaded / opened
01/05/2024 10:48:57 Initialised
01/05/2024 10:49:15 Book1.xlsx
01/05/2024 10:49:25 Book2.xlsx
01/05/2024 10:49:31 Book1.xlsx
01/05/2024 10:49:33 Book2.xlsx
01/05/2024 10:49:43 Book3.xlsx
01/05/2024 10:49:50 Book1.xlsx
If you want something a bit more 'interactive' add the line:
To the xlApp_WorkbookActivate() procedure just after the Debug.Print line and you will be shown a dialog every time you switch Workbooks. It gets boring quickly, so delete the MsgBox line again when you are over the thrill(!). Note that when you added (and deleted) this line … so long as this was the only change you made … the events continued to be called and lines continued to be printed to the Immediate windoe: this is important later!
Now lets add another event … but this time lets do it 'properly' (i.e. without copy / pasting it from this post).
First, when viewing the CApp Module, look to the top of the code pane (i.e. the window that you would type VBA code into) and you will see two DropDown boxes. The values initially shown in these DropDowns will depend on where your cursor is. If you put the cursor at the very first line (with Option Explicit on it) the values will be '(General)' and '(Declarations)'. Move the cursor down line-by-line and after a few lines the values in the DropDowns will change, first to 'Class' and 'Initialize' (when your cursor is in the Class_Initialize() event handler / procedure) and then to 'xlApp' and 'WorkbookActivate' (when your cursor is in the xlApp_WorkbookActivate() event handler / procedure). In other words, the two DropDowns are associated with the code that the cursor is located within. And, what is actually useful about this, is that you can use the DropDowns to find out what event handlers are available and to add more of them.
So to do that, click in the left DropDown to list all of the available Objects (that you have access to and can emit events) and select 'xlApp' (if it's not selected already) … this is our 'xlApp' variable that we declared in this Module (and, importantly, that we declared 'WithEvents') then click in the right DropDown to list all of the available events that are available for the Object selected in the left DropDown (maybe take a minute to look through them all … there is a link at the end of this post to the MS Docs for the events; note that the WorkbookActivate event is in bold because we have already added it) and then select 'SheetChange'.
A new procedure will be added to your code:
This is the normal way of adding event handler procedures and will be how you do this going forward. The xlApp_WorkbookActivate() event handler procedure that you copy / pasted from the above code was actually added in exactly the same way by me when writing this post.
Note that you should not change the signature of the event handler at all (or try writing them manually yourself … always use the DropDowns). If you change its name or arguments, it will either stop working or result in errors when it runs.
The SheetChange() event handler, by the way, is run when a cell in a Worksheet is changed by the user … for example, when the user types a value into a cell.
So lets make this do something … update the code of the newly added event handler to (the comments are optional and just for your understanding):
Then compile your code to check for errors.
Now, you'd expect that next I will tell you to go to the Excel UI and type some values into some cells and then check the Immediate window. But, before you do that, note that as things stand, that's not going to cause any events to fire. You can do it anyway to test but you will see nothing new in the Immediate window. Also nothing will appear in the Immediate window if you switch between Workbooks so it looks like the xlApp_WorkbookActivate() procedure has also stopped working.
So what's wrong with our code?
Absolutely nothing (so long as you checked that it compiles correctly, of course).
What actually is wrong is that because you made Module-level changes to the AddIn (i.e. you added the xlApp_SheetChange() procedure to Module CApp), there was a 'loss of state'. This means that the values held by all Module-level (and Project-level) variables were destroyed, including the value of the mApp variable in the App class and the value of the xlApp variable in our CApp class.
Note this 'loss of state' only occurs when you make Module-level or Project-level changes and not when you make procedure-level changes (that is, there is no 'loss of state' when you make changes within an existing procedure or property that don't impact anything outwith that procedure or property … so when you added and removed the MsgBox Wb.Name line within the xlApp_WorkbookActivate() procedure you only made procedure-level changes and so there was no loss of state, but adding a new procedure such as we did with xlApp_SheetChange() is Module-level and so there was a 'loss of state').
How to fix this? You have three options:
What we did previously: every time you make Module-level (or Project-level) changes to the code, close (remember to save the AddIn first!) and re-open Excel. Re-opening Excel will cause the Auto_Open() procedure to be run by Excel and all variables will have their values set. This works, but it’s a pain to do this every time you add some Module-level code.
Every time you add Module-level code, run the Auto_Open() procedure yourself … i.e. manually put the cursor somewhere within it and press F5. This works but becomes a bit tiresome after the 100th time of manually navigating to Auto_Open(), pressing F5 and then navigating back to wherever you were previously editing code.
Use VBE_Extras and set the Auto_Open() procedure to be the 'Sub To Run' then, wherever you are editing code, without having to navigate to Auto_Open(), just press F6.
Having done one of the above, now go to the Excel UI and type some values into some cells and you will see the xlApp_SheetChange() procedure (and the xlApp_WorkbookActivate() procedure, if you switch between Workbooks) are being triggered and new line are being printed in the Immediate window.
Another point on 'loss of state' is that there are more ways to cause a 'loss of state' then making Module-level or Project-level changes, for example:
If an 'unhandled' error occurs in your code and you press the 'End' button in the error dialog then that will also result in a loss of state. By 'unhandled' I mean an error that results in code stopping running rather than an error that, for example, follows an On Error Resume Next statement
If you select the VBE's Reset button (or in the menu, Run > Reset)
Finally, another point I want to make here is that you might think some of the code in the procedures looks a bit odd. What is with the
and
lines? These lines are checking that the Objects passed into the event handler procedures are populated (i.e. that they are not Nothing). In event handling code, you should never assume … always check. There is no guarantee that the Objects passed into the event handler procedure will be instantiated. And a simple line to check that they have a value other than Nothing is much better than unhandled errors and you (or your users) facing an annoying VBA error dialog.
What you do next is up to you and depends on the functionality you want to implement. Once you have your AddIn created and you've added the initial code to hook into the Excel application's events (i.e. the App Module and the CApp Module's xlApp variable and Class_Initialize() event handler … less the Debug.Print statements whenever you feel ready to remove them), there are many events you can use. The MS Docs for the Excel Application object includes a list of all Events including the WorkbookActivate and SheetChange events that we used in the code in this post.
AddIns and the Ribbon / context menus
Follow these links for my posts on The Ribbon and AddIns and Context menus and AddIns.
Other information on the 'auto' events
Auto_Open() and Auto_Close() are special event handlers that are run by the Excel application when a Workbook, Template or AddIn is opened or closed. To be run, they must be spelt exactly that way and they must be in a standard Module.
They are very similar to the Workbook_Open() and Workbook_BeforeClose() event handlers (which must be in the ThisWorkbook Module) and, to a large extent, you can use them interchangeably.
However, the key reason that I tend to prefer using Auto_Open() over Workbook_Open() (and so I then also use Auto_Close() over Workbook_BeforeClose()) is that using Workbook_Open() can result in odd errors in your VBA code as the Excel object model appears not to be fully loaded when the Workbook_Open() event runs whereas when the Auto_Open() event runs (Auto_Open() being run after Workbook_Open()) then the Excel object model has been fully loaded and those same errors will not occur. By 'odd errors' I mean errors occurring in perfectly correct code that uses some method or property of the Application object. I've never found anything from Microsoft documentating this problem but I can assure you it is real enough: I have experienced it in a number of projects and the solution is always the same: use Auto_Open() not Workbook_Open().
Other differences between these two sets of events are:
If Application.EnableEvents is set to False then Workbook_Open() and Workbook_BeforeClose() will not be run but Auto_Open() and Auto_Close() will be
If a Workbook is opened or closed programmatically then Workbook_Open() and Workbook_BeforeClose() will be run but Auto_Open() and Auto_Close() will not be … though if you write code that opens or close a Workbook programmatically and you want Auto_Open() or Auto_Close() to run then, if wkb is your reference to that Workbook, you can use wkb.RunAutoMacros xlAutoOpen to run Workbook_Open() and wkb.RunAutoMacros xlAutoClose to run Auto_Close()
And similarities between these two sets of events are:
Both Workbook_Open() and Auto_Open() run before any RibbonX is loaded so you should not interact with the Ribbon from these event handler procedures
There is never a guarantee that ANY event handlers will run (or, indeed that any VBA code will run): holding down the SHIFT key when opening a Workbook via Excel's File > Open > Browse menu will disable both Workbook_Open() and Auto_Open() … and these events, in fact all VBA code, is subject to user-controlled (or centrally-control if the user works in a corporate IT environment) macro security settings … there is never any guarantee your VBA event handling code will run so design your AddIns accordingly!
Comments