Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
Can someone please tell me how I can access the contents of a Defined Name in
a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
You are wanting to point at a range so you need to include that in your code
something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
Well....no luck so far. This technique seems simple, but I cannot seem to
make it work. Here is how it looks now. Am I missing something? Public Function FTE(SRnum As Range, RELnum As Range) As Double FTE = (SRnum.Value * Range("SRhours").Value) + _ (RELnum.Value / 12 * Range("RelHours").Value) / 168 End Function The two defined name fields are SRHours and RelHours, and do exist, and are populated, but I still get back only #Value. :-( What am I missing? Something incredibly obvious, I am sure.....thanks a million! Pat "Jim Thomlinson" wrote: You are wanting to point at a range so you need to include that in your code something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
Try qualifying them with the worksheet to which they refer, such as
Worksheets("Data Sheet").Range("SRHours").Value -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "PatK" wrote in message ... Well....no luck so far. This technique seems simple, but I cannot seem to make it work. Here is how it looks now. Am I missing something? Public Function FTE(SRnum As Range, RELnum As Range) As Double FTE = (SRnum.Value * Range("SRhours").Value) + _ (RELnum.Value / 12 * Range("RelHours").Value) / 168 End Function The two defined name fields are SRHours and RelHours, and do exist, and are populated, but I still get back only #Value. :-( What am I missing? Something incredibly obvious, I am sure.....thanks a million! Pat "Jim Thomlinson" wrote: You are wanting to point at a range so you need to include that in your code something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
Thank you for the reply, Bob! Actually, what you proposed is how I started
out. I had found a EXCEL VBA Book that used that same technique. Here is what it looks like at the moment: Public Function FTE(SRnum As Range, RELnum As Range) As Double MsgBox SRnum.Value FTE = (SRnum.Value * Worksheets("Factors").Range("SRhours").Value) + _ (RELnum.Value / 12 * Worksheets("Factors").Range("RelHours").Value) / 168 End Function What does the "As Range" do in function parameters? Wondering if I don't have a problem there. When I was passing all 4 parameters (including absolute references to the defined names) it worked fine, but now, not :-( Thanks for helping! "Bob Phillips" wrote: Try qualifying them with the worksheet to which they refer, such as Worksheets("Data Sheet").Range("SRHours").Value -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "PatK" wrote in message ... Well....no luck so far. This technique seems simple, but I cannot seem to make it work. Here is how it looks now. Am I missing something? Public Function FTE(SRnum As Range, RELnum As Range) As Double FTE = (SRnum.Value * Range("SRhours").Value) + _ (RELnum.Value / 12 * Range("RelHours").Value) / 168 End Function The two defined name fields are SRHours and RelHours, and do exist, and are populated, but I still get back only #Value. :-( What am I missing? Something incredibly obvious, I am sure.....thanks a million! Pat "Jim Thomlinson" wrote: You are wanting to point at a range so you need to include that in your code something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
What does the "As Range" do in function parameters?
The variable is Declared as a Range object. The function requirers you to pass a cell reference, not simply a number. If you want the option to pass either a cell or a number omit "As Range" or change to "As Double". Also omit the pair of .Value's in the function attached to the two passed arguments. Your function worked fine for me with the defined ranges on another sheet and passing a pair of cel-ref's. If you you use this UDF extensively calculation would be much faster as a formula, eg with SRnum defined as was create a new name RelHoursF refersto =Sheet1!$A$2/(12*168) and a formula like this =C1*SRhours+C2*RelHoursF Regards, Peter T "PatK" wrote in message ... Thank you for the reply, Bob! Actually, what you proposed is how I started out. I had found a EXCEL VBA Book that used that same technique. Here is what it looks like at the moment: Public Function FTE(SRnum As Range, RELnum As Range) As Double MsgBox SRnum.Value FTE = (SRnum.Value * Worksheets("Factors").Range("SRhours").Value) + _ (RELnum.Value / 12 * Worksheets("Factors").Range("RelHours").Value) / 168 End Function What does the "As Range" do in function parameters? Wondering if I don't have a problem there. When I was passing all 4 parameters (including absolute references to the defined names) it worked fine, but now, not :-( Thanks for helping! "Bob Phillips" wrote: Try qualifying them with the worksheet to which they refer, such as Worksheets("Data Sheet").Range("SRHours").Value -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "PatK" wrote in message ... Well....no luck so far. This technique seems simple, but I cannot seem to make it work. Here is how it looks now. Am I missing something? Public Function FTE(SRnum As Range, RELnum As Range) As Double FTE = (SRnum.Value * Range("SRhours").Value) + _ (RELnum.Value / 12 * Range("RelHours").Value) / 168 End Function The two defined name fields are SRHours and RelHours, and do exist, and are populated, but I still get back only #Value. :-( What am I missing? Something incredibly obvious, I am sure.....thanks a million! Pat "Jim Thomlinson" wrote: You are wanting to point at a range so you need to include that in your code something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
UDF Using Defined Name
Hi, Peter...you are absolutely right! In fact, this is how this particular
formula started....as simply a formula, instead of a function. What I was trying to accomplish with the function is to learn how to use one, as I now need to take the next step of some VERY complicated calculations based upon the pass parameters. I was trying to avoid "foot long" forumulas. Ie, I will need to take output from the first calculations, do v & hlookups against other tables, more calculations, and then crunch output the results into different cells in another page. SO, this was the "just getting my feet wet" part. I am a complete and total noob with functions, but I do have some programming background (long ago, and far far away), so functions and subroutines are not at all foreign...it is just the whole "Object/Class/properties/events/etc" orientation of this type of programming, with all the dot.notation stuff...that twists my brain sideways! :-) But you guys have really helped me get a start. I am a great "monkey see, monkey do" kinda guy, so am in quest of a respository of really cool spreadsheets that use functions, so I can analyze them, to see how they work, etc. If you have any ideas of this type of resources, and directions would be appreciated. Again, thanks for all the help!!!! Pat "Peter T" wrote: What does the "As Range" do in function parameters? The variable is Declared as a Range object. The function requirers you to pass a cell reference, not simply a number. If you want the option to pass either a cell or a number omit "As Range" or change to "As Double". Also omit the pair of .Value's in the function attached to the two passed arguments. Your function worked fine for me with the defined ranges on another sheet and passing a pair of cel-ref's. If you you use this UDF extensively calculation would be much faster as a formula, eg with SRnum defined as was create a new name RelHoursF refersto =Sheet1!$A$2/(12*168) and a formula like this =C1*SRhours+C2*RelHoursF Regards, Peter T "PatK" wrote in message ... Thank you for the reply, Bob! Actually, what you proposed is how I started out. I had found a EXCEL VBA Book that used that same technique. Here is what it looks like at the moment: Public Function FTE(SRnum As Range, RELnum As Range) As Double MsgBox SRnum.Value FTE = (SRnum.Value * Worksheets("Factors").Range("SRhours").Value) + _ (RELnum.Value / 12 * Worksheets("Factors").Range("RelHours").Value) / 168 End Function What does the "As Range" do in function parameters? Wondering if I don't have a problem there. When I was passing all 4 parameters (including absolute references to the defined names) it worked fine, but now, not :-( Thanks for helping! "Bob Phillips" wrote: Try qualifying them with the worksheet to which they refer, such as Worksheets("Data Sheet").Range("SRHours").Value -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "PatK" wrote in message ... Well....no luck so far. This technique seems simple, but I cannot seem to make it work. Here is how it looks now. Am I missing something? Public Function FTE(SRnum As Range, RELnum As Range) As Double FTE = (SRnum.Value * Range("SRhours").Value) + _ (RELnum.Value / 12 * Range("RelHours").Value) / 168 End Function The two defined name fields are SRHours and RelHours, and do exist, and are populated, but I still get back only #Value. :-( What am I missing? Something incredibly obvious, I am sure.....thanks a million! Pat "Jim Thomlinson" wrote: You are wanting to point at a range so you need to include that in your code something like this... (you should also specify the arguments and return value of your function) Public Function FTE(SRnum as Range, RELnum as Range) as double FTE = ((SRnum.value * range("SRfactor").value) + _ (RELnum.value / 12 * range("RELfactor").value)) / 168 End Function -- HTH... Jim Thomlinson "PatK" wrote: Can someone please tell me how I can access the contents of a Defined Name in a user defined function? I have a page in a workbook with a number of "factors". I have, on a second worksheet, a series of cells into which I have placed the UDF, with a few parameters. In some cases, the same factor is always used in the function, I do not wish to pass that factor to the function, rather, just point at the defined name. In the following Public Function FTE(SRnum, SRfactor, RELnum, RELfactor) FTE = ((SRnum * SRfactor) + (RELnum / 12 * RELfactor)) / 168 End Function The factors SRfactor and RELfactor are fixed, and stored in a defined name on another sheet. Rather than always passing these values, I would like to simply point at them. I have tried a number of ways I have seen in books on excel functions, etc, but they always result in a #Value error. The defined name of these factors, on worksheet "Factors" are SRHours and RELHours. How would I replace SRfactor and RELfactor to point at these defined names? Thanks!!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Run-time error '50290': Application-defined or object-defined erro | Excel Discussion (Misc queries) | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Runtime error 1004- application defined or object defined erro | Excel Programming | |||
Application-Defined or Object-Defined Error on simple code | Excel Programming |