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.
Please Share
If you liked this or know someone who could use it please click the buttons below to share it.
){kind=link}
Dave says
thank you very much this was very clear and helpful
Philip Treacy says
You’re welcome
Danny says
This used to work on my Excel versions 2013 and 2016, but doesn’t anymore.
Anytime I add the reference to Personal.xlsb in VBA editor and try, the functions added won’t appear and when I close the file and reopen, the reference to the renamed Personal.xlsb is gone…
any ideas?
Catalin Bombea says
Hi Danny, try creating an add-in with your functions, you will no longer need to add a reference to personal.xlsb. Here is an article that will help: create-an-excel-add-in-for-user-defined-functions-udfs
Catalin
Peter Buyze says
Just a minor point: I still don’t understand why you have to give the PERSONAL.XLSB a different name. You it is because “it’s easy to find later because we have to look though a long list of file names and libraries.” But how does that make it easier??? What difference does it make having to look for PERSONAL.XLSB or PGTPersonal or ABRAcadabra or whatever?
Philip Treacy says
Hi Peter,
If you don’t give PERSONAL.XLSB a ‘nice’ name, then when you look at the list of available files/libraries you can create a reference to, PERSONAL.XLSB just appears with the name VBAProject as any other open workbook will too.
By giving it a nice name, it’s easier to find in this list.
This ‘nice’ name is a name used internally by Excel, it doesn’t actually change the filename of PERSONAL.XLSB.
Phil
Glenn Case says
Phil:
Thanks for this post! I have long wondered why my UDFs had to be local vs in Personal.xlsb in order to work. It had never occurred to me to try to create a reference.
You might note that the reason to change the name of Personal.xlsb in the VBA editor is that it’s default name of VBAProject hides it amongst any other open projects. Also, that this does not change the filename of your Personal.xlsb file, it just changes the VBA project name. I found that confusing until I sorted out why I couldn’t find Personal.xlsb as such in the list until I changed the VBAProject name.
Again, thanks for the good tip!
Philip Treacy says
Thanks Glenn, glad to shed some light on this for you.
Phil
Gadi Bizinyan says
Hi Phil,
I would just like to emphasize about the necessity to change the reference name of Personal.xlsb file in the VBA Project if you want to add reference to it.
By default every new file you open has the exact same reference name: “VBAProject”
If you try to add a reference to Personal.xlsb without renaming its default name (i.e. “VBAProject”), you will end up with the following error: “Name conflicts with existing module, project, or object library”
Of course you would not be able to create a reference to Personal.xlsb after receiving this error.
I got this in Excel 2010 but I’m pretty sure it’s the same behavior across all Excel versions. Therefore renaming the reference of Personal.xlsb before referring to it is not a recommendation, it’s mandatory.
Another point, I believe that regardless of the name you decide to give to it (even if you don’t rename it), it would always appear at the top of the references list.