Anyway to skip reference to personal.xlsb when calling functions

I have a lot of UDF's in my personal macro book. So each time I want to use them I need to type out something like

Formula:

Please Login or Register  to view this content.

Any way I can get Excel to automatically check Personal.xlsb for functions rather than giving me a NAME error?

Thanks in advance.

The only way I know how to do this is to copy the PERSONAL file to a .xla file and install it as an add-in. Then continue to maintain your UDFs in the add-in rather than PERSONAL.

I've never made an "Add-in" would I need to save it to a specific location? This will work with UDFs but what will happen to my macros? Will they still work as part of an Add-in? Thanks 6 String.

Add-ins can be stored anywhere. Office, by default, likes to store them/look for them in your add-ins folder under your user name (in Windows), which is a fine place, unless you need the add-in to be more publicly available or your spreadsheets need to migrate from computer to computer. When you use a function in an add-in, you create a link to that file in your spreadsheet (specific to path and file name). So, when you go to a different computer, if the add-in (including path) is different, you have to jump through a couple of hoops to re-establish the link to the add-in. Then when you come back to the original computer, you have to re-establish the link again. If you are always on the same computer, then location does not matter.

I prefer to continue to store my macros in personal.xlsb, so that they are easy to find and use. So I end up with two files, personal.xlsb and myfunctions.xlam.

That's how I do it.

Thanks Mr. S

Some other info, CB:

	A	B	C
1	Method	Advantage	Disadvantage
2	Preface the function name with workbook name, e.g., =Personal.xls!MyFunc(...)	Personal’s macros appear in the Macros dialog. No reference required.	A lot of typing
3	Name the VBA project something recognizable (like projPersonal) so you can find it in Tools > References, and then set a reference.	Personal’s macros appear in the Macros dialog	Requires a reference in every workbook. If the workbook includes a reference to Personal but uses no Personal UDFs, but does include and use some UDFs of its own, and the workbook is sent to someone else, the reference itself will result in a missing reference error. That’s a disincentive to including a reference in your standard workbook template.
4	Set Personal’s IsAddIn property to True and then save Personal	No reference required	Personal’s macros do not appear in the Macros dialog

Thanks shg. Learn something new every day.