Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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 *** Sent via Developersdex http://www.developersdex.com *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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. Michael Smith wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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 -- Rick (MVP - Excel) "Michael Smith" wrote in message ... 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 *** Sent via Developersdex http://www.developersdex.com *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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 *** Sent via Developersdex http://www.developersdex.com *** |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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 wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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 *** Sent via Developersdex http://www.developersdex.com *** |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
#NAME? error in function
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). Michael Smith wrote: 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 *** Sent via Developersdex http://www.developersdex.com *** -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Function Error? | Excel Worksheet Functions | |||
Function error | Excel Worksheet Functions | |||
GammaDist function error in error | Excel Programming | |||
Excel - User Defined Function Error: This function takes no argume | Excel Programming | |||
IF THEN function error | Excel Worksheet Functions |