Sleeper: Creating own formula

[LordKrishna]

Hi,
I am new to excell programming. I would like to know how to create my own custom formual by creating functions or procedures. I know how to create functions. My question is how can I add the custom function to be used as custom formula just like excell formula by entering = in a cell.

[mvidas]

Hi LK,

To use the function in a worksheet, you have to either have that function code in a module of the worksheet you want to use it on, or in a module of an addin that is installed/active in excel.

For example, though I know you know how to make functions, create a new workbook in excel and press alt-f11 to open the VBA editor. Right click within the vbaproject of that new workbook, go to Insert, then Module. In that module, paste the following:

Function DoubleANumber(ByVal TheNumber As Double) As Double
 DoubleANumber = 2 * TheNumber
End Function

Close the VBA editor, and in a cell of that worksheet, enter =doubleanumber(5) and you'll see 10.
As I said, if you have a custom add-in, or one that allows you to make changes, if you put that function in there too it will work, provided the add-in is installed.

Matt

[LordKrishna]

Hi,

Thanks for your reply it is working. What it is not doing is that like excell formula, there is no intellisence for parameter prompt for user to properly use the formula. What is the way to prompt user with parameters along with formula and when we type just a few words of formula and it fill entierely just like excell formula.

Please reply.

[Norie]

The only way that is possible is through an Add-In.

[Cyberdude]

I guess I just don't understand what you want. When you said "parameters", do you mean function arguments? The functions I have that require arguments show up in the formula list. When I press Enter, then I get the dialog box that prompts (so to speak) for each argument one at a time. I didn't do anything special to make that happen. It's best to name the arguments with names that are pretty much self-explanatory. You can add a few lines of explanatory information to be associated with the function too. I think you'll see where that can be done if you go through the procedure of recording a new macro by doing things on your worksheet. When you turn off the macro recording, they give you a chance to write some explanation of what the macro does.

[LordKrishna]

ok, I will try and let you know.

[TonyJollans]

This came up a couple of days ago. See this thread for details and a link to Laurent Longre's site.

[ALe]

Look in the cool tools section.

[Zack Barresse]

ALe, the Cool Tools section has parry's add-in, not Laurent Longre's add-in. (I kind of hijacked the thread telling about another add-in - parry's.) I have them both, they're excellent.