Perhaps the following will help:

http://www.rondebruin.nl/win/personal.htm

Hi Maya,

This is a bit of a pain and another one of those things in Excel that you scratch your head at and wonder why they did it this way.

Thanks for the link Derek but I found it a bit hard to locate Ron's instruction on what to do. Here's a quick rundown.

1. Open the workbook you want to use the function(s) from PERSONAL.XLSB in.
2. Open the VBA editor
3. Select the workbook, then go to Tools->References
   
4. Find your PERSONAL.XLSB and check/tick the box beside it and then click OK. It's handy to give your PERSONAL.XLSB a name so it's easy to find. You can change the name in the Properties window of the VBA editor: select the PERSONAL.XLSB workbook name first and then enter the new name in the Properties window.
   
5. You should now see a reference created in your workbook
   
6. Save the workbook

You can now use the functions in your workbook, BUT when you start typing the function name e.g. =myfunc.... you won't see Excel pop up the function name so it looks like it doesn't recognise what you are typing - but it does. Just complete the function and it should work ok.

Phil

Thank you Derek, I'm learning the issue

Thank you Philip. If I understand, it only applies for a certain workbook. in that case, I can just add it to this WB, not to personal, right?
Is there a way to add the reference automatically?

Hi Maya,

I'm a bit confused by your reply.

The reference to PERSONAL.XLSB is added to the workbook that you want to use the function in.  This function being stored in PERSONAL.XLSB.

I don't know of any way to add the reference automatically.

regards

Phil

Hi Maya,

Try saving the file with macros as .xlam (excel add-in), it's an alternative to personal.xlsb. It will be saved in the default add-ins folder.

Then, from File-->Options-->Add-ins-->Excel Add-ins, activate the add-in you created.

Other things to check:

Make sure they are functions, not Sub with arguments

Make sure they are NOT Private Functions.

The functions will show up in list when you start typing the function name, and, if you press Ctrl+Shift+A after the opening paranthesis, you will even get the list of arguments displayed.

Hi Phil

the question was if I have to add it as a reference to each WB I use, or just once and it will apply to all WB,

Since I need  this function in all of my WB

Thanks

Thank you Catalin

I will try this solution (:

Hi Maya,

Yes, then use Catalin's suggestion, an add-in will suit you better in this situation.

Phil

Well, the add-in is the best solution in my case (:

Thank you all for your help