It is possible to write VBA code that will function on other VBA code. This is called 'extensibility'. In this post, I'll show you how to do this by adding new functionality to the VBE (the Visual Basic Editor) by way of new menu items in its main menu. The reason to do this is to make life simpler when writing VBA code … Microsoft stopped developing the VBE many years ago and for anyone familiar with other development environments, the VBE looks archaic … because it is. So here we're going to add:
A function to list all Modules in a VBA Project and jump to one
A function to list all TODOs in a VBA Project and jump to one
A function to backup a VBA Project
These are just a small part of what is possible. To get an idea of what else can be done, check out the functions provided by VBE_Extras.
Please note that the code in this post will work in Excel only, though it can of course be adapted to work for other Office applications.
If you are here looking for help with how to add functionality to the Excel user-interface (as opposed to adding functionality to the VBE), see my Creating an AddIn in Excel post.
Before we start writing any code, though, to allow VBA code to function on other VBA code, you must allow "programmatic access to the VBE" ... in Excel, click File > Options > Trust Center > Trust Center Settings > Macro Settings > check 'Trust access to the VBA project object model'. Having checked this option, close and restart Excel to allow the option to take effect.
If you don't check this box, when running the code below, you will get an error message "Could not get programmatic access to the VBE ... to fix this, in Excel go to: File > Options > Trust Centre > Trust Centre Settings > Macro Settings, then check 'Trust access to the VBA project object model'".
If you work in an environment where your IT is controlled centrally (e.g. by group policy) then you may find this option is greyed-out and cannot be checked. However, this setting only needs to be checked for VBA code to function on other VBA code, this limitation does not apply to COM AddIns … so if you cannot check this box, you can still download and try out VBE_Extras.
Create an AddIn
The first step is 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 VBE Extension.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) 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 (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 … still in Excel (not the VBE): File > Options > Add-ins > Manage > Excel Add-ins > Go … 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 Vbe Extension') 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).
Go to the VBE and you will find your AddIn in the Project window … by default it will have the Project name 'VBAProject' 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 VBE Extension.xlam').
Click the '+' to expand the the AddIn Project 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, you see the file name of the AddIn e.g. it will say something like 'Microsoft Visual Basic for Applications – My VBE Extension.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).
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 … 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.
Add Project References
In the VBE, in Tools > References (or if you have VBE_Extras installed, you can use the improved dialog as detailed in my Adding / updating "Project References" in the VBE post) find the following libraries, put a check next to them and click OK (it can sometimes be hard to find the libraries … sometimes they can be in alphabetical order but sometimes they can be up towards the start of the list of available references. Keep looking!)
'Microsoft Visual Basic for Applications Extensibility N.N' (where N.N is 5.3 on my device … but I suppose you might have a different version … go with the latest numbers if you have a choice) … this provides core functionality to allow VBA code to function on other VBA code (i.e. 'extensibility')
'Microsoft ActiveX Data Objects N.N Library' (where N.N is 6.1 on my device) … this provides us with the RecordSet object that we will use for sorting CodeModules … once we've got round to adding it, this will be in UtilsVbe.GetModules()
Add code … phase 1: boilerplate
The first thing we're going to do is add an item to the VBE's main menu but before doing that, we need to add some supporting code. There's a lot of code to add before we can really do anything interesting so I'm just going to show it here for you to copy and add to your Project. For this phase and all the following phases, the code is either documented for your understanding or is (I think / hope) fairly evident as to its purpose so I'm not going into any lengthy explanations. However, if something is really unclear then please add a comment to the post.
First add a Class Module called CVbeCommandHandler with this code:
Next add a Standard Module called GlobalConstants and add this code to it:
Next add a Standard Module called UtilsVBE. This is going to contain a (large!) number of helper procedures. Add this code:
Add a Standard Module called MenuItemHandler with this code
Add a Standard Module called AddDeleteMenuItems with this code
Then, in ThisWorkbook, add this code:
Finally, add a Standard Module called UtilsFiles then go to my Working with files and folders in VBA post and copy the code and paste it into the UtilsFiles Module that you just added.
Again, compile your Project (that is: Debug > Compile VBAProject) … if any errors are found then go back and check the code you copy/pasted to ensure nothing is missing.
Really important: with the AddIn 'active' in the VBE, click Save (note my warning, above, about the VBE and/or Excel not prompting you to save changes when closing the VBE or Excel).
Then either:
Manually run (i.e. put your cursor anywhere within it and press F5) the AddDeleteMenuItems.AddMainMenuItems() procedure, or
Close and re-start Excel and re-open the VBE
The reason for needing to do one of these is that, as you make changes to the code, the menu items will stop working. This is because there has been a 'loss of state' which happens, for example, when an error occurs and you click 'End' or when you change Module-level code (you can change … most … code within a procedure or property and not have a 'loss of state' but changing, for example, the signature of a procedure or adding a procedure or Module-level variable will result in a 'loss of state').
The loss of state means that the MenuItemHandler object (declared in the CVbeCommandHandler class) is no longer set and so it's Click event handler (i.e. MenuItemHandler_Click) is no longer called and it is this event handler that is responsible for calling the specific procedures for each of the functions we are adding to the VBE (so far only one ... ShowAllModules() in MenuItemHandler).
What you need to remember (it will keep happening while you follow the post and it will keep happening if you go on and subsequently extend the code in this post with your own code) is that when the menu items are visible but they do nothing, you can re-activate them by doing one of the above two actions.
If all has gone well, you will see a new menu item at the far right of the VBE's list of menu items named 'MyStuff' (or whatever you changed the value assigned to gsMENU_NAME). Click it and you should see 'Goto Module' and, if you click that, you should see "Placeholder!".
And even though I said, above, I wouldn't add any explanation, I'm going to give the highest of high level explanations here:
When Excel opens, it automatically calls ThisWorkbook.Workbook_Open() …
… which calls AddDeleteMenuItems.AddMainMenuItems() …
… which (after cleaning up from the last time Excel was closed, in case cleanup didn't happen) adds a series of menu items to the VBE's main menu …
… the topmost item is "MyStuff" (or whatever you changed the value of gsMENU_NAME to) and when this item is clicked, it calls the MenuClicked() procedure (as that is what is added to its OnAction property) and then 'drops down' to show the menu items that were added to it …
… and added to the topmost item is (currently only one, though we will add more if you follow this whole post) sub-menu item called "Goto Module" and when this item is clicked, it calls the ShowAllModules() procedure (as that is what is added to its OnAction property)
When Excel closes, it automatically calls ThisWorkbook.Workbook_BeforeClose() …
… which calls AddDeleteMenuItems.CleanUp() …
… which cleans up
By the way ...
The ampersand in the caption "Goto &Module" and also in "&MyStuff" (GlobalConstants.gsMENU_NAME) define the accelerator keys used to trigger the menu items.
The explicit number 472 is specifying a FaceId (the icon displayed on the menu item) ... you can change this to other values ... more on this later.
Phase 1 complete!
Add code … phase 2: show a list of all Modules, optionally go to one
So now for more code and actually adding some useful functionality, we're going to create the code for the 'Goto Module' menu item.
First, add UserFormComboBox based on my Multiple choice UserForm in VBA using a ComboBox post (or you can just download the code / UserForm at the end of that post).
The list of Modules (and, later, the list of TODOs) would be much better shown in a UserForm with a ListBox rather than a ComboBox … but this post is about VBE extensibility … I'll leave you to add a UserForm with a ListBox if you'd rather.
We need to change a couple of things in UserFormComboBox - both in the Display() Sub. Firstly, to make it so that when the UserForm is shown, it doesn't bring Excel in front of the VBE window: immediately before the Show command, add the line
And after it add the line
The second change is to make it so that we can change the width of UserFormComboBox while repeatedly showing its 'default instance'. To do this, change the line
To
The Display() Sub is then
You might wonder why we would be using the default instance of UserFormComboBox when that is not best practice. 99% of the time I would be using a specific new instance … but this is the 1%. The reason is that if we use a new instance then, once the code has finished running (in this case, the code to go to a Module), the VBE (in the unhelpful way that only the VBE knows how to do) will jump to the UserFormComboBox. We don't want that to happen as it completely defeats the purpose of 'going to a Module' if UserFormComboBox is forced upon us! Hence, in this case, we use the default instance.
Another optional thing to change in UserFormComboBox is to change the Caption of the 'OK' button to 'Goto' (change it in the UserForm UI editor ... the Caption isn't set with code) … it makes the result of clicking the button clearer … but it's up to you if you do (the code functions the same)!
Next, in the MenuItemHandler Module, update the code for ShowAllModules() to this:
As before at the end of phase 1, compile and fix any problems, then save, then either manually run the AddDeleteMenuItems.AddMainMenuItems() procedure or close and re-start Excel and re-open the VBE.
Click on the 'Goto Module' menu item and you should see UserFormComboBox containing a list of all Module names in whichever Project is active in the VBE … unless you activated a different Project before clicking the menu item then it will show you the Modules in the Project we are creating. Of course, if you activate a different Project then it will show you the Modules from that Project. Click the 'down arrow' to open the ComboBox and select a Module then click OK (or Goto, if you changed the Caption of the button) and the VBE will be updated to show the selected Module.
Phase 2 complete!
Add code … phase 3: show a list of TODOs, optionally go to one
So code for TODOs. By TODOs I am talking about code comments with the text TODO so, for example:
Add a Class Module called CToDo and add this code:
Then add a Standard Module called UtilsToDos and add this code:
Then in the existing MenuItemHandler Module, add this Sub
Finally, in AddDeleteMenuItems.AddMainMenuItems(), below the line where the "Goto &Module" menu item is added, add this line:
As before at the end of phases 1 and 2, compile and fix any problems, then save, then either manually run the AddDeleteMenuItems.AddMainMenuItems() procedure (or close and re-start Excel and re-open the VBE).
Click on 'MyStuff' and then the 'List TODOs' menu item and you should see … a message saying no TODOs were found. So add some TODOs in a few places around the Project then click the menu item again and this time you will see UserFormComboBox with a list of those TODOs (so long, that is, the Project that you added them to is still the Project that is active in the VBE). As before, click the 'down arrow' to open the ComboBox and select a TODO then click OK / Goto and the VBE will be updated to show the selected TODO.
Phase 3 complete!
Add code … phase 4: backup
In the existing MenuItemHandler Module, add this Sub:
In the exiting AddDeleteMenuItems Module, add a Module-level variable (so this goes before the AddMainMenuItems() Sub)
Then within AddMainMenuItems() in the same Module, below the lines that add the existing "Goto &Module" and "List &TODOs" menu items, add this line:
Still in the AddDeleteMenuItems Module, in MenuClicked(), delete the "nothing yet!" comment and add this one line of code
The point of this is to demo how menu items can be (in this case) enabled or disabled when they are shown in the menu … you can also make them hidden/visible, change their caption etc.
Finally, still in the AddDeleteMenuItems Module, at the end of CleanUp(), add this one line of code:
The reason for adding this line (actually, the reason for setting all Module-level objects back to Nothing) is to ensure a clean shutdown of Excel … this isn't normally required as the garbage collector will do this for us … but here we have a risk of interdependencies between multiple objects meaning that the GC cannot clean everything for us. If you find Excel not shutting down properly, or re-starting following a shutdown, when you have been using this VBE AddIn, then this will be why: the fix is to check that all Module-level objects are set to Nothing.
As before at the end of the previous phases, compile and fix any problems, then save, then either manually run the AddDeleteMenuItems.AddMainMenuItems() procedure or close and re-start Excel and re-open the VBE.
Click on the 'Backup to Desktop' menu item and go through the backup process. Once done, you will find a 'VBA backups' folder on your desktop with the backup within it. Of course, you can change the name and location of the 'VBA backups' folder if you want.
Also test that the 'Backup to Desktop' menu item is disabled when the active Project has never been saved (i.e. add a new Workbook in Excel without saving it then, back in the VBE, double-click that Project in the Project window) then click 'MyStuff'.
Phase 4 complete!
Finally
You can change the names of the menu items, the FaceIds (i.e. the pictures on the menu items … to read more about FaceIds read my VBA and FaceIds post) then manually re-run AddMainMenuItems() and they will update. You can also change the name of the procedures added to the OnAction property of the various menu items but you must then also change the names of the actual Subs themselves to match otherwise you will see a 'Cannot run the macro' error dialog (followed by a 'loss of state' meaning you need to run the AddDeleteMenuItems.AddMainMenuItems() procedure).
While the functions you've added to the VBE while following this post might be useful to you (as a professional VBA developer, I use functions like these every day … but I use them as provided by VBE_Extras), I hope that you can think of ways to add additional functions to the VBE that work for you. Many of the procedures added in UtilsVbe and UtilsFiles can be re-used to help you to create your own functionality. The best way to learn what you can do is to give it a go!
And finally, finally … to help you with creating your own functionality, this is a link to the Microsoft documentation for the 'Visual Basic Add-in Model reference' https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/visual-basic-add-in-model-reference ... as usual with Microsoft documentation for VBA it is fairly limited … but better than nothing.
Comments