You do run into a problem with this though in that unless you create a reference to PERSONAL.XLSB, every time you want to use your UDF, you need to precede the function name with PERSONAL.XLSB!.
But there is an alternative way to use your UDF's and avoid this clumsiness. You can create your own add-in and store your UDF's in that.
Add-ins can be very complex and allow you to write large, complicated applications, or you can use them to create customized Ribbons and provide extra functionality via the Ribbon icons you create.
But in this example, we're just going to look at creating a UDF and saving that into our own add-in.
Once you know these basic steps (and it's not hard) you can experiment on your own.
Creating an Add-In
An add-in is just a workbook you save as an add-in (.xlam) from the Save As dialog box. NOTE: I'm using Excel 2013. In Excel 97-2003 the file extension is .xla.
Once you've saved it and loaded it into Excel, the functions in it become available to all workbooks open in Excel, and you can edit the add-in's VBA code from the VBA editor as you would any other macro enabled workbook.
Where the Add-In is Saved
When you choose to save the workbook as an add-in, Excel automatically changes the save location to the default folder for add-ins.
On my Windows 10/Excel 2013 machine, this default is C:\Users\pgt\AppData\Roaming\Microsoft\AddIns, pgt being my username.
You can save the add-in to any folder though, but if you do, when you want to load the add-in into Excel, you'll have to locate it on your computer by browsing to that folder.
If you save it to the default location you don't have to go hunting around for it on your computer, Excel will automatically list it.
What You Need To Do - Step by Step
Create a new workbook and write a UDF in a code module. I'm going to use the CUBE function I wrote in my last blog post about referencing PERSONAL.XLSB.
Function cube(number As Long) As Long cube = number ^ 3 End Function
Click on the File tab. On the right hand side you should see some of the file's properties listed. At the bottom of this list click on Show All Properties.
When you come to loading the add-in into Excel, Excel uses the title and comments to provide you with information about what the add-in does.
So click on Title and Comments, and fill in something appropriate like so:
Now click on Save As and save the workbook as an add-in (.xlam).
You'll notice that Excel hasn't kept the add-in open. The Title Bar won't show the name of your add-in file. Neither is the add-in visible in the VBA editor.
Loading the Add-In into Excel
To load your new add-in, go to the File tab again. Click on Options, at the bottom left.
In the Excel Options window, click on Add-Ins. Make sure Excel Add-ins is selected in the Manage box, and click Go.
Check the box beside your add-in and click on OK.
The function(s) in your add-in are now available for use, and if you go into the VBA editor (ALT+F11), you'll see the add-in is there and available for editing.
That's it. You've now created your own add-in, loaded it into Excel and you can use the UDF's in it. If you want to write more UDF's, just write the code into the add-in in the VBA editor and save it.