How to use my user-defined function whenever I open Excel

[chouna]

Two questions:

1. After writing my own user-defined function in Excel, how do I make it run whenever I call Excel without having to open the file it resides in? Sort of making it like a native Excel function.

2. How do I make Excel show the needed parameters or arguments of my functions as I type in the function name? I know it is available whenever you click on the Functions List combo box but is it possible to have the same auto-complete effect of other Excel functions?

Thanks in advance.

 

[mbarron]

For question one.
Create the function in a blank workbook. Save the workbook as an Add-in (XLA file extension) Excel should "jump" to the add-in folder. Go to Tools/ Add-ins... "Browse" and find your file. Check the box to enable. Your function will now be available whenever you start Excel.



Mike

 

[SkipVought]

Hi,

1. After writing my own user-defined function in Excel, how do I make it run whenever I call Excel without having to open the file it resides in? Sort of making it like a native Excel function.

You don't want it to RUN, whenever you OPEN Excel, but rather you want it AVAILABLE to any of your workbooks. Place the macro in your Personal.xls in a MODULE.

2. How do I make Excel show the needed parameters or arguments of my functions as I type in the function name? I know it is available whenever you click on the Functions List combo box but is it possible to have the same auto-complete effect of other Excel functions?

Any Function in a Module will do this. UDF's are listed in the User Defined list of available Functions

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[chouna]

Mike,

Thanks for the quick reply. Got it. Can anybody now give me the solution to question #2?

Thanks

 

[SkipVought]

Check my last post

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}

 

[mbarron]

One major drawback (at least to me) about putting it in a Personal.xls module is that in order to use the function, you need to use =Personal.xls!funtion() instead of just =function().

Mike

 

[chouna]

Skip,

On question #2, I was referring to the prompts that come out whenever you type in a native Excel function, e.g. typing "SUM(" brings up a text-tip of the required parameters. Is this possible in a UDF?

Thanks,
Chouna

 

[01Patrik]

Yes it is. Take a look at

http://www.fontstuff.com/vba/vbatut03.htm#addin

You might try to save the file as an add-in while you're at it. It's a good way to share your code with others (and yourself if you don't want to use personal.xls).



// Patrik

 

[SkipVought]

Patrik,

Your link ONLY adds a description to the FUNCTION an NOT each ARGUMENT.

Skip,

_{[red]Be advised:[/red]
Alcohol and Calculus do not mix!
If you drink, don't derive!}