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:
Create an Add-In
An alternative to the method described here is to put your functions into an add-in.
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.
Write Your Own Add-In
As mentioned earlier, an alternative to creating a reference 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.
It's a lot easier to do this than you might think.
when I save my xls workbook the links to the personal.xlsb refence function is not saved.
thanks for the help
Hi Guy,
When you say ‘links’ do you mean the reference you created in the VBA editor?
When you re-open your xls, and you then look at it in the VBA editor, does it not show the reference to your PERSONAL.XLSB?
It might be better if you can create a post on the forum and supply screenshots of what you are seeing in your VBA editor so I can see what is going on.
An alternative to creating a reference like this is to write your function as an add-in
Regards
Phil
Thanks! This was the thing i was looking for. I’ll check if it works for me.
You’re welcome
In my case this solution did not worked. Because I need to use this reference in several files. There’s no way I can register the reference in all of these files, because I may not even know they exist.
But the solution it self works.
The thing that i’m sure of is the use of the PERSONAL in these files. There’s a way to do this without having to reference every time in different workbooks?
Hi Rafael,
Here is a link to an alternative solution.
thank you very much this was very clear and helpful
You’re welcome
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?
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
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?
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
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!
Thanks Glenn, glad to shed some light on this for you.
Phil
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.