Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Below is a function I am working with to calculate
commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
You should be able to see it in the function category Userd Defined
If not, post again -- Kind Regards, Niek Otten Microsoft MVP - Excel "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Todd,
Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Ah there we go, thank you. I had it in the worksheet
module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
-- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
hey Bob,
I got it to work. Below is what I was doing which was causing the error... Public Function CalComm() As Currency CalComm = SalesQ * (1.5 / 100) End Function I failed to put SalesQ in the parenthesis. Thank you and to everyone else who helped me. I seem to be finding myself spending allot of time on obvious mistakes. Thanx again Todd -----Original Message----- -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Well we would never have guessed that unless you posted the code, so well
done for sorting it yourself. It is still the best way to learn<g B ob "Todd Huttenstine" wrote in message ... hey Bob, I got it to work. Below is what I was doing which was causing the error... Public Function CalComm() As Currency CalComm = SalesQ * (1.5 / 100) End Function I failed to put SalesQ in the parenthesis. Thank you and to everyone else who helped me. I seem to be finding myself spending allot of time on obvious mistakes. Thanx again Todd -----Original Message----- -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Todd,
If you want to put the function in A1, it cannot use a value in A1, that's a circular reference. A function in a cell can only reference another cell. SO if you want to pick up SalesQ from a cell, the function would look like this Public Function CalComm(SalesQ) As Currency CalComm = SalesQ * (1.5 / 100) End Function and if A1 held the SalesQ figure, the function would go in A2 or some other cell and would look like =CalComm(A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
When I do that Im getting #VALUE error
-----Original Message----- Todd, If you want to put the function in A1, it cannot use a value in A1, that's a circular reference. A function in a cell can only reference another cell. SO if you want to pick up SalesQ from a cell, the function would look like this Public Function CalComm(SalesQ) As Currency CalComm = SalesQ * (1.5 / 100) End Function and if A1 held the SalesQ figure, the function would go in A2 or some other cell and would look like =CalComm(A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . . |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Todd,
That probably means that you don't have a number in A1. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... When I do that Im getting #VALUE error -----Original Message----- Todd, If you want to put the function in A1, it cannot use a value in A1, that's a circular reference. A function in a cell can only reference another cell. SO if you want to pick up SalesQ from a cell, the function would look like this Public Function CalComm(SalesQ) As Currency CalComm = SalesQ * (1.5 / 100) End Function and if A1 held the SalesQ figure, the function would go in A2 or some other cell and would look like =CalComm(A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . . |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creating a function
Option Explicit
Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Function main(SalesQ as Double) as string Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub The above is your sub re-written (quickly) as a function. If it doesn't work post back. -- Michael Hopwood "Todd Huttenstine" wrote in message ... When I do that Im getting #VALUE error -----Original Message----- Todd, If you want to put the function in A1, it cannot use a value in A1, that's a circular reference. A function in a cell can only reference another cell. SO if you want to pick up SalesQ from a cell, the function would look like this Public Function CalComm(SalesQ) As Currency CalComm = SalesQ * (1.5 / 100) End Function and if A1 held the SalesQ figure, the function would go in A2 or some other cell and would look like =CalComm(A1) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Ah there we go, thank you. I had it in the worksheet module. I put it in a regular module and now its in User Defined, however when I try to insert it in cell A1, it does nothing. when I am inserting it in cell A1 it says this function takes no arguments. So how would I make it take argument so that whatever is in cell A1, it will use that value as the variable instead of the input box? -----Original Message----- Todd, Where have you stored it? It should be in a general module, not a worksheet module or ThisWorkbook. -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Todd Huttenstine" wrote in message ... Below is a function I am working with to calculate commissions. How do I get it to where when I click on a cell in Excel, and then go to insert function, how do I make it to where I can select that function from in there? Right now I dont see it in there. Option Explicit Dim SalesQ As Currency Dim CalculateCommission As Currency Public Function CalComm() As Currency CalculateCommission = SalesQ * (1.5 / 100) End Function Public Sub main() SalesQ = InputBox("Enter Sales Amount") Call CalComm MsgBox ("The commission amount is: $" & CalculateCommission) End Sub . . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Help with creating a function | Excel Worksheet Functions | |||
Creating a Function | Excel Worksheet Functions | |||
Creating a function | Excel Worksheet Functions | |||
Need help creating a function | Excel Discussion (Misc queries) | |||
creating a function | Excel Discussion (Misc queries) |