You've created your own PERSONAL.XLSB and want to use it to store VBA that you are going to use all the time. Sounds cool.
You are really keen to know what the cube of numbers are so you write this function and place it into a module in PERSONAL.XLSB.
Function cube(number As Long) As Long cube = number ^ 3 End Function
Looks pretty straightforward, pass in a number and get the cube of that number returned.
You then start typing the formula into your sheet and the first thing you notice is that your function isn't listed:
and if you complete the formula and hit enter, you get a #NAME error.
So what's going on?
In order to use the function you have to precede the function name with PERSONAL.XLSB! e.g.
Create a Reference to PERSONAL.XLSB
To avoid having to type PERSONAL.XLSB! before every function in our personal macro workbook, we can create a reference to PERSONAL.XLSB.
Before you do this, it's a good idea to give your PERSONAL.XLSB a 'nice' name so that it's easy to find later because we have to look though a long list of file names and libraries.
To do this you must open the VBA editor (ALT+F11) and click on the PERSONAL.XLSB name at the top of the Project Explorer (press CTRL+R) if this isn't visible.
In the Properties window you should now see the name PERSONAL.XLSB has. Type over this and press Enter. I've called mine PGTPersonal.
Now to create the reference. If it isn't already open, open the workbook you want to use the function in.
Click on the name of the workbook in the Project Explorer, then click on the Tools menu, and References.
Look for the name you gave to PERSONAL.XLSB, check/tick the box beside it, and click on OK.
Your workbook should now have a new reference created to your PERSONAL.XLSB.
Use the Function
You can now use your function without having to use PERSONAL.XLSB before it, but when you start typing your function name, it still will not appear in the list of available functions (IntelliSense). But it is there, just go ahead and try it.
If you use the Excel-DNA IntelliSense you can get your UDF's to appear in the IntelliSense list.
Every workbook in which you want to use a UDF from PERSONAL.XLSB, has to have a reference created like this.
An alternative to this approach is to create an add-in with the function(s) you want to use. and load them into Excel that way. Functions loaded this way will appear in IntelliSense. I'll look at creating an add-in in a subsequent post.
If you liked this or know someone who could use it please click the buttons below to share it.