Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating Custom Buttons | Excel Discussion (Misc queries) | |||
creating custom function - need to receive a cell address as a par | Excel Programming | |||
Creating Custom Function - A Little Help Please! | Excel Programming | |||
Creating a Custom Excel Function to Calculate Gini Coefficients | Excel Worksheet Functions | |||
Creating Custom Function: TRIMSTDEV | Excel Programming |