Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

chouna

Programmer
Mar 28, 2003
75
DE
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.

 
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
 
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] [glasses]
Alcohol and Calculus do not mix!
If you drink, don't derive! [tongue]
 
Mike,

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

Thanks


 
Check my last post

Skip,

[red]Be advised:[/red] [glasses]
Alcohol and Calculus do not mix!
If you drink, don't derive! [tongue]
 
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
 
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
 
Patrik,

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

Skip,

[red]Be advised:[/red] [glasses]
Alcohol and Calculus do not mix!
If you drink, don't derive! [tongue]
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top