#NAME? error in function

[Michael Smith]

Hi all, I'm creating my first function, and I think I have it right, but
excel doesn't recognize my function and returns #NAME? Is there a
problem with my code?,.. or is this a referencing problem in VBE. I am
saving this in a module in personal.xlsx
Thanks!
-Mike

Function ROP(Usage, LeadTime, SafetyStock)
ROP = RoundUp(Sum(Usage / 12 * LeadTime) + (Usage / 12 *
SafetyStock), 0)
End Function

 

[Dave Peterson]

Make sure you save the function in a General module--not behind a worksheet, not
behind ThisWorkbook, not in a class module.

And if you're using that UDF in a different workbook (not personal.xls), you'll
need to use something like:

=personal.xls!ROP(a1,b1,c1)

And VBA doesn't have a Sum or Roundup in its arsenal. You could go back and use
excel's builtin functions with something like:

With Application
ROP = .RoundUp(.Sum(Usage / 12 * LeadTime) + (Usage / 12 * SafetyStock), 0)
End With

The dots in front of .roundup and .sum mean that they belong to the object in
the previous With statement--the Excel Application in this case.

 

[Rick Rothstein]

Why are you trying to use a Sum function on what looks like straight
addition? What will the variables Usage and LeadTime contain... individual
numbers or an array of values? Because your function is trying to use Sum on
Usage/12*LeadTime, but not Usage/12*SafetyStock, I can only conclude Usage
is a single value and LeadTime just doesn't seem to be an array in this
situation... so I would think an attempt to use Sum could be eliminated
altogether. As Dave mentioned in his post, VBA doesn't actually have a Sum
or RoundUp function, so you can call out to the worksheet functions to
duplicate them. However, if I am right that none of the arguments in your
function call will be arrays, and that Sum isn't really needed, then you can
use a simple modification to VB's Round function to create RoundUp
functionality...

Function ROP(Usage, LeadTime, SafetyStock)
Value = (Usage / 12 * LeadTime) + (Usage / 12 * SafetyStock)
ROP = Round(Value - (CLng(Value) <> Value))
End Function

 

[Michael Smith]

Dave and Rick, thanks. I understand what you are saying and I adjusted
my code, but I still get a #name? error in the cell. I store all my
macros in personal.xlsx so they are accessible in any open excel
session, so I am storing this function there as well. Do I need to do
something under tools references?
Thanks again.
-Mike

 

[Dave Peterson]

Nope.

But you're going to have to post your corrected UDF.

And share where you saved that procedure (the module name).

And what workbook held the cell with the formula and the formula you used.

 

[Michael Smith]

Thanks Dave, below is my corrected UDF, it's saved in Module1 in modules
folder under VBAproject(PERSONAL.XLSB)
I then go into any open workbook and type into a cell
=ROP(100,.5,.5)

Function ROP(Usage, LeadTime, SafetyStock)
Value = (Usage / 12 * LeadTime) + (Usage / 12 * SafetyStock)
ROP = Round(Value - (CLng(Value) <> Value))
End Function

 

[Dave Peterson]

You have to tell excel where to find it:

=personal.xlsb!ROP(100,.5,.5)

Another option would be to save your personal workbook as an addin. Then excel
will find it (if you continue to have excel open it when excel starts).