I’ve developed a number of Excel User Defined Functions that I find useful in my day-to-day work, and that I’d like to have at my fingertips.
There’s a few ways I could achieve this. For example I could create an Excel Add-in, or I could create a PERSONAL.XLSB file and store the relevant functions in there.
The problem with both these methods is that the code is stored locally on my machine, so if I were to share the workbook I was working on, a colleague wouldn’t have access to the functions I’d embedded, and it wouldn’t work properly at their end. Therefore the code must be embedded in to the active workbook itself.
When adding a number of functions, it can be a little cumbersome.
I’ve written a short macro that automates the process of importing new modules in to a workbook. I then created a shortcut button that links to this macro and placed it in the Quick Access Toolbar, making importing new modules a breeze:
Click here to download the module containing the relevant code.
The instructions given in the video above are specific to Excel 2016.
You’ll need to first:
- Change your macro security settings as described in the video above.
- Display the Developer tab in the Excel ribbon as described here.
- Create the PERSONAL.XLSB workbook as described here.