Help with creating a custom function

I

I need help please

** Using Excel 2003 **
There was a help page that said go to VB editor, make a new module, and a
function - then call that function within the workbook. So I made a new
module and typed:

Function Commission(MyNumber)
Commission = MyNumber * 0.6
End Function

In that workbook, I did =commission(D7) and =commission(100) and both gave
me the #name error. I followed the example... cant find out how I went wrong.

Anyway - this is what I'm looking for. I have MANY LONG functions, and was
hoping to do something like this:
(note that I have named data ranges - and DateCell & SiteCell differ - I've
got this formula set up for over 500 cells with different DateCell & SiteCell
on each but now I need to add more usability to the worksheet!!)

Current: = SUMPRODUCT( dates1 = DateCell ) * ( sites1 = SiteCell ) *
referred1 ) + SUMPRODUCT ( dates2 = DateCell ) * ( sites2 = SiteCell ) *
referred2 ) + SUMPRODUCT ( dates3 = DateCell ) * ( sites3 = SiteCell ) *
referred3 ) + SUMPRODUCT ( dates4 = DateCell ) * ( sites4 = SiteCell ) *
referred4 )

What I want to type in the cell is = FindReferred(DateCell, SiteCell)

and have a custom function like:

Function FindReferred(DateCell, SiteCell)
FindReferred = (scary long formula above)
End Function

So -
1) how do I get the workbook to find the function I made (I was having the
#name error with the super simple custom function I made)
2) can I use current excel functions like SUMPRODUCT in a custom defined
function?
3) can I have multiple parameters (I want to include 2 cells to make
calculations from - DateCell & SiteCell)
 
G

Gary''s Student

User Defined Functions (UDFs) are very easy to install and use:

1. ALT-F11 brings up the VBE window
2. ALT-I
ALT-M opens a fresh module
3. type the stuff in and close the VBE window

If you save the workbook, the UDF will be saved with it.

To remove the UDF:

1. bring up the VBE window as above
2. clear the code out
3. close the VBE window

To use the UDF from Excel:

=myfunction(A1)
 
T

Tushar Mehta

So -
1) how do I get the workbook to find the function I made (I was having the
#name error with the super simple custom function I made)

Put the function in a standard module. Make sure it is a Public
Function
2) can I use current excel functions like SUMPRODUCT in a custom defined
function?

Yes, most functions are available. Access them via
Application.Worksheetfunction.{function}
3) can I have multiple parameters (I want to include 2 cells to make
calculations from - DateCell & SiteCell)
Yes. Use Public Function MyFx(Argument1, Argument2).

For more search XL VBA help for 'function' (w/o the quotes) and for the
page on 'Function Statement' pay particular attention to arglist
 
I

I need help please

Thanks! But, I'm still having trouble.
Here's what I have in detail:
In 'Module1'
Public Fuction Comission(MyNum)
Comission = MyNum * 0.6
End Function
(I then hit save, exit VBE, save workbook)
type in =comission(A1)

I get #NAME? error still...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top