Creating Excel worksheet functions

Factual Questions
1

How can I create a new worksheet function? I swear I did this once upon a time but I cannot figure out how to do it now.

Simple example: Say I want to create a function Cube(x), where x is either a number or a cell refernce and the result would be the cube. How can I do this?

Thanks in advance…

2

Type in the ‘=’ or hit the fx button after clicking on the cell. Then enter in the function.

3

I don’t think I did a very good job of asking the question. The function I want is a 6th order polynomial that I use in a lot of different places. Now instead of typing

=6.034e-8x^5+4.3257e-6x^4…

where x is typically a cell reference into every single cell I wan the formula, I would like to define a function:

[begin pseudo matlab code]

Function output = Poly(x)

output = =6.034e-8x^5+4.3257e-6x^4…

[end pseudo matlab code]

such that all I have to type in a cell is

=Poly(A2)

and it gives the right answer. I swear I have defined functions before in excel before I just cannot remeber how or find anything in the help files.

4

The quickest way to do it when I need to make a function is record a bogus Macro, open the visual basic editor, erase the macro function, and put your own function there. This way you’ll be assured that your function is in the right place.

5

To expand this a bit:

In the VBA editor (ALT+F11), create your function. The syntax is basically:

Function name (parameters) as type
name=some formula or other
End Function

You can then directly access this function in your worksheet cells as:

=name()

So, you could create the wonderful function below:

Function four (y as Double) As Double
four=4*y
End Function

Then, when you type =four(3) in the worksheet of which the macro is a part, you’ll get the number 12 in the cell.

You can refer to macros that are not present in the current workbook, and you can make the function an add in, if you wish.

6

Here’s a link to a Microsoft help page that might further help:

link

7

Thanks all. I see.

I think the last time I did this was before VBA was integrated into Excel (am I dating myself?). Back then, it was much simpler as they just had a drop down menu item “create function” or something similar.