Hi,
I have written some UDFs and saved them in personal.xlsb so that they will be available to all my workbooks.
the problem is that if I want to type the udf (instead of selecting it from the udf category under "Insert Function"),
I have to type "personal.xlsb" before the UDF's name...
Is there a way for a direct and easy way to use UDFs, as if they were regular excel functions?
Thanks
January 18, 2015
Perhaps the following will help:
October 5, 2010
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.
- Open the workbook you want to use the function(s) from PERSONAL.XLSB in.
- Open the VBA editor
- Select the workbook, then go to Tools->References
- 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.
- You should now see a reference created in your workbook
- 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
Power Query
Power Pivot
Xtreme Pivot Tables
Excel for Decision Making
Excel for Finance
Excel Analysis Toolpak
Power BI
Excel
Word
Outlook
Excel Expert
Excel Customer Service
November 8, 2013
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.
Answers Post
1 Guest(s)