Help with creating a custom function
 

** 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)

Help with creating a custom function
 

1. Insert, Function, User Defined should do it.
2. Yes
2. Yes

RBS

"I need help please" wrote in
message ...
** 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)

Help with creating a custom function
 

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)

--
Gary''s Student - gsnu200715


"I need help please" wrote:

** 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)

Help with creating a custom 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)

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

In article ,
says...
** 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)

Help with creating a custom function
 

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...


"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200715


"I need help please" wrote:

** 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)

Help with creating a custom function
 

I see...You have spelled the word Function incorrectly.
--
Gary''s Student - gsnu200716


"I need help please" wrote:

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...


"Gary''s Student" wrote:

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)

--
Gary''s Student - gsnu200715


"I need help please" wrote:

** 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)