How do I "install" custom functions

[NukedWhale]

Hello,

The concatenate range function (http://www.vbaexpress.com/kb/getarticle.php?kb_id=817#instr) is amazing and I want to make it always available for myself and several (3-4) other users. How do I do so?

Thanks!

[Bob Phillips]

Put it in your personal.xls


Personal.xls is located in the XLStart directory, and is used to store macros and
things that you want to be available to all workbooks, whenever you start
Excel.

You can create it by
- goto Tools>Macros>Record New Macro...
- Choose Personal Macro Workbook form the dropdown
- OK
- click the Stop button on the toolbar that pops-up

You now have a Personal.xls workbook. It is not visible though, it is hidden
by default (Windows>Unhide)

Maybe create a button and assign it to the function.

[NukedWhale]

I'm unable to get this to work. :banghead:

Some additional information...
I'm running Office 2007 on Windows XP Pro in a networked office

I tried saving the custom function into my Personal Macro Workbook, but I am unable to find my Personal.xls in my XLStart Directory.
When I opened a network document that contained the concatenate range function, the function returns the #NAME error because excel has disabled macros and I have to click on the top button to reenable.

The concatenate range function that I'm using...
http://www.vbaexpress.com/kb/getarticle.php?kb_id=817#instr

[Bob Phillips]

Go into the VBIDE, select Personal.xls and then do a

?Application.StartupPath

in the immediate window.

[mdmackillop]

It's possible some PCs don't have Personal.xls, which is created when you first record a macro and choose to save it there.
In this case, either create one, or copy your Personal.xls to the startup folder.

[NukedWhale]

Holy cow this has been a pain in the butt.

I seem to have the concatenate range function now stored in my personal.xlsb file.

However, I can't just type in =concrange()
I either have to insert the function from the insert functions menu
OR
Type in =personal.xlsb!concrange()

Is there a good way around this?

[mdmackillop]

If you save the function in an add-in, you can use it without any prefix.

[NukedWhale]

I tried the add-in and managed to crash Excel 2007 twice.