UDF Using Defined Name

[Guest]

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

 

[Guest]

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

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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!

 

[Peter T]

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

 

[Guest]

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